Detecting head blockers and blocking chains with details for blocking/blocked sessions, actual running queries and blocked objects

Blocking is a very common problem in database systems and occurs when there is a conflict in access to objects. An open transaction keeps its locks whether it is running or sleeping and any other process trying to access these locked objects will be blocked. To solve a blocking problem quickly and efficiently, you have to know which process blocks which processes, on which objects, and using which queries. Both the head blocker and the blocked queries are important because a blocking problem can be solved by fixing any or all of them.

Unfortunately, SQL Server does not have a complete way of providing these details around a blocking problem in real-time. So, I decided to write an SP to do just that! Using recursive common table expressions, the SP can output multiple blocking chains in order, starting with each head blocker and its sibling blocked processes including all blocked objects and queries causing the problem.
 

 USE [master]  
 GO  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 -- =============================================  
 -- Author      : Ersin Unal, (C)2016  
 -- Description : Retrieves head blockers and blocking chain structure for multiple blocking chains.  
 -- =============================================  
 CREATE PROCEDURE [dbo].[sp_GetBlockingChains]  
 AS  
 SET NOCOUNT ON  
 DECLARE @BlockingChains TABLE (spid int, Chain varchar(MAX), Level int, Root tinyint);  
 DECLARE @ProcessTable TABLE (  
     spid smallint,  
     kpid smallint,  
     blocked smallint,  
     waittype binary(2),  
     waittime bigint,  
     lastwaittype nchar(32),  
     waitresource nchar(256),  
     [dbid] smallint,  
     [uid] smallint,  
     cpu int,  
     physical_io bigint,  
     memusage int,  
     login_time datetime,  
     last_batch datetime,  
     ecid smallint,  
     open_tran smallint,  
     [status] nchar(30),  
     [sid] binary(86),  
     hostname nchar(128),  
     [program_name] nchar(128),  
     hostprocess nchar(10),  
     cmd nchar(16),  
     nt_domain nchar(128),  
     nt_username nchar(128),  
     net_address nchar(12),  
     net_library nchar(12),  
     loginame nchar(128),  
     [context_info] binary(128),  
     [sql_handle] binary(20),  
     stmt_start int,  
     stmt_end int,  
     request_id int,  
     DatabaseName sysname NULL,  
     SchemaName sysname NULL,  
     ObjectName sysname NULL,  
     sql_text nvarchar(max) NULL  
 )  
 INSERT INTO @ProcessTable  
 SELECT p.*  
     , DB_NAME(p.dbid)  
     , OBJECT_SCHEMA_NAME(objectid, p.dbid)  
     , OBJECT_NAME(objectid, p.dbid)  
     , SUBSTRING(CONVERT(nvarchar(MAX), t.text),  
             COALESCE(NULLIF((p.stmt_start/2), 0), 1),   
             CASE (p.stmt_end/2.0)  
                 WHEN -0.5 THEN DATALENGTH(t.text)   
                 ELSE ((p.stmt_end/2) - (p.stmt_start/2) + 2)  
             END  
         )  
 FROM sys.sysprocesses p  
 CROSS APPLY sys.dm_exec_sql_text (p.sql_handle) t  
 ;WITH BlockingChains AS  
 (  
     SELECT  
         spid,  
         CONVERT(VARCHAR(MAX), spid) AS Chain,  
         1 AS Level,  
         1 AS Root  
     FROM @ProcessTable  
     WHERE blocked = 0  
     UNION ALL  
     SELECT  
         s1.spid,  
         x.Chain + ',' + CONVERT(VARCHAR(MAX), s1.spid) AS Chain,  
         x.Level + 1 AS Level,  
         0 AS Root  
     FROM BlockingChains x  
     JOIN @ProcessTable s1 ON s1.blocked = x.spid  
 )  
 INSERT INTO @BlockingChains  
 SELECT * FROM BlockingChains  
 OPTION (MAXRECURSION 0);  
 SELECT 'Head Blocker' AS Comment  
     , PT.spid, PT.blocked  
     , CONVERT(varchar(max), PT.spid) AS BlockingChain  
     , 1 AS BlockingLevel  
     , PT.waittype, PT.waittime, PT.lastwaittype, PT.waitresource, PT.cpu, PT.physical_io, PT.memusage, PT.login_time, PT.last_batch, PT.open_tran, PT.status, PT.hostname, PT.program_name, PT.hostprocess, PT.cmd, PT.loginame  
     , PT.DatabaseName  
     , PT.SchemaName  
     , PT.ObjectName  
     , PT.sql_text AS LatestStatement  
 FROM @ProcessTable PT  
 WHERE spid IN (  
     SELECT CONVERT(INT, LEFT(Chain, CHARINDEX(',', Chain) - 1)) FROM @BlockingChains  
     WHERE Root = 0 AND Level > 1  
     GROUP BY CONVERT(INT, LEFT(Chain, CHARINDEX(',', Chain) - 1))  
     )  
 UNION ALL  
 SELECT 'Blocked Process Details' AS Comment, PT2.spid, PT2.blocked  
     , C.Chain AS BlockingChain  
     , C.Level AS BlockingLevel  
     , PT2.waittype, PT2.waittime, PT2.lastwaittype, PT2.waitresource, PT2.cpu, PT2.physical_io, PT2.memusage, PT2.login_time, PT2.last_batch, PT2.open_tran, PT2.status, PT2.hostname, PT2.program_name, PT2.hostprocess, PT2.cmd, PT2.loginame  
     , PT2.DatabaseName  
     , PT2.SchemaName  
     , PT2.ObjectName  
     , PT2.sql_text AS LatestStatement  
 FROM @ProcessTable PT2  
 JOIN @BlockingChains C ON C.spid = PT2.spid  
 WHERE C.Root = 0  
 ORDER BY 4 ASC, 5 DESC, 2 ASC  
 GO