Purpose
Renders active blocking chains as a visual indented tree at the moment the script is run. Head blockers — sessions holding locks that others are waiting for — appear at the root level labelled HEAD. Blocked sessions appear as indented branches beneath them, with further levels for chains where a blocked session is itself blocking others.
Each row shows the SPID and the SQL batch currently executing, stripped of newlines for readable output. The script only returns sessions that are part of an active blocking chain — sessions with no blocking activity are excluded.
Supported Platforms and Versions
SQL Server 2012, 2016, 2017, 2019, 2022 (on-premises and SQL Server on Azure VMs).
Uses sys.sysprocesses and sys.dm_exec_sql_text, both available across all supported versions. Not applicable to Azure SQL Database — sys.sysprocesses is not available there; use sys.dm_exec_requests instead.
Required Permissions
VIEW SERVER STATE on the SQL Server instance.
Preconditions
Active blocking must exist at the time the script runs. The script returns an empty result set when no blocking is present — this is the expected result on a healthy instance.
Run this script during a reported slowdown or in response to a monitoring alert for long-running blocking chains, not as a scheduled batch.
Risk Level
Low. Read-only. Uses a temporary table (#T) which is session-scoped and dropped explicitly at the end. No changes to any session, lock, or configuration.
Script
-- ============================================================
-- SQL Server Blocking Tree
-- Permissions : VIEW SERVER STATE
-- Risk : Read-only
-- Note : Returns results only when active blocking exists
-- ============================================================
SET NOCOUNT ON;
GO
SELECT
SPID,
BLOCKED,
REPLACE(REPLACE(T.TEXT, CHAR(10), ' '), CHAR(13), ' ') AS BATCH
INTO #T
FROM sys.sysprocesses AS R
CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) AS T;
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH) AS
(
-- Anchor: head blockers (not blocked, or self-blocked)
-- that have at least one other session waiting on them
SELECT
SPID,
BLOCKED,
CAST(REPLICATE('0', 4 - LEN(CAST(SPID AS VARCHAR))) + CAST(SPID AS VARCHAR)
AS VARCHAR(1000)) AS LEVEL,
BATCH
FROM #T AS R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (
SELECT * FROM #T AS R2
WHERE R2.BLOCKED = R.SPID
AND R2.BLOCKED <> R2.SPID
)
UNION ALL
-- Recursive: sessions blocked by a member of the tree so far
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;
GOSafe Execution Guidance
- Run against the instance where blocking is reported. Output is a point-in-time snapshot — blocking chains can resolve or change between the execution and the display of results.
- If the result set is empty, no blocking chain exists at this moment. Re-run during the next reported slowdown window.
- Do not leave the connection open between the
INTO #Tstep and theDROP TABLEstep — the temp table is session-scoped and will be cleaned up when the connection closes, but explicit cleanup is included to avoid confusion across multiple executions in the same session. - On instances with a very large number of sessions,
sys.sysprocessesmay take a moment to return. This is expected.
Expected Output
One row per session involved in an active blocking chain, formatted as an indented tree.
HEAD - 58 UPDATE Orders SET Status = 'P' WHERE OrderId = ...
|------ 62 SELECT * FROM Orders WITH (UPDLOCK) WHERE CustomerId = ...
|------ 71 UPDATE Orders SET Status = 'C' WHERE OrderId = ...
| |------ 83 SELECT * FROM Orders WHERE Status = 'P' ...HEADrows are the root blockers — the sessions holding locks that all downstream sessions are waiting for.|------rows are sessions directly blocked by the session above them.- Deeper indentation levels indicate chains where a blocked session is itself blocking further sessions.
- The SPID and truncated SQL batch appear on each row.
Interpreting Results
Identify the HEAD blocker first. The HEAD session is where the blocking originates. Its BATCH column shows what it is executing. Common causes:
- A long-running explicit transaction that has not committed or rolled back.
- A batch that is still running and holds row or page locks that others need.
- An idle session in an open transaction (the
BATCHcolumn may show the last statement, not necessarily an active one — checksys.dm_exec_sessionsforopen_transaction_count).
Investigate the HEAD session:
-- Check transaction state and idle time for the head blocker
SELECT s.session_id, s.status, s.open_transaction_count,
s.last_request_start_time, s.last_request_end_time,
r.wait_type, r.wait_time, r.blocking_session_id
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
WHERE s.session_id = <HEAD_SPID>;Long blocking chains (3+ levels): A chain where a blocked session is itself blocking others usually indicates that the root transaction is holding locks for a disproportionately long time. Shorten the transaction or reduce the lock scope.
Same HEAD across multiple runs: The blocker is not resolving. Escalate — this may require killing the session if the business impact is confirmed and the root cause cannot be addressed immediately.
Killing a session (
KILL <spid>) rolls back any open transaction for that session and releases its locks. This is a disruptive action — confirm the session is the confirmed source of blocking and that the rollback impact is understood before proceeding.
Rollback Steps
Not applicable — read-only script. The temporary table #T is dropped explicitly at the end of the script.