JohnnyBno

SQL Blocking Tree w/Cursor info

Apr 15th, 2020
453
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*  SQL Blocking Tree w/Cursor info
  2. * Displays blocked processes with relevant information, ordered by lead blocker.
  3. * Original Ref: https://blog.sqlauthority.com/2020/04/20/sql-server-blocking-tree-identifying-blocking-chain-using-sql-scripts/
  4.                 https://blog.sqlauthority.com/2015/07/07/sql-server-identifying-blocking-chain-using-sql-scripts/
  5.  
  6. * 2020-05-01 / JB: Adjusted the script to run on SQL Server versions 2008 R2, 2012, 2014, 2016, 2017, 2019, Azure (12.0.2000.8).
  7.     (1) Changed from IIF to CASE WHEN, since IIF doesn't exist on older SQL Server versions.
  8.     (2) Added new result column [Schema].
  9.     (3) Removed calls to 'master.dbo.syslockinfo' and 'master.dbo.spt_values'; these don't exist on Azure.
  10. * 2020-05-01 / JB: Bugfix and enhancement if multiple databases are used in the blocking scenario.
  11.     (1) Wrong database id was used in 4 places when displaying database, schema and table name.
  12.     (2) Added new result column [Source database]; this is where the SQL command is executed from.
  13.  
  14. Columns:
  15.     [Source database] - The database where the [SQL Text] or [Cursor SQL Text] is executed from.
  16.     [SQL Text], [Cursor SQL Text] - The SQL command that is blocking or blocked.
  17.     [Database], [Schema], [Table], [Wait Resource] - The blocked table and page/row.
  18.    
  19. */
  20. IF OBJECT_ID('tempdb..#Blocks') IS NOT NULL
  21.     DROP TABLE #Blocks
  22. SELECT   spid
  23.         ,blocked
  24.         ,REPLACE (REPLACE (st.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS batch
  25. INTO     #Blocks
  26. FROM     sys.sysprocesses spr
  27.     CROSS APPLY sys.dm_exec_sql_text(spr.SQL_HANDLE) st
  28. GO
  29.  
  30. WITH BlockingTree (spid, blocking_spid, [level], batch)
  31. AS
  32. (
  33.     SELECT   blc.spid
  34.             ,blc.blocked
  35.             ,CAST (REPLICATE ('0', 4-LEN (CAST (blc.spid AS VARCHAR))) + CAST (blc.spid AS VARCHAR) AS VARCHAR (1000)) AS [level]
  36.             ,blc.batch
  37.     FROM    #Blocks blc
  38.     WHERE   (blc.blocked = 0 OR blc.blocked = SPID) AND
  39.             EXISTS (SELECT * FROM #Blocks blc2 WHERE blc2.BLOCKED = blc.SPID AND blc2.BLOCKED <> blc2.SPID)
  40.     UNION ALL
  41.     SELECT   blc.spid
  42.             ,blc.blocked
  43.             ,CAST(bt.[level] + RIGHT (CAST ((1000 + blc.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS [level]
  44.             ,blc.batch
  45.     FROM     #Blocks AS blc
  46.         INNER JOIN BlockingTree bt
  47.             ON  blc.blocked = bt.SPID
  48.     WHERE   blc.blocked > 0 AND
  49.             blc.blocked <> blc.SPID
  50. )
  51. SELECT  N'' + ISNULL(REPLICATE (N'|         ', LEN (LEVEL)/4 - 2),'')
  52.         + CASE WHEN (LEN(LEVEL)/4 - 1) = 0 THEN '' ELSE '|------  ' END
  53.         + CAST (bt.SPID AS NVARCHAR (10)) AS BlockingTree
  54.         ,spr.lastwaittype   AS [Type]
  55.         ,spr.loginame       AS [Login Name]
  56.         ,DB_NAME(spr.dbid)  AS [Source database]
  57.         ,st.text            AS [SQL Text]
  58.         ,CASE WHEN cur.sql_handle IS NULL THEN '' ELSE (SELECT [TEXT] FROM sys.dm_exec_sql_text (cur.sql_handle)) END  AS [Cursor SQL Text]
  59.         ,DB_NAME(sli.rsc_dbid)  AS [Database]
  60.         ,OBJECT_SCHEMA_NAME(sli.rsc_objid,sli.rsc_dbid) AS [Schema]
  61.         ,OBJECT_NAME(sli.rsc_objid, sli.rsc_dbid) AS [Table]
  62.         ,spr.waitresource   AS [Wait Resource]
  63.         ,spr.cmd            AS [Command]
  64.         ,spr.program_name   AS [Application]
  65.         ,spr.hostname       AS [HostName]
  66.         ,spr.last_batch     AS [Last Batch Time]
  67. FROM BlockingTree bt
  68.     LEFT OUTER JOIN sys.sysprocesses spr
  69.         ON  spr.spid = bt.spid
  70.     CROSS APPLY sys.dm_exec_sql_text(spr.SQL_HANDLE) st
  71.     LEFT JOIN sys.dm_exec_cursors(0) cur
  72.         ON  cur.session_id = spr.spid AND
  73.             cur.fetch_status != 0
  74.     JOIN sys.syslockinfo sli
  75.         ON  sli.req_spid = spr.spid AND
  76.             sli.rsc_type = 5 AND
  77.             OBJECT_NAME(sli.rsc_objid, sli.rsc_dbid) IS NOT NULL
  78. ORDER BY LEVEL ASC
RAW Paste Data Copied