Blocking is just part of life I’m afraid. Because we have locks (and yes we have to have them, and no, NOLOCK doesn’t avoid them) we will have blocking. Typically it’s going to be very brief and you won’t even notice it. But sometimes you get a query or two blocked for long enough to cause a problem. Even more rarely you end up with a long chain of blocked sessions. Session 100, 101, and 102 are blocked by 67 which is blocked by 82, which is blocked by … Well, you get the idea. It can be very difficult to scan through all of those blocked sessions to find the root cause. That one or two session(s) that are actually causing the problem. So to that end I’ve written the following query. Among other things it will return any lead blockers, how many sessions are actually being blocked by it, and the total amount of time those sessions have been waiting. It will also give you the last piece of code run by the that particular session. Although be aware that won’t always tell you exactly what code caused the blocking.

WITH Blocked_Sessions AS (
       -- Collect lead blockers
       --     Pull all blocking IDs & check which ones are not being blocked themselves
       SELECT sys.dm_exec_requests.blocking_session_id  AS lead_session_id,
             sys.dm_exec_requests.blocking_session_id  AS blocking_session_id , 0 cnt
       FROM sys.dm_exec_requests
       WHERE blocking_session_id  <> 0
         AND blocking_session_id  NOT IN (SELECT session_id FROM sys.dm_exec_requests
                                                            WHERE sys.dm_exec_requests.blocking_session_id  <> 0) 
       UNION ALL
       -- Recurse through list of blocked sessions
       SELECT Blocked_Sessions.lead_session_id, sys.dm_exec_requests.session_id, 1 cnt
       FROM sys.dm_exec_requests
       JOIN Blocked_Sessions
             ON Blocked_Sessions.blocking_session_id  = sys.dm_exec_requests.blocking_session_id 
    ),
       Blocked AS (
             -- Add up all sessions blocked for the lead blocker
             SELECT lead_session_id, SUM(cnt) AS sessions_blocked 
             FROM Blocked_Sessions
             GROUP BY lead_session_id )
SELECT Blocked.*, DATEDIFF(s, Sess.last_request_start_time, getdate()) seconds_blocking,
ISNULL(Req.status,'sleeping') [status], SqlText.text [sql_text],
STUFF((SELECT DISTINCT ISNULL(', ' + db.name,'')
FROM sys.databases db
JOIN sys.dm_tran_locks lcks
ON db.database_id = lcks.resource_database_id
WHERE lcks.request_session_id = Sess.session_id
ORDER BY ISNULL(', ' + db.name,'')
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
,1,2,'') AS database_list
, Conn.client_net_address, Sess.login_name
FROM sys.dm_exec_connections Conn
LEFT OUTER JOIN sys.dm_exec_sessions Sess
       ON Conn.session_id = Sess.session_id
JOIN Blocked
       ON Blocked.lead_session_id = Sess.session_id
CROSS APPLY sys.dm_exec_sql_text(Conn.most_recent_sql_handle) SqlText
LEFT JOIN sys.dm_exec_requests Req
       ON Req.session_id = Sess.session_id
WHERE Blocked.sessions_blocked >= 1
-- We only care if the session has been blocked for longer than 30 seconds.
-- This can obviously be modified or commented out.
  AND DATEDIFF(s, Sess.last_request_start_time, getdate()) > 30;

Filed under: Microsoft SQL Server, Performance, Problem Resolution, SQLServerPedia Syndication, T-SQL Tagged: blocking, microsoft sql server, T-SQL