Following a Blocking Chain to the Head Blocker

The query everyone sees waiting is rarely the one holding the line. Following a blocking chain to the head blocker shows where the queue starts and what transaction needs attention.

Start With the Waiting Sessions

Blocking happens when one session holds a lock another needs. sys.dm_exec_requests exposes the immediate blocker for an active waiting request. A chain forms when that blocker is also waiting behind another session. The head blocker is the session at the start of that dependency path. Finding it prevents you from treating every waiting query as a separate problem.

I begin with current requests and their blocker IDs. The view is a snapshot. Sessions can finish or change blockers before the next query runs, so capture the sample time and repeat when the pattern is moving. Do not assume the longest wait is the head. It is usually a victim.

SELECT SYSDATETIME() AS sample_time,
       session_id,
       blocking_session_id,
       wait_type,
       wait_time,
       wait_resource,
       DB_NAME(database_id) AS database_name
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
ORDER BY wait_time DESC;

Walk Toward the Head Blocker

For each waiting session, follow blocking_session_id to the next request. Continue until the blocker is not itself blocked. A recursive query can help build the chain, but keep a depth limit and inspect cycles or special values. The head can be sleeping and therefore absent from sys.dm_exec_requests. In that case, look in sys.dm_exec_sessions and transaction views.

I draw the chain in a few lines during an incident. Session A waits for B, B waits for C, and C is the place to investigate. A dashboard that shows only A’s wait is missing the cause. Ask which session begins the line before discussing any termination. The immediate blocker is not always the ultimate blocker.

Inspect the Head Blocker Session

Join to sys.dm_exec_sessions for login, host, program, status, and open_transaction_count. Use sys.dm_exec_connections for the most recent SQL handle when the session is sleeping. The last batch can be a clue, but it is not necessarily the statement that opened the transaction. Compare with application logs and transaction details before assigning cause.

This query looks at a named session. Replace the example ID with the head blocker you found. A sleeping session with an open transaction deserves urgent investigation, but it can still be part of a legitimate operation. The question is what owns the transaction and when it will commit.

DECLARE @head_session_id int = 57;
SELECT s.session_id,
       s.login_name,
       s.host_name,
       s.program_name,
       s.status,
       s.open_transaction_count,
       c.connect_time,
       t.text AS most_recent_batch
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_connections AS c
  ON c.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
WHERE s.session_id = @head_session_id;

Check Head Blocker Locks and Transaction Age

A head blocker can hold locks for a long-running transaction, or it can simply be doing expensive but expected work. Look at the lock resource and transaction begin time. An old transaction with no active request is a different operational situation from an active index operation. Do not infer intent from the database name alone.

I ask the application owner whether the session is doing useful work before ending it. If the blocker is a scheduled maintenance command, stopping it can cause a long rollback and more disruption. If it is an abandoned transaction, ending the session can be appropriate after a reviewed decision. Record both the wait impact and the expected rollback cost.

Choose the Safest Ending

Possible endings include letting the transaction finish, asking the application to commit or roll back, correcting an access pattern, or terminating a session under an incident procedure. KILL is a last operational choice, not a diagnosis. A killed transaction rolls back and can continue holding resources during rollback. The user-visible wait can not vanish immediately.

I do not paste a KILL command into a general article as though a session ID were a harmless parameter. Confirm the exact instance and ID at the time of action. Get the incident owner and application owner involved when possible. The safe ending depends on the transaction, not on how impatient the waiting queue looks.

Find the Recurring Cause

After the incident, examine transaction boundaries, isolation level, indexing, and query patterns. A transaction that holds locks while waiting for user input needs application work. A scan that locks far more rows than expected can need an index or plan review. Read committed snapshot isolation can reduce some reader-writer blocking, but it changes behavior and uses version storage. It deserves testing.

I preserve the blocking sample and application timeline. Without them, the next review becomes a debate over whose query was slow. Identify the statement that held the lock, the one that waited, and the resource between them. That is a concrete starting point for a durable fix.

Use History for Intermittent Chains

If blocking clears before you can inspect it, configure a blocked process report or a targeted Extended Events session. Set the threshold according to the level of delay worth capturing. The report records blocked and blocking process details, but it is still one event in a changing system. Correlate it with Query Store, job history, and application logs.

I prefer a small, persistent capture over waiting for the perfect live screenshot. It gives you timestamps and enough context to reconstruct the pattern. Avoid capturing every statement indefinitely. Focus on the waits that affect users and keep the event file retention appropriate for your incident process.

Report the Chain Clearly

Write the chain from head to victims. Include sample time, database, head session identity, open transaction state, wait resource, and affected queries. State what ended the block and what remains to be fixed. A report that lists only waiting sessions hides the most useful fact. It also invites a team to tune victim queries that were simply waiting.

What would you need to know if the chain returned tomorrow? Keep that answer in the runbook. A single incident can produce a repeatable query and a clearer escalation route. The head blocker is the place to start, but the underlying transaction design is where long-term work belongs.

Related reading on this blog: Blocking Tree: Identifying Blocking Chain Using SQL Scripts and Locking, Blocking, and Deadlocking: Differences, Similarities, and Best Practices.

A blocked query is not the start of the queue, it is a sign to trace the chain to its head.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.