Welcome to

SqlServer-QA.net

Sign in | Join | Help

Browse by Tags

All Tags » query   (RSS)
Friday, September 28, 2007 1:12 AM

TSQL to list the most used query plans

select TOP 100 objtype, p.size_in_bytes, LEFT([sql].[text], 100) as [text] from sys.dm_exec_cached_plans p outer apply sys.dm_exec_sql_text (p.plan_handle) sql ORDER BY usecounts DESC
Posted by SQL Master | 2 Comments
Monday, September 24, 2007 7:09 AM

Handle ISDATE() with care

Now-a-days it became common practice for newbies to varchar datatype to store dates. My first advise is to use proper DATETIME datatype. Sometimes it is preferred to use varchar when importing dates from other systems(to avoid any errors if the source
Posted by Madhivanan | 2 Comments
Monday, September 24, 2007 3:45 AM

TSQL to create a TraceCollector with a default collection set

use msdb declare @schedule_uid uniqueidentifier select @schedule_uid=(select schedule_uid from msdb..sysschedules where name=N'CollectorSchedule_Every_15min') declare @collection_set_id int; exec dbo.sp_syscollector_create_collection_set @name = N'SqlTraceDefault',
Posted by SQL Master | 2 Comments
Tuesday, September 04, 2007 7:32 AM

TSQL to obtain a list of current execution of Parallel Plans (MAXDOP)

PARALLELISM is a close friend of DBA where you get to see it as a common occurence in the multi-processor server environment and during such performance issues you can also investigate whether a parallel plan is in use. For instance If a particular query
Posted by SQL Master | 2 Comments
Wednesday, August 29, 2007 4:37 AM

TSQL to detect long running queries against the database

When I'm performing a performance analysis on a 24/7 application and dealing with PSS I had been given the following TSQL to identify the long running queries against a database. select r.session_id, s.host_name, s.program_name, s.host_process_id, r.status,
Posted by SQL Master | 3 Comments
Friday, July 27, 2007 12:44 AM

TSQL to generate blocking scenario for testing

Most of the times you have observed to identify the blocking and how to resolve them. How about you need a script to generate a blocking scenario within your queries, this is to identify the blocker script is working or not. Also will help to test whether
Posted by SQL Master | 1 Comments