SQL SERVER – Blocking Tree – Identifying Blocking Chain Using sp_WhoIsActive
As a DBA, I often receive user requests to check server slowness. Quite often, slowness is directly linked with active blocking on the server.
I find many scripts on internet that provide partial blocking information. For example, below 2 scripts by Pinal Dave are very famous:-
- https://blog.sqlauthority.com/2020/04/20/sql-server-blocking-tree-identifying-blocking-chain-using-sql-scripts/
- https://blog.sqlauthority.com/2015/07/07/sql-server-identifying-blocking-chain-using-sql-scripts/
But, neither of above scripts provide me full picture or flexibility to choose my own metrics.
In order to overcome this issue, I wrote my own version of Blocking Query, which is as below:-
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
USE tempdb; DECLARE @s VARCHAR(MAX), @t VARCHAR(MAX); DECLARE @tableName varchar(255); DECLARE @DeleteFlag tinyint; /* 0 = drop, 1 = truncate, 2 = No Action */ SET @DeleteFlag = 0; SET @tableName = 'tempdb.dbo.WhoIsActive_ResultSet'; EXEC sp_WhoIsActive @sort_order = '[start_time] ASC', @get_outer_command=1, @find_block_leaders=1 --,@get_full_inner_text=1 ,@get_transaction_info=1 ,@get_locks = 1 ,@get_task_info=2, @get_avg_time=1, @get_additional_info=1 ,@get_plans=1 /* 1 = current query, 2 = entire batch */ ,@return_schema = 1, @schema = @s OUTPUT SET @s = REPLACE(@s, '<table_name>', @tableName) IF @DeleteFlag = 0 SET @t = 'IF OBJECT_ID('''+@tableName+''') IS NOT NULL DROP TABLE '+@tableName; IF @DeleteFlag = 1 SET @t = 'IF EXISTS(SELECT * FROM '+@tableName+') TRUNCATE TABLE '+@tableName; IF @DeleteFlag = 2 SET @t = '-- ignore'; EXEC(@t); PRINT @t; SET @s = 'IF OBJECT_ID('''+@tableName+''') IS NULL BEGIN '+@s+'; CREATE CLUSTERED INDEX [CI_WhoIsActive_ResultSet] ON '+@tableName+' ( [collection_time] ASC, session_id ); END' EXEC(@s); EXEC sp_WhoIsActive @sort_order = '[start_time] ASC', @get_outer_command=1, @find_block_leaders=1 --,@get_full_inner_text=1 ,@get_transaction_info=1 ,@get_locks = 1 ,@get_task_info=2, @get_avg_time=1, @get_additional_info=1 ,@get_plans=1 /* 1 = current query, 2 = entire batch */ ,@destination_table = @tableName; -- Delete records when No Blocking was Found delete from tempdb.dbo.WhoIsActive_ResultSet where collection_time in (select r.collection_time from tempdb.dbo.WhoIsActive_ResultSet as r group by r.collection_time having count(r.blocking_session_id) = 0); ;WITH T_BLOCKERS AS ( -- Find block Leaders SELECT r.[dd hh:mm:ss.mss], r.[session_id], r.[sql_text], [batch_text] = REPLACE(REPLACE(REPLACE(REPLACE(CAST(COALESCE(r.[sql_command],r.[sql_text]) AS VARCHAR(MAX)),char(13),''),CHAR(10),''),'<?query --',''),'--?>',''), r.[sql_command], r.[login_name], r.[wait_info], r.[tempdb_allocations], r.[tempdb_current], r.[blocking_session_id], r.[blocked_session_count], r.[reads], r.[writes], r.[physical_reads], r.[CPU], r.[used_memory], r.[status], r.[open_tran_count], r.[percent_complete], r.[host_name], r.[database_name], r.[program_name], r.locks, r.[start_time], r.[login_time], r.[request_id], r.[collection_time] ,[LEVEL] = CAST (REPLICATE ('0', 4-LEN (CAST (r.session_id AS VARCHAR))) + CAST (r.session_id AS VARCHAR) AS VARCHAR (1000)) FROM tempdb.dbo.WhoIsActive_ResultSet AS r WHERE (ISNULL(r.blocking_session_id,0) = 0 OR ISNULL(r.blocking_session_id,0) = r.session_id) AND EXISTS (SELECT * FROM tempdb.dbo.WhoIsActive_ResultSet AS R2 WHERE R2.collection_time = r.collection_time AND ISNULL(R2.blocking_session_id,0) = r.session_id AND ISNULL(R2.blocking_session_id,0) <> R2.session_id) -- UNION ALL -- SELECT r.[dd hh:mm:ss.mss], r.[session_id], r.[sql_text], [batch_text] = REPLACE(REPLACE(REPLACE(REPLACE(CAST(COALESCE(r.[sql_command],r.[sql_text]) AS VARCHAR(MAX)),char(13),''),CHAR(10),''),'<?query --',''),'--?>',''), r.[sql_command], r.[login_name], r.[wait_info], r.[tempdb_allocations], r.[tempdb_current], r.[blocking_session_id], r.[blocked_session_count], r.[reads], r.[writes], r.[physical_reads], r.[CPU], r.[used_memory], r.[status], r.[open_tran_count], r.[percent_complete], r.[host_name], r.[database_name], r.[program_name], r.locks, r.[start_time], r.[login_time], r.[request_id], r.[collection_time] ,CAST (B.LEVEL + RIGHT (CAST ((1000 + r.session_id) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL FROM tempdb.dbo.WhoIsActive_ResultSet AS r INNER JOIN T_BLOCKERS AS B ON r.collection_time = B.collection_time AND r.blocking_session_id = B.session_id WHERE r.blocking_session_id <> r.session_id ) --select * from T_BLOCKERS SELECT [BLOCKING_TREE] = N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 1) + CASE WHEN (LEN(LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END + CAST (r.session_id AS NVARCHAR (10)) + N' ' + (CASE WHEN LEFT(r.[batch_text],1) = '(' THEN SUBSTRING(r.[batch_text],CHARINDEX('exec',r.[batch_text]),LEN(r.[batch_text])) ELSE r.[batch_text] END), r.[dd hh:mm:ss.mss], r.[wait_info], r.[blocked_session_count], r.[blocking_session_id], --r.[sql_text], r.[sql_command], r.[login_name], r.[host_name], r.[database_name], r.[program_name], r.locks, r.[tempdb_allocations], r.[tempdb_current], r.[reads], r.[writes], r.[physical_reads], r.[CPU], r.[used_memory], r.[status], r.[open_tran_count], r.[percent_complete], r.[start_time], r.[login_time], r.[request_id], [sql_command] ,r.[collection_time] FROM T_BLOCKERS AS r ORDER BY collection_time, LEVEL ASC; |
The main benefit of above query is that it uses world famous procedure sp_WhoIsActive to collect blocking metrics with full customization of sp_WhoIsActive.
Thus, with little knowledge and learning, you we get a good grasp of blocking issue with the above query.
I hope this has been helpful. Happy Coding 🙂
Related Posts
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.
Leave a Reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.