Diagnosing Blocking Chains With sys.dm_exec_requests

Users reporting a frozen application need an explanation of what is waiting. Blocking chains connect waiting requests to the sessions that retain the resources they need.

Distinguish Blocking From Other Delays

Inspect the active requests before assuming a lock is the cause. A request can wait for storage, a worker, memory, or a client rather than another user's transaction. Positive blocking_session_id values identify a session-based blocking relationship at that observation. Negative values have documented special meanings and are not ordinary sessions to terminate.

SELECT session_id,request_id,status,command,blocking_session_id,
       wait_type,wait_time,wait_resource,open_transaction_count,
       cpu_time,total_elapsed_time
FROM sys.dm_exec_requests
WHERE session_id<>@@SPID
ORDER BY wait_time DESC,session_id;

Capture the time and preserve the evidence before changing anything. Blocking relationships can change while several queries are being run. I start with the request and transaction state rather than the application's description of everything being stuck. A slow screen supplies a symptom, not the identity of a safe session to end.

Metadata visibility requires the appropriate server diagnostic permission for the deployed version. A restricted result can omit relevant sessions. Record that limitation and obtain approved access before describing a partial view as the complete chain. A permission boundary cannot be resolved by guessing which session is absent.

Walk Blocking Chains to the Head Session

Save positive relationships into a temporary snapshot, then recursively follow each blocker. The example accepts sessions whose observed requests share one positive blocker. Sessions with multiple different request-level relationships require a separate request-aware review rather than an arbitrary collapse.

SELECT session_id,request_id,blocking_session_id
INTO #RequestSnapshot
FROM sys.dm_exec_requests WHERE session_id<>@@SPID;
SELECT session_id AS SessionID,MIN(blocking_session_id) AS BlockerID
INTO #Edges
FROM #RequestSnapshot
GROUP BY session_id
HAVING COUNT(DISTINCT blocking_session_id)=1
   AND MIN(blocking_session_id)>0;
WITH Walk AS
(
    SELECT SessionID AS StartSession,BlockerID AS NextBlocker,
           0 AS Depth,
           CONVERT(varchar(8000),'/' + CONVERT(varchar(12),SessionID) + '/') AS Visited
    FROM #Edges
    UNION ALL
    SELECT w.StartSession,e.BlockerID,w.Depth+1,
           CONVERT(varchar(8000),w.Visited+CONVERT(varchar(12),e.SessionID)+'/')
    FROM Walk AS w
    JOIN #Edges AS e ON e.SessionID=w.NextBlocker
    WHERE w.Depth<31
      AND CHARINDEX('/'+CONVERT(varchar(12),e.SessionID)+'/',w.Visited)=0
)
SELECT StartSession,NextBlocker AS CandidateHeadSession,Depth
FROM Walk AS w
WHERE NOT EXISTS(SELECT 1 FROM #Edges AS e WHERE e.SessionID=w.NextBlocker)
OPTION(MAXRECURSION 32);
SELECT session_id,COUNT(DISTINCT blocking_session_id) AS DistinctBlockers
FROM #RequestSnapshot GROUP BY session_id
HAVING COUNT(DISTINCT blocking_session_id)>1;

The recursion's path check and depth limit prevent uncontrolled traversal. A cycle, a longer chain, a special blocker, or a changing snapshot needs further investigation. The result identifies a candidate head within the captured scope. It does not establish that the candidate is idle, faulty, or safe to disconnect.

Review intermediate requests as well as the head. Several layers can reflect separate transactions and application operations. A blocked session can also block others with locks it already owns. Keep the chain and the application transaction boundaries together so the explanation covers the whole waiting group.

Inspect the Head Even When It Has No Active Request

A sleeping session can retain an open transaction and its locks after a statement ends. It then disappears from active-request-only investigations while remaining the head of a wait chain. Inspect the candidate's session and most recent input alongside any current request.

DECLARE @HeadSession int=54;
SELECT s.session_id,s.status,s.login_name,s.host_name,s.program_name,
       s.open_transaction_count,s.last_request_start_time,s.last_request_end_time,
       r.command,r.wait_type,r.blocking_session_id,b.event_info
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id=s.session_id
OUTER APPLY sys.dm_exec_input_buffer(s.session_id,NULL) AS b
WHERE s.session_id=@HeadSession;
SELECT st.session_id,st.transaction_id,st.is_user_transaction,
       at.transaction_begin_time,at.transaction_state
FROM sys.dm_tran_session_transactions AS st
JOIN sys.dm_tran_active_transactions AS at ON at.transaction_id=st.transaction_id
WHERE st.session_id=@HeadSession;

Replace the sample session number with a freshly verified candidate. Session IDs can be reused after a connection ends. The input buffer describes the submitted batch and is not guaranteed to be the precise statement that originally acquired every retained lock. Correlate it with transaction evidence and application ownership.

Host and program labels are useful clues, but clients supply them. Do not treat them as authenticated proof of the responsible service. Resolve the actual identity and operation through approved application diagnostics. A sleeping session has stopped sending work, but its transaction has not necessarily finished its responsibilities.

Reproduce Blocking Chains in Two Lab Sessions

Create a shared test table in an existing disposable database. Use two independent query sessions connected to that database. Confirm neither session has an unrelated open transaction before beginning.

CREATE TABLE dbo.InventoryItem
(
    ItemID int PRIMARY KEY,
    Amount int NOT NULL
);
INSERT dbo.InventoryItem VALUES(1,10);

In session A, begin a transaction and update the test row. Leave the transaction open briefly while observing from another session. This is an intentional lab state, not a pattern for application code.

IF @@TRANCOUNT<>0 THROW 50000,'Use a clean lab session.',1;
BEGIN TRANSACTION;
UPDATE dbo.InventoryItem SET Amount=11 WHERE ItemID=1;
SELECT @@SPID AS SessionA;

In session B, run the following bounded waiting operation. The timeout prevents an abandoned exercise from waiting indefinitely. The catch block rolls back the lab transaction before reporting the error.

SET LOCK_TIMEOUT 60000;
BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE dbo.InventoryItem SET Amount=12 WHERE ItemID=1;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE()<>0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Inspect the requests while B waits, then commit or roll back A deliberately. Recheck B's result and reset its timeout setting afterward. The exercise shows how a session can be waiting while another connection holds the required row lock. The waiting session's patience is configurable; its opinion about the application is not.

Resolve the Transaction With Its Owner

Which operation owns the head transaction, and can it finish normally? Contact the responsible operating role through the established incident process before choosing an intervention. Check whether a client exception, missing commit, long batch, or slow dependency explains the retained transaction.

I prefer the correct owner completing or rolling back the intended transaction when that remains available. Terminating a session is an incident action with potential rollback time and partial external side effects. It does not erase work instantly. Verify identity immediately before an approved intervention and monitor the rollback and waiting requests afterward.

Close the Lab Transactions After Observation

Return to session A and explicitly finish its transaction before ending the exercise. If B reached its timeout, inspect its transaction count and confirm the catch block rolled back its own transaction. Do not leave the lab connection holding locks while moving on to another task. A demonstration can reproduce a problem accurately and still need careful cleanup.

IF @@TRANCOUNT>0 ROLLBACK TRANSACTION;
SELECT @@TRANCOUNT AS RemainingTransactions;

Reset LOCK_TIMEOUT to its intended session value in B and verify that no lab operation is still active. Remove the test table only after both sessions have finished. Record which statements were observed while the wait existed, because diagnostics captured after releasing A describe a different state. This distinction makes the example useful when explaining a transient incident later.

SET LOCK_TIMEOUT -1;
IF @@TRANCOUNT<>0 THROW 50000,'Finish the lab transaction before cleanup.',1;
DROP TABLE dbo.InventoryItem;

Prevent Blocking Chains From Returning

Keep transactions short and place commit or rollback handling on every application path. Avoid holding locks while waiting for user interaction or a remote service. Review access paths and consistent modification order where they extend contention. Snapshot-based reading can address particular reader-writer interactions, but it does not eliminate conflicting writes.

Blocking chains are useful evidence when their capture scope and changing state remain clear. When investigating blocking chains, preserve the observed relationship, head transaction, owner decision, and service outcome. Resolve the cause that retained the resource rather than repeatedly clearing symptoms without understanding the transaction.

Related reading on this blog: Following a Blocking Chain to the Head Blocker and The Blocked Process Report.

A head blocker is not automatically a bad session, it is the transaction owner whose retained resources need an explained decision.

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