Identifying blocking and locking Currently executing Queries with Waits In SQL Server

This is one of my favorite script which I use most of the time in troubleshooting performance issues on production servers. With the help of this script we can find below.

  • performance bottleneck
  • Is there any blocking & blocker ?
  • currently What queries are executing ?
  • currently what stored procedure are running ?
  • what statement in stored procedure is getting executed right now?
  • Currently which queries consuming most CPU?
  • What are the high CPU queries?
  • provides xml plans for better understanding?
  • How long queries are running?


SELECT s.session_id
    ,r.blocking_session_id AS 'blocked_by'
    ,CONVERT(VARCHAR, DATEADD(ms, r.wait_time, 0), 8) AS 'wait_time'
    ,CONVERT(varchar, (r.total_elapsed_time/1000 / 86400))+ 'd ' +
     CONVERT(VARCHAR, DATEADD(ms, r.total_elapsed_time, 0), 8)   AS 'elapsed_time'
            '<?query --  ' + CHAR(13) + CHAR(13) + Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
                        CASE r.statement_end_offset
                            WHEN - 1
                                THEN Datalength(st.TEXT)
                            ELSE r.statement_end_offset
                            END - r.statement_start_offset
                        ) / 2
                    ) + 1) + CHAR(13) + CHAR(13) + '--?>'
            ) AS XML) AS 'query_text'
    ,COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' + 
     QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS 'stored_proc'
    ,qp.query_plan AS 'xml_plan'  -- uncomment (1) if you want to see plan
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp -- uncomment (2) if you want to see plan
    OR r.session_id != @@SPID
ORDER BY r.cpu_time DESC


After executing the above script you will get the output like below and above shows only currently executing request.


Leave a Reply

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