Part of the DBA-Tools Project.
A single blocked session is usually easy to spot. The harder case is a chain: session C is waiting on session B, which is itself waiting on session A, which is sitting idle holding a lock from a transaction nobody remembers is still open. Killing C’s session does nothing, C isn’t the problem, A is, three levels up a chain that isn’t obvious from any single session’s wait state alone.
This script traces every active blocking chain from head blocker down to the last waiting session, in one recursive query, so the actual root cause is visible immediately instead of pieced together session by session.
Why Blocking Chains Matters
Blocking is normal in any multi-user database; the problem is a blocker that doesn’t release in a reasonable time, and the chain of everyone waiting behind it:
- The head blocker, the session actually holding the lock everyone else is waiting on, is often several levels removed from the session a user or application is complaining about
- An idle head blocker (an open transaction that finished its last statement but never committed) is invisible in a simple active-requests view; this script surfaces it anyway via its last executed statement
downstream_waitersshows blast radius directly: a session blocking 10 others is a very different priority than one blocking 1- Chain depth and shape tell you whether this is a single stuck transaction or a genuine contention hotspot with many independent waiters
When to Run This Script
- Investigating reports of a slow or “hung” application
- Routine SQL Server health checks on busy OLTP systems
- Diagnosing a specific session that’s blocked, to find the actual root cause upstream
- After identifying elevated
LCK_%waits and needing to see the full picture, not just aggregate numbers
The Script
Run the following script against your SQL Server instance.
/*
Script Name : Get-BlockingChains
Category : diagnostics
Purpose : Traces all active blocking chains using a recursive CTE. Returns
every session involved in blocking — head blockers, mid-chain
nodes, and leaf victims — ordered so each chain reads depth-first.
Idle head blockers (sleeping but holding locks) are included; their
last executed statement is recovered via sys.dm_exec_connections.
Returns no rows when the server is not blocked.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-blocking-chains/)
Requires : VIEW SERVER STATE
Performance note: dm_exec_requests is materialised into #ar once to avoid
repeated DMV scans. downstream_waiters is pre-aggregated rather than computed
via a correlated subquery. dm_exec_connections is joined only for sessions
that have no active request (idle head blockers).
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
-- Materialise once — the recursive CTE and downstream count would otherwise
-- each trigger a separate scan of sys.dm_exec_requests.
DROP TABLE IF EXISTS #ar;
SELECT
session_id, blocking_session_id, status, wait_type, wait_time,
cpu_time, logical_reads, writes, total_elapsed_time,
sql_handle, plan_handle, database_id,
statement_start_offset, statement_end_offset
INTO #ar
FROM sys.dm_exec_requests
WHERE session_id <> @@SPID;
WITH
DownstreamCounts AS (
SELECT blocking_session_id,
COUNT(*) AS downstream_waiters
FROM #ar
WHERE blocking_session_id > 0
GROUP BY blocking_session_id
),
Chain AS (
-- Anchor: head blockers — block others but are not themselves blocked
SELECT
s.session_id AS chain_id,
0 AS chain_level,
CAST('head blocker' AS VARCHAR(16)) AS role,
CAST(0 AS SMALLINT) AS blocker_session_id,
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.open_transaction_count,
COALESCE(r.status, s.status) AS status,
r.wait_type,
r.wait_time,
r.cpu_time,
r.logical_reads,
r.writes,
r.total_elapsed_time,
COALESCE(r.database_id, s.database_id) AS database_id,
COALESCE(r.statement_start_offset, 0) AS statement_start_offset,
COALESCE(r.statement_end_offset, -1) AS statement_end_offset,
-- Only fetch dm_exec_connections when there is no active request (idle blocker)
COALESCE(r.sql_handle, c.most_recent_sql_handle) AS sql_handle,
r.plan_handle
FROM sys.dm_exec_sessions AS s
LEFT JOIN #ar AS r ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
AND r.session_id IS NULL
WHERE s.is_user_process = 1
AND EXISTS (SELECT 1 FROM #ar WHERE blocking_session_id = s.session_id)
AND NOT EXISTS (SELECT 1 FROM #ar WHERE session_id = s.session_id AND blocking_session_id > 0)
UNION ALL
-- Recursive: victims blocked by the previous chain level
SELECT
ch.chain_id,
ch.chain_level + 1,
CAST('blocked' AS VARCHAR(16)),
r.blocking_session_id,
r.session_id,
s.login_name,
s.host_name,
s.program_name,
s.open_transaction_count,
r.status,
r.wait_type,
r.wait_time,
r.cpu_time,
r.logical_reads,
r.writes,
r.total_elapsed_time,
r.database_id,
r.statement_start_offset,
r.statement_end_offset,
r.sql_handle,
r.plan_handle
FROM #ar AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
JOIN Chain AS ch ON ch.session_id = r.blocking_session_id
WHERE r.blocking_session_id > 0
)
SELECT
ch.chain_id,
ch.chain_level,
ch.role,
ch.blocker_session_id,
ch.session_id,
ch.login_name,
ch.host_name,
ch.program_name,
DB_NAME(ch.database_id) AS database_name,
ch.open_transaction_count,
ch.status,
ch.wait_type,
ISNULL(ch.wait_time, 0) AS wait_time_ms,
ISNULL(ch.cpu_time, 0) AS cpu_time_ms,
ISNULL(ch.logical_reads, 0) AS logical_reads,
ISNULL(ch.writes, 0) AS writes,
ISNULL(ch.total_elapsed_time, 0) AS total_elapsed_time_ms,
ISNULL(dc.downstream_waiters, 0) AS downstream_waiters,
CAST(
(ISNULL(su.user_objects_alloc_page_count, 0) +
ISNULL(su.internal_objects_alloc_page_count, 0)) * 8
AS BIGINT) AS tempdb_allocations_kb,
CAST(
(ISNULL(su.user_objects_alloc_page_count, 0) - ISNULL(su.user_objects_dealloc_page_count, 0) +
ISNULL(su.internal_objects_alloc_page_count, 0) - ISNULL(su.internal_objects_dealloc_page_count, 0)) * 8
AS BIGINT) AS tempdb_current_kb,
SUBSTRING(
ISNULL(qt.text, ''),
(ch.statement_start_offset / 2) + 1,
CASE
WHEN ch.statement_end_offset = -1
THEN LEN(ISNULL(qt.text, ''))
ELSE (ch.statement_end_offset - ch.statement_start_offset) / 2 + 1
END
) AS sql_text
FROM Chain AS ch
LEFT JOIN DownstreamCounts AS dc ON dc.blocking_session_id = ch.session_id
LEFT JOIN sys.dm_db_session_space_usage AS su ON su.session_id = ch.session_id
OUTER APPLY sys.dm_exec_sql_text(ch.sql_handle) AS qt
ORDER BY
ch.chain_id,
ch.chain_level,
ch.session_id
OPTION (MAXRECURSION 10);
The script materialises sys.dm_exec_requests once, then walks it with a recursive CTE from every head blocker (blocking others, not itself blocked) down through each level of victims, returning one row per session in each chain with its wait detail and the actual statement it’s running or last ran.
How To Run From The Repo
Clone DBA Tools, initialize and run the script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Trace all active blocking chains:
.\run.ps1 Get-BlockingChains
# To run against a remote sql server:
.\run.ps1 Get-BlockingChains -ServerInstance SQLSERVER01
This script lives in the repo at:
Example Output
A genuine 3-level chain, deliberately created in the lab (three sessions updating the same two rows in an order that guarantees a chain rather than a deadlock): session 66 is the actual root cause: idle (sleeping), an open transaction from an earlier statement, holding a lock that’s blocking 67, which is itself blocking 68. Killing 68 would do nothing; 66 is the one to investigate.
Understanding the Results
- chain_id — groups all sessions in the same blocking chain; the head blocker’s session ID.
- chain_level — 0 is the head blocker, 1 is directly blocked by it, 2 is blocked by level 1, and so on. Read chains top to bottom as a dependency path.
- role —
head blockeris always the fix target. Everything else is a symptom of that session, not an independent problem. - status —
sleepingon a head blocker is the classic idle-transaction pattern: the session finished its last statement but never committed or rolled back. - downstream_waiters — how many sessions are waiting, directly or indirectly, behind this one. A head blocker with a high count is a wider-impact problem than one blocking a single session.
- sql_text — the actual statement, current or last executed. For an idle head blocker this is what it ran before going idle, the missing piece that usually explains the chain.
How to Fix Blocking Chains
Identify the head blocker (chain_level = 0) and investigate why its transaction is still open, application code that didn’t commit, a long-running batch, a session left open interactively. If it’s safe to do so:
-- Check what a session is doing before killing it
SELECT session_id, status, open_transaction_count, last_request_start_time
FROM sys.dm_exec_sessions WHERE session_id = 66;
-- Kill the head blocker only after confirming it's safe to lose that transaction
KILL 66;
Killing a mid-chain or leaf session almost never helps; the actual lock is held further up the chain regardless of what happens downstream.
Best Practices
- Always identify and act on the head blocker, not the session that happens to be visible in a user complaint.
- Keep transactions short and avoid leaving one open across user interaction, network calls, or application logic that isn’t strictly part of the database work.
- A recurring pattern of the same query becoming a head blocker points at a design issue (missing index causing a wider lock footprint, a transaction scope that’s too large) worth fixing at the source.
Related Scripts
You may also find these scripts useful:
- Blocking Sessions
- Blocking Summary
- Contention Analysis
- Deadlock Summary
- Lock Escalation Stats
- Open Transactions
Frequently Asked Questions
Is the session at the top of a chain always the one to kill?
Almost always the one to investigate, not necessarily to kill outright. Confirm what it’s doing and whether its transaction is safe to lose before terminating it; sometimes the better fix is letting it finish or committing it manually.
Why does this return more useful detail than sp_who2 or Activity Monitor?
Those tools show blocking pairs (who’s blocking whom, one level), not full chains, and they don’t reliably surface an idle head blocker’s last statement. This script walks the full chain and recovers that context in one pass.
Summary
A blocked session is a symptom; the head blocker several levels up the chain is the actual cause. This script exists to collapse that investigation from several manual steps into one query.
Run this script the moment blocking is suspected, since the chain only exists while the blocking is actively happening; there’s nothing to capture once it clears.
Leave a Reply