Snippet Name: block

Tags: helpful,scriptgeneration

Created Date: Oct 1 2021 8:05AM
Last Modified Date: Apr 1 2022 11:22AM

–desc: produces a tree like structure of current blocking tree with the main blockers marked as HEAD:

Dependencies: none-standalone code

This is one of my go to procedures it produces a quick visual representation of it the blocking tree and the only information I need including generating a hopeful kill statement to fix and identify any blocking that may have been gone on for hours there’s a cute little expansion to show these hours minutes seconds so you know how long it was going on.

Sample Output:

 
  --desc: produces a tree like structure of current blocking tree with the main blockers marked as HEAD: 
 
 
 
 SET NOCOUNT ON 
 
 GO
 
 SELECT 
 
   ElapsedTime = dt.days + ':' + dt.Hours + ':' + dt.Minutes + ':' + dt.Seconds,
 
   [R].[spid], 
 
   [R].[blocked],
 
   [R].[nt_username],
 
   QUOTENAME(OBJECT_SCHEMA_NAME(t.[objectid],t.[dbid])) 
 
   + '.' 
 
   + QUOTENAME(OBJECT_NAME(t.[objectid],t.[dbid])) AS ObjectName,
 
   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
 
     CROSS APPLY(SELECT [Days]          = RIGHT('000' + CONVERT(VARCHAR(128),datepart(day,GETDATE() - r.[last_batch])-1),2),
 
                        [Hours]         = RIGHT('000' + CONVERT(VARCHAR(128),DATEPART(Hour,GETDATE() - r.[last_batch])),2),
 
                        [Minutes]       = RIGHT('000' + CONVERT(VARCHAR(128),datepart(Minute,GETDATE() - r.[last_batch])),2),
 
                        [Seconds]       = RIGHT('000' +CONVERT(VARCHAR(128),datepart(Second,GETDATE() - r.[last_batch])),2)
 
                ) dt
 
 GO
 
 WITH BLOCKERS (ElapsedTime,SPID, BLOCKED, nt_username,ObjectName,LEVEL, BATCH)
 
 AS
 
 (
 
 SELECT R.ElapsedTime,
 
 [R].[spid],
 
 [R].[blocked],
 
 R.[nt_username],
 
 R.ObjectName,
 
 CAST (REPLICATE ('0', 4-LEN (CAST ([R].[spid] AS VARCHAR))) + CAST ([R].[spid] AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
 
 [R].[BATCH] FROM #T R
 
 WHERE ([R].[blocked] = 0 OR [R].[blocked] = [R].[spid])
 
 AND EXISTS (SELECT * FROM #T R2 WHERE [R2].[blocked] = [R].[spid] AND [R2].[blocked] <> [R2].[spid])
 
 UNION ALL
 
 SELECT R.ElapsedTime,[R].[spid],
 
 [R].[blocked],
 
 R.[nt_username],
 
 R.ObjectName,
 
 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 ElapsedTime,[BLOCKERS].[SPID],[BLOCKERS].[nt_username],ISNULL(ObjectName,'==Inline SQL Text ==') AS ObjectName,N'    ' + REPLICATE (N'|         ', LEN ([BLOCKERS].[LEVEL])/4 - 1) +
 
 CASE WHEN (LEN([BLOCKERS].[LEVEL])/4 - 1) = 0
 
 THEN 'HEAD -  '
 
 ELSE '|------  ' END
 
 + CAST ([BLOCKERS].[SPID] AS NVARCHAR (10)) + N' ' + [BLOCKERS].[BATCH] AS BLOCKING_TREE
 
 FROM BLOCKERS ORDER BY [BLOCKERS].[LEVEL] ASC
 
 GO
 
 DROP TABLE #T
 
 GO