This is one of my favourite scripts. It shows all running processes on the server and what statement they are running (the SQL statement bit was taken from BOL). It shows blocking, run time, db, object etc. It’s very handy when people complain the server has suddenly slowed down and you want to know WTF!

The program name column pulls out the job name for any SQL Agent process and the Queue name for any Service Broker tasks. Tracking service broker tasks can be tricky so this is quite handy. By default I comment out the last column and the last join – uncomment these to see the XML query plan (which you can click on in SSMS to get the graphical plan). I leave these commented out because they can cause it to be slower to return results – generally you want this script to be quick.

I use RedGate’s SQL Prompt tool, and have this setup as a snippet shortcut as wtf so I can quickly run it.

SELECT DER.Session_id SessionID, CAST(DER.start_time AS TIME) StartTime, Command, der.[Status],
     (SELECT TOP 1 SUBSTRING(x.[text],statement_start_offset / 2+1 , 
      ( (CASE WHEN statement_end_offset = -1 
         THEN (LEN(CONVERT(nvarchar(max),x.[text])) * 2) 
         ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS SqlStatement,
        OBJECT_SCHEMA_NAME(x.objectid,der.database_id) + '.' + OBJECT_NAME(x.objectid,der.database_id) ObjectName, 
       DB_NAME(der.database_id) DBName, blocking_session_id BlockedBy, wait_type WaitType, wait_time WaitTime, last_wait_type LastWait,
       CASE    WHEN DER.total_elapsed_time < 3000 THEN CAST(DER.total_elapsed_time AS VARCHAR(100)) + ' ms'
        WHEN DER.total_elapsed_time BETWEEN 3000 AND 120000 THEN CAST(DER.total_elapsed_time/1000 AS VARCHAR(100)) + ' secs'
        ELSE CAST((DER.total_elapsed_time/1000)/60 AS VARCHAR(100)) + ' mins' END ElapsedTime, DER.cpu_time CPUTime,
       DER.Reads DiskReads, DER.logical_reads MemReads, DER.Writes, DER.row_count [Rows],
      CASE WHEN OBJECT_NAME(at.queue_id,der.database_id) IS NOT NULL THEN 'SSB: ' + OBJECT_NAME(at.queue_id,der.database_id) WHEN program_name LIKE 'SQLAgent - TSQL%' 
        THEN (SELECT FROM msdb.dbo.sysjobs  
     WHERE program_name LIKE 'SQLAgent - TSQL%' AND SUBSTRING(program_name,  32, 8)=(SUBSTRING(LEFT(msdb.dbo.sysjobs.job_id,8),7,2)+
     SUBSTRING(LEFT(msdb.dbo.sysjobs.job_id,8),5,2)+SUBSTRING(LEFT(msdb.dbo.sysjobs.job_id,8),3,2)+SUBSTRING(LEFT(msdb.dbo.sysjobs.job_id,8),1,2)) )           
        ELSE program_name END AS ProgramName, 
       DES.host_name HostName, DES.original_login_name LoginName, DER.open_transaction_count OpenTrans, der.prev_error PrevErr, MG.wait_time_ms MemWait_ms, MG.DOP
       -- ,P.query_plan
FROM sys.dm_exec_requests DER
JOIN sys.dm_exec_sessions DES ON DER.session_id = DES.session_id
LEFT JOIN sys.dm_exec_query_memory_grants MG ON DES.session_id = MG.session_id
LEFT JOIN sys.dm_broker_activated_tasks AT ON AT.spid = des.session_id
CROSS APPLY sys.dm_exec_sql_text(DER.sql_handle) X
-- OUTER APPLY sys.dm_exec_query_plan (DER.plan_handle) P
WHERE DER.session_id <> @@SPID OR DER.open_transaction_count > 0

Updated Jan 5th to include Service Broker queue names in the program name column. And DOP.

Leave a Reply

Your email address will not be published. Required fields are marked *