Here is a script that shows the full blocking chain. 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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
36 Comments. Leave new
Thanks!
We can you below query as below –
——
SELECT s.session_id
,r.STATUS
,r.blocking_session_id ‘blocked by’
,r.wait_type
,wait_resource
,r.wait_time / (1000.0) ‘Wait Time (in Sec)’
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time / (1000.0) ‘Elapsed Time (in Sec)’
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN – 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END – r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N’.’ + Quotename(Object_schema_name(st.objectid, st.dbid)) + N’.’ +
Quotename(Object_name(st.objectid, st.dbid)), ”) AS command_text
,r.command
,s.login_name
,s.host_name
,s.program_name
,s.host_process_id
,s.last_request_end_time
,s.login_time
,r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
,r.STATUS
,r.blocking_session_id
,s.session_id
Where I can use where condition like ses.login_name not in (‘xxxxx\user_login_name’).