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:
- SQL SERVER – Quickest Way to Identify Blocking Query and Resolution – Dirty Solution
- SQL SERVER – SSMS: Activity – All Blocking Transactions
- SQL SERVER – Find Blocking Using Blocked Process Threshold
- SQL SERVER – Activity Monitor to Identify Blocking – Find Expensive Queries
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
This shows the rows like:
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:
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)
36 Comments. Leave new
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?
Hi,
Would you be abble add some more info lika db, login ,server, app ?
Great script. I modified the script with additional info:
SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ‘ ‘), CHAR (13), ‘ ‘ ) AS BATCH,
program_name, lastwaittype, status, loginame, hostname, db_name(R.dbid) as databasename, cpu, physical_io
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH, program_name, lastwaittype, status, loginame, hostname, databasename, cpu, physical_io)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE (‘0′, 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH,
program_name, lastwaittype, status, loginame, hostname, databasename, cpu, physical_io
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,
R.program_name, R.lastwaittype, R.status, R.loginame, R.hostname, R.databasename, R.cpu, R.physical_io
FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED R.SPID
)
SELECT spid, program_name, lastwaittype, status, loginame, hostname, databasename, cpu, physical_io,
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
This is seriously the bee’s knees! Thanks!
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!
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.
caruncles has replied to this. Thanks caruncles.
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.
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.
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.
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.
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.
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.
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
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
Wow! This is great. Thanks for sharing.
Hi, great script, thanks to both Pinal and Braden!
In our company, we have some applications that use cursors.
When debugging cursors, you normally only get the SQL statement “FETCH API_CURSOR…”, which doesn’t tell the real SQL statement run by the cursor.
I took Braden’s version and added 2 new features: (1) Table name and (2) The cursor SQL command.
You’ll find it here: https://pastebin.com/P84CX7pf
Thank you Johnny,
If you approve, I can do a blog post on it so people can access the code easily.
Hi Pinal,
Yes, thanks – a blog post on it would be nice. I believe there are many IT departments out there that encounters this debug problem. So, making the script easily accessible would be great.
Thank you Johnny, I appreciate your kind approval. I will blog with due credit to you.
Thank you Pinal, very cool script.
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.
Also, we cannot see 250 on activity monitor too.
Check sysprocesses.
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
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
What a clever script to generate the nesting formatting.
Do you have a version of this script that work with SQL 2016 and above?
Great script. There is one issue wherein if multiple blocking exists at specified time, it is difficult to correlate head and tail SPID as sometimes they are not in order. Hence created below script to provide complete tree info.
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ‘ ‘), CHAR (13), ‘ ‘ ) AS BATCH,
program_name, lastwaittype, status, loginame, hostname, db_name(R.dbid) as databasename, cpu, physical_io
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH, program_name, lastwaittype, status, loginame, hostname, databasename, cpu, physical_io)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE (‘0’, 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH,
program_name, lastwaittype, status, loginame, hostname, databasename, cpu, physical_io
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,
R.program_name, R.lastwaittype, R.status, R.loginame, R.hostname, R.databasename, R.cpu, R.physical_io
FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED R.SPID
)
SELECT getdate(), spid, program_name, lastwaittype, status, loginame, hostname, databasename, cpu, physical_io, CASE WHEN (LEN(LEVEL)/4 – 1) = 0 then ‘Head’ else ‘Tail’ + ‘-‘ + CONVERT(VARCHAR,(LEN(LEVEL)/4 – 1)) end as BlockingType,
convert(varchar,convert(int,substring(LEVEL, 1,4))) +
case when (LEN(LEVEL)/4 – 1) >= 1 then ‘ – ‘ + convert(varchar,convert(int,substring(LEVEL, 5,4))-1000) else ” end +
case when (LEN(LEVEL)/4 – 1) >= 2 then ‘ – ‘ + convert(varchar,convert(int,substring(LEVEL, 9,4))-1000) else ” end +
case when (LEN(LEVEL)/4 – 1) >= 3 then ‘ – ‘ + convert(varchar,convert(int,substring(LEVEL, 13,4))-1000) else ” end +
case when (LEN(LEVEL)/4 – 1) >= 4 then ‘ – ‘ + convert(varchar,convert(int,substring(LEVEL, 17,4))-1000) else ” end +
case when (LEN(LEVEL)/4 – 1) >= 5 then ‘ – ‘ + convert(varchar,convert(int,substring(LEVEL, 21,4))-1000) else ” end +
case when (LEN(LEVEL)/4 – 1) >= 6 then ‘ – ‘ + convert(varchar,convert(int,substring(LEVEL, 25,4))-1000) else ” end +
case when (LEN(LEVEL)/4 – 1) >= 7 then ‘ – ‘ + convert(varchar,convert(int,substring(LEVEL, 29,4))-1000) else ” end +
case when (LEN(LEVEL)/4 – 1) >= 8 then ‘ – ‘ + convert(varchar,convert(int,substring(LEVEL, 33,4))-1000) else ” end as Tree,
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
OMG!! This just helped us find the root cause of an issue we’ve been trying to diagnose for a couple of days. Thank you!