SQL SERVER – Identifying Blocking Chain Using SQL Scripts

There are a number of blog posts that have been written on this very topic. This can be from basics to almost advanced in many ways. I am outlining some of them here below:

If these were not enough, I wanted to play around with some SQL Scripts for this basic deadlock behavior. The most basic script I have been using and used by many DBA will include Activity Monitor, sp_who2, sysprocesses etc.

Recently I saw a script written by a DBA friend:

SELECT * FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL
GO

Solarwinds

This shows the rows like:

SQL SERVER - Identifying Blocking Chain Using SQL Scripts blocking-tree-01

This is so cool because I know who is waiting for whom. In the above image 53 is waiting for 68. While 79 is waiting for 53. This was a cool way to look at things.

I wanted to show the same data in slightly different way using T-SQL. So I wrote a Blocking Tree TSQL script.

SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, 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
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (
SELECT * FROM #T R2 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,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N'    ' + REPLICATE (N'|         ', LEN (LEVEL)/4 - 1) +
CASE WHEN (LEN(LEVEL)/4 - 1) = 0
THEN 'HEAD -  '
ELSE '|------  ' END
+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO

The output would look like:

SQL SERVER - Identifying Blocking Chain Using SQL Scripts blocking-tree-02

This is a cool way to look at the same Blocking data inside SSMS. I have taken a simple way to show this script. I am sure this can be beautified more with your help. So if you use this and modify the same. Do post them over comments too. That is a great way to enhance the Blocking Tree Script.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Creating Dataset Using VALUES Clause Without Creating A Table
Next Post
SQL SERVER – 2016 FIX: Install – Rule “Oracle JRE 7 Update 51 (64-bit) or higher is required” failed

Related Posts

No results found

26 Comments. Leave new

  • David W. Madden
    July 14, 2015 6:02 pm

    I need a version of your blocking tree that could run on an old Sql 2000 Server with which I still have to work. Do you have one?

    Reply
  • Hi,

    Would you be abble add some more info lika db, login ,server, app ?

    Reply
  • This is seriously the bee’s knees! Thanks!

    Reply
  • This is easy to read and to the point. I’ve seen several different scripts on this topic all over the web, but seems like none can identify who the blocker is. I have a script running in SQL Agent every 13 seconds looking for blocks. So if it finds one in the middle of the night, it sends an email. I don’t see it until morning. I can SP_WHO2 and look for the blocking spid, but how do I know it was the same process which the job emailed 12 hours ago? I would like the blocker to be identified by a program, workstation, login ID or something that was valid at the time the blocking occurred.

    Thanx!

    Reply
  • Arokiya Nishanthini
    March 6, 2017 5:46 pm

    Excellent!!! Very useful query. Clearly shows which query is blocking the other in a tree format. Thanks a lot for posting all these life saving queries. It would be great if you can setup email notification when blocking occurs.

    Could you please give us an idea how to send this result set in email notification when blocking occurs.

    Reply
  • https://sqlserver-help.com/2014/11/12/script-setup-email-notification-when-blocking-occurs/

    Here is a link to an email notification from a different query. I’ve set mine up to run in SQL Agent every 13 seconds. It only sends an email if it finds blocking. The only problem with it, and the one posted here, is that by time you get the email, the blocking SPID is gone. there needs to be a way to identify the blocker so that it can be researched AFTER the blocking has cleared.

    Reply
  • My wife says I don’t pay attention and after many years I’ve started to agree with her, but in the output displayed in the article I still did not see anything that identified the offending process (blocker). Please point it out to me if I’m wrong. Unless we know exactly what process is causing the block, we can’t correct it. We can only respond after it happens and kill the spid.

    Reply
  • I have not tried using the blocked process threshold. From what I can see on the post, I do not see the blocker. If it is there, please point me to it. The email routine I mentioned earlier will show me a SPID of a blocking process which happened in the middle of the night. by the next morning when I see the email, that SPID no longer exists because the block has cleared. So, I have no idea what caused the block. I want to know the blocking process even if it is 24 hours after the block.

    Reply
    • sys.dm_exec_requests is not showing the session_id as its not running. It left an open transaction and seen only in blocking_session_id column.

      Reply
      • Is there any way to find out what was causing the open transaction? In my case it can take 20 minutes after that session has finished before another session’s statement (a sp deleting in several tables) gets blocked by the open transaction.

  • Darko Martinovic
    May 2, 2017 12:20 pm

    You have ignored completely ECID value. I have many situations in which blocker and blocking thread has the same SPID but different ECID.
    Your script is good starting point. It is useful when you investigating blocking problems and looking for root blocker.

    Reply
  • So did anyone get this working in an email…really need to see the blocking query text associated with the id. I have seen this handled by using event notificatinos…but really do not want to set that up on 100 + sql servers…looking for a simple stored proc running as a job approach with a nicely formatted email….but its necessary to have the BLOCKING QUERY

    Reply
  • Pinal,
    I love this script – simple and genius way of visualizing a blocking tree.
    I took your advice and extended this script to include useful information about the sessions at a glance (and altered the original code for a bit more readability ;) ).
    This could be very easily deployed as a scheduled job and set to send dbmail if any blocking is encountered.
    Hope someone finds this useful.

    Get the useful script from here:
    https://pastebin.com/zKhGjywt

    Reply
  • Robert Montgomery
    March 2, 2018 1:32 am

    Thank you Pinal, very cool script.

    Reply
  • Hi Sir, we’ve a problem.

    We blocking_session_id is greater than 0; for instance, 250. but we cannot see 250 in the session ids, however we can run kill 250 successfully. How we can handle? Thanks.

    Reply
  • Also, we cannot see 250 on activity monitor too.

    Reply
  • selahattin hancer
    May 8, 2019 2:48 am

    You can use the following sql to find the head blocker and identify the blocking reason
    without losing yourself in detail

    create function find_head_blocker (@input int )
    returns int
    as
    begin

    declare @spid int =@input
    declare @head_spid int=null
    declare @blocked int = 999999
    declare @rowCount int
    while @blocked 0
    begin
    declare cur_find_blocker cursor for select spid,blocked from sys.sysprocesses where spid=@spid
    open cur_find_blocker
    fetch next from cur_find_blocker into @spid,@blocked
    set @rowCount=@@ROWCOUNT
    if @rowCount=1
    begin
    if @blocked0
    begin
    set @head_spid=@blocked
    set @spid= @blocked
    end

    end
    if @rowCount=0
    set @blocked=0
    close cur_find_blocker
    DEALLOCATE cur_find_blocker
    end
    return @head_spid
    end

    SELECT
    GETDATE() date,
    databaseName = DB_Name(database_id),
    blocking.session_id AS blocking_session_id ,
    blocked.session_id AS blocked_session_id ,
    dbo.find_head_blocker(blocking.session_id) as head_blocking_session_id,
    waitstats.wait_type AS blocking_resource ,
    waitstats.wait_duration_ms/1000 duration_second,
    waitstats.resource_description ,
    blocking_cache.text AS blocking_text,
    blocked_cache.text AS blocked_text
    FROM sys.dm_exec_connections AS blocking
    INNER JOIN sys.dm_exec_requests blocked
    ON blocking.session_id = blocked.blocking_session_id
    CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle)
    blocked_cache
    CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle)
    blocking_cache
    INNER JOIN sys.dm_os_waiting_tasks waitstats
    ON waitstats.session_id = blocked.session_id

    Reply
  • above script is good. but i need head blocking need to be killed. This script is finding blocking tree. I want to find and kill block with the same script. please do the needful

    Reply
  • What a clever script to generate the nesting formatting.

    Reply
  • Do you have a version of this script that work with SQL 2016 and above?

    Reply

Leave a Reply

Menu