Lead Blocker Query

Question

SET NOCOUNT ON
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 - 1) 
      + CASE WHEN (LEN(LEVEL)/4 - 1) = 0 THEN 'HEAD -  ' ELSE '|------  ' END
      + CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE
FROM BLOCKERS 
ORDER BY LEVEL ASC

GO
DROP TABLE #T
GO

Is this the good query to figure out the Blocking or do we we have better option?

Trying to find blocker query where the keep changing the spids

Answer 1

Not familiar with the API, but looking at some basic practices I'd fail that query in a code review on the formatting alone. Going deeper, at very least I'd replace the temp table with another CTE. That also lets us get rid of all the extra batch separators and get this down to what is effectively a single statement:

WITH Procs AS 
(
    SELECT SPID, BLOCKED
        ,REPLACE(REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
    FROM sys.sysprocesses sp
    CROSS APPLY sys.dm_exec_sql_text(sp.SQL_HANDLE) T
)
, 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 Procs P
    WHERE (BLOCKED = 0 OR BLOCKED = SPID)
        AND EXISTS (SELECT * FROM Procs P2 WHERE P2.BLOCKED = P.SPID AND P2.BLOCKED <> P2.SPID)

    UNION ALL

    SELECT P.SPID,
        P.BLOCKED,
        CAST (B.LEVEL + RIGHT (CAST ((1000 + P.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
        P.BATCH 
    FROM Procs P
    INNER JOIN BLOCKERS B ON P.BLOCKED = B.SPID 
    WHERE P.BLOCKED > 0 AND P.BLOCKED <> P.SPID
)
SELECT 
    N'    ' + REPLICATE (N'|         ', LEN (LEVEL)/4 - 1) 
      + CASE WHEN (LEN(LEVEL)/4 - 1) = 0 THEN 'HEAD -  ' ELSE '|------  ' END
      + CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE
FROM BLOCKERS 
ORDER BY LEVEL ASC

Also, the mnemonics for table aliases should actually mean something, rather than just sequential letters R,S,T, etc. So now the query uses sp as short for "sysprocesses", T for "text", P for the "Procs" CTE (or P2 for the second instance), and B as short for "Blockers".