Find "Header Blocker" SQL Blocking Queries

 Execute below to find and tune/kill the blocking queries.

SET NOCOUNT ON

SET CONCAT_NULL_YIELDS_NULL OFF

GO 

SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH

INTO #T

FROM SYS.SYSPROCESSES R CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T

GO 

WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)

AS

( 

SELECT SPID, 

BLOCKED, 

CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL, 

BATCH FROM #T R

WHERE (BLOCKED = 0 OR BLOCKED = SPID)

AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID) 

UNION ALL 

SELECT R.SPID, 

R.BLOCKED, 

CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL, 

R.BATCH FROM #T AS R 

INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID 

)

SELECT N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 2) + CASE WHEN (LEN (LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END + CAST (SPID AS NVARCHAR (10)) + ' ' + BATCH AS BLOCKING_TREE FROM BLOCKERS ORDER BY LEVEL ASC

GO

DROP TABLE #T

GO