Lead Blocker

7

January 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;

7 thoughts on “Lead Blocker

  1. kast218 says:

    Hi, Kenneth. Thanks for script, please fix Line 23 for case sensitive servers: replace SUM(cnt) on SUM(Cnt).

  2. Chris Harshman says:

    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_id
    • Oh 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.

  3. D S says:

    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 =&gt; RECURSIVE LOOP */
    						ELSE CAST(LTRIM(RTRIM(BLOCKED)) AS INT) END) BLKBY
    			FROM PROCESS_LIST
    			WHERE SPID &gt;= 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 + ' &gt; ' + 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('&gt;',BLOCK_PATH)-1)
    		END BLOCK_BASE,
    		BLOCK_NODE, 
    		BLOCK_LEVEL, 
    		BLOCK_PATH, 
    		@BLOCK_TIME BLOCK_TIME
    FROM BLOCK_TREE
    ORDER BY BLOCK_PATH
  4. Or you could try: EXEC sp_whoisactive @find_block_leaders = 1 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,674 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: