Lead Blocker
16January 18, 2017 by Kenneth Fisher
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;
SQL Studies 
Hi, Kenneth. Thanks for script, please fix Line 23 for case sensitive servers: replace SUM(cnt) on SUM(Cnt).
Thanks! Usually I check on a case sensitive instance but I forgot in this case. I’ve changed the “cnt” to all lower case.
Is it OK to think of the lead blockers as the sessions who are blocking others but not being blocked themselves? I tend to use a much simpler query to find the problem sessions, but this doesn’t give all the details about who is blocked like yours does:
--ultimate blocking session SELECT s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.status AS session_status, db_name(s.database_id) AS database_name FROM sys.dm_exec_sessions s INNER JOIN sys.dm_exec_requests br ON s.session_id = br.blocking_session_id LEFT OUTER JOIN sys.dm_exec_requests er ON s.session_id = er.session_id WHERE er.blocking_session_id IS NULL OR er.blocking_session_id = er.session_idOh absolutely. If you look the vast majority of mine is collecting information. At it’s simplest the lead blockers are the sessions that are blocking but are not being blocked.
This is a similar SProc I wrote to do something similar. Creates a “block tree” showing if these are multiple levels of blocks.
I just cut and pasted it out of the code we run. No warranties ad it probably needs to be cleaned up to run on some systems.
WITH PROCESS_LIST AS (SELECT RTRIM(CAST(@@SERVERNAME AS VARCHAR(100))) AS SERVER, SPID, BLOCKED, UPPER(SB.NAME) DB, LASTWAITTYPE, SP.LAST_BATCH, UPPER(SP.STATUS) STATUS, SP.HOSTNAME, SP.PROGRAM_NAME, SP.LOGINAME FROM MASTER..SYSPROCESSES SP INNER JOIN MASTER..SYSDATABASES SB ON SP.DBID = SB.DBID), UNIQUE_SPID(SERVER, SPID, HOSTNAME, SPID_START, LOGIN, BLKBY) /* REMOVE DUPLICATE LINES FOR PROCESSES WHICH ARE RUNNING IN PARALELL. */ AS (SELECT SERVER, SPID, HOSTNAME, LAST_BATCH, MAX(LOGINAME) LOGINAME, /* LOGIN ARE BLANK ON DUPLICATES */ MAX(CASE WHEN LTRIM(RTRIM(BLOCKED)) = '.' THEN NULL /* DEAL WITH THESE FIRST BECAUSE '.' CAN'T BE CAST AS AN INT */ WHEN SPID = CAST(LTRIM(RTRIM(BLOCKED)) AS INT) THEN NULL /* NULL WHEN PROCESS IS BLOCKING ITSELF => RECURSIVE LOOP */ ELSE CAST(LTRIM(RTRIM(BLOCKED)) AS INT) END) BLKBY FROM PROCESS_LIST WHERE SPID >= 50 GROUP BY SERVER, SPID, HOSTNAME, LAST_BATCH), BLOCKED_DESC_LIST(BLOCKED, BLKBY) /* CREATE LIST WITH DESCRIPTION OF BLOCKED PROCESS AND THE BLKBY PROCESS SPID. */ AS (SELECT CAST(SPID AS VARCHAR) + ' - ' + RTRIM(LOGIN) + ' - ' + RTRIM(HOSTNAME) + ' - ' + CONVERT(VARCHAR,SPID_START,120) BLOCKED, BLKBY FROM UNIQUE_SPID), BLOCKLIST(BLOCKED, BLOCKER) /* JOIN BACK TO UNIQUE_SPID LIST TO CRETAE BLOCKER DESCRIPTION. */ AS (SELECT BLOCKED_DESC_LIST.BLOCKED, CAST(BLOCKED_DESC_LIST.BLKBY AS VARCHAR) + ' - ' + RTRIM(DESCRIPT.LOGIN) + ' - ' + RTRIM(DESCRIPT.HOSTNAME) + ' - ' + CONVERT(VARCHAR,DESCRIPT.SPID_START,120) BLOCKER FROM BLOCKED_DESC_LIST LEFT OUTER JOIN UNIQUE_SPID DESCRIPT ON BLOCKED_DESC_LIST.BLKBY = DESCRIPT.SPID), BASE_NODES(NODE) /* UNIQUE LIST OF PROCESS DESCRIPTION THAT BLOCK SOMETHING ELSE BUT ARE NOT BLOCKED BY SOMETHING ELSE */ AS (SELECT DISTINCT BLOCKLIST.BLOCKED FROM BLOCKLIST JOIN BLOCKLIST BLOCKERS ON BLOCKLIST.BLOCKED = BLOCKERS.BLOCKER /* THIS SEEDS ONLY THOSE THAT BLOCK SOMETHING ELSE. THIS GIVE A BASE FOR THE TREE.*/ WHERE LTRIM(RTRIM(BLOCKLIST.BLOCKER)) IS NULL /* ONLY INCLUDE THOSE THAT ARE NOT BLOCKED BY SOMETHING ELSE - WE ONLY WANT THE BASE BLOCKER, NOT INTERMEDIATE ONES. */), BLOCK_TREE(BLOCK_NODE, BLOCK_LEVEL, BLOCK_PATH) AS (SELECT CAST(NODE AS VARCHAR(MAX)), 1 AS BLOCK_LEVEL, CONVERT(VARCHAR(MAX),NODE) BLOCK_PATH FROM BASE_NODES UNION ALL /* RECURSE BACK TO THE BLOCKLIST TO GET ANY BLOCKED PROCESS AND PUT IT IN THE TREE PATH WITH ITS BLOCKER*/ SELECT CAST(BLOCKLIST.BLOCKED AS VARCHAR(MAX)), BLOCK_TREE.BLOCK_LEVEL + 1 AS BLOCK_LEVEL, BLOCK_TREE.BLOCK_PATH + ' > ' + CONVERT(VARCHAR(MAX),BLOCKLIST.BLOCKED) BLOCK_PATH FROM BLOCKLIST JOIN BLOCK_TREE ON BLOCKLIST.BLOCKER = BLOCK_TREE.BLOCK_NODE) SELECT @BLOCK_TIME BLOCK_TIME, REPLICATE('----',BLOCK_LEVEL - 1) + BLOCK_NODE BLOCK_TREE, CASE WHEN BLOCK_LEVEL = 1 THEN BLOCK_NODE ELSE SUBSTRING(BLOCK_PATH,1,CHARINDEX('>',BLOCK_PATH)-1) END BLOCK_BASE, BLOCK_NODE, BLOCK_LEVEL, BLOCK_PATH, @BLOCK_TIME BLOCK_TIME FROM BLOCK_TREE ORDER BY BLOCK_PATHOr you could try: EXEC sp_whoisactive @find_block_leaders = 1 🙂
Well of course 🙂
Unfortunately not everyone is allowed to put SPs on their instances.
I don’t understand how you are determining how long a session has been blocked. It appears you are getting the time the session being blocked was started. This doesn’t mean the request was blocked the entire time. In most of my testing of this code, this clearly is not the case.
You are right. It’s how long the session has been open. There is no easy way to tell when a session started blocking another session or sessions.
[…] Query – Lead Blocker […]
lead blocker
select distinct blocked leadblocker
from sys.sysprocesses where blocked 0 and blocked not in (select spid from sys.sysprocesses where blocked 0)
Right, but I’m pulling a lot more information and sys.sysprocesses is deprecated and I try not to use those as much as possible.
Cool, I didn’t know sysprocesses was deprecated.
Missing “DISTINCT”, is it a bug? 🙂
…. SELECT DISTINCT sys.dm_exec_requests.blocking_session_id AS lead_session_id ….
Nope. Has to be there otherwise the SUM in the Blocked CTE won’t get the right number :). Same reason I’m using a UNION ALL instead of a UNION.
[…] Finding the lead blocker(s). […]