MSSQLWIKI

Karthick P.K on SQL Server

Posts Tagged ‘Blocking’

Script to get current blocking tree with wait types

Posted by Karthick P.K on November 24, 2010

 

 

SET NOCOUNT ON; 

SET CONCAT_NULL_YIELDS_NULL OFF 

GO 

WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH,waittype,lastwaittype) 

AS 

( 

   SELECT 

   SPID, 

   BLOCKED, 

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

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

   R.waittype, 

   R.lastwaittype 

   FROM sys.sysprocesses R with (nolock) 

   CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T 

   WHERE (BLOCKED = 0 OR BLOCKED = SPID) 

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

   BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH,R.waittype,R.lastwaittype FROM sys.sysprocesses R2 with (nolock) 

   CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T 

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, 

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

    R.waittype, 

    R.lastwaittype 

    FROM sys.sysprocesses AS R with (nolock) 

    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T 

    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 VARCHAR (10)) + ' '  + BATCH AS BLOCKING_TREE ,  waittype ,lastwaittype,  GETDATE() as Time FROM BLOCKERS with (nolock) ORDER BY LEVEL ASC 

go

 

–By Ajith Krishnan

Posted in Performance, SQL Query | Tagged: , , , , , , , , , | 6 Comments »