Advertisement
Libertherme

SQL Blocking Tree

Feb 19th, 2018
1,062
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*  SQL Blocking Tree
  2. * Displays blocked processes with relevant information, ordered by lead blocker.
  3. * Original Ref: https://blog.sqlauthority.com/2015/07/07/sql-server-identifying-blocking-chain-using-sql-scripts/
  4. */
  5.  
  6. IF OBJECT_ID('tempdb..#Blocks') IS NOT NULL
  7.     DROP TABLE #Blocks
  8. SELECT   spid
  9.         ,blocked
  10.         ,REPLACE (REPLACE (st.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS batch
  11. INTO     #Blocks
  12. FROM     sys.sysprocesses spr
  13. CROSS APPLY sys.dm_exec_sql_text(spr.SQL_HANDLE) st
  14. GO
  15.  
  16. WITH BlockingTree (spid, blocking_spid, [level], batch)
  17. AS
  18. (
  19.     SELECT   blc.spid
  20.             ,blc.blocked
  21.             ,CAST (REPLICATE ('0', 4-LEN (CAST (blc.spid AS VARCHAR))) + CAST (blc.spid AS VARCHAR) AS VARCHAR (1000)) AS [level]
  22.             ,blc.batch
  23.     FROM    #Blocks blc
  24.     WHERE   (blc.blocked = 0 OR blc.blocked = SPID)
  25.     AND     EXISTS (SELECT * FROM #Blocks blc2 WHERE blc2.BLOCKED = blc.SPID AND blc2.BLOCKED <> blc2.SPID)
  26.     UNION ALL
  27.     SELECT   blc.spid
  28.             ,blc.blocked
  29.             ,CAST(bt.[level] + RIGHT (CAST ((1000 + blc.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS [level]
  30.             ,blc.batch
  31.     FROM     #Blocks AS blc
  32.     INNER JOIN BlockingTree bt ON blc.blocked = bt.SPID
  33.     WHERE   blc.blocked > 0
  34.     AND     blc.blocked <> blc.SPID
  35. )
  36. SELECT N'' + ISNULL(REPLICATE (N'|         ', LEN (LEVEL)/4 - 2),'')
  37.         + CASE WHEN (LEN(LEVEL)/4 - 1) = 0 THEN '' ELSE '|------  ' END
  38.         + CAST (bt.SPID AS NVARCHAR (10)) AS BlockingTree
  39.         ,spr.lastwaittype   AS [Type]
  40.         ,spr.loginame       AS [Login Name]
  41.         ,st.text            AS [SQL Text]
  42.         ,DB_NAME(spr.dbid)  AS [Database]
  43.         ,spr.cmd            AS [Command]
  44.         ,spr.waitresource   AS [Wait Resource]
  45.         ,spr.program_name   AS [Application]
  46.         ,spr.hostname       AS [HostName]
  47.         ,spr.last_batch     AS [Last Batch Time]
  48. FROM BlockingTree bt
  49. LEFT OUTER JOIN sys.sysprocesses spr ON spr.spid = bt.spid
  50. CROSS APPLY sys.dm_exec_sql_text(spr.SQL_HANDLE) st
  51. ORDER BY LEVEL ASC
Advertisement
Advertisement
Advertisement
RAW Paste Data Copied
Advertisement