MSSQLWIKI

Karthick P.K on SQL Server

Posts Tagged ‘how to fix SQL Server 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 »

 
%d bloggers like this: