Browse by Tags

All Tags » query » tsql   (RSS)
TSQL to list the most used query plans
28 September 07 01:12 AM | SQL Master | 2 Comments   
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 Read More...
Handle ISDATE() with care
24 September 07 07:09 AM | Madhivanan | 2 Comments   
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 Read More...
TSQL to create a TraceCollector with a default collection set
24 September 07 03:45 AM | SQL Master | 2 Comments   
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', Read More...
TSQL to detect long running queries against the database
29 August 07 04:37 AM | SQL Master | 3 Comments   
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, Read More...
TSQL to generate blocking scenario for testing
27 July 07 12:44 AM | SQL Master | 1 Comments   
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 Read More...