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
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
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
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)
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
DROP TABLE #T
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)