The ticket said the database was down. The database was not down: CPU at twelve percent, disk idle, and forty-three sessions stacked in a queue behind one connection that had been sleeping for twenty-two minutes with an open transaction. The monitoring dashboard stayed green the whole time, because nothing was technically wrong except that half the application was waiting on a lock held by a session doing nothing at all.
This is the blocking that never makes the deadlock articles. Deadlocks are loud and self-resolving; a blocking chain is quiet and can sit there for hours. Deadlock graphs get their own treatment in my deadlock graph reading notes — this piece is about the non-deadlock kind, the chain you have to walk yourself. Everything here applies to SQL Server 2016 through 2022, on-prem and Azure SQL Managed Instance.
How do I find the lead blocker in a live chain?
Query sys.dm_exec_requests and read blocking_session_id: every request currently waiting on a lock names the session it waits on, and the lead blocker is the session at the top of the chain whose own blocking_session_id is zero — or whose row is missing entirely, because a sleeping session with an open transaction has no active request and never appears in dm_exec_requests at all. That is the entire algorithm, plus the one wrinkle that decides whether you actually see the head: the chain query must bring blockers back in through sys.dm_exec_sessions, which is exactly what the query below does. The trap is killing whichever session someone complained about first, which is usually a mid-chain victim blocking a few others while itself waiting — kill it and the chain just promotes the next victim.
SELECT s.session_id,
r.blocking_session_id,
r.wait_type,
r.wait_time,
r.wait_resource,
s.status,
s.open_transaction_count,
s.last_request_end_time,
ib.event_info AS last_batch
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, r.request_id) AS ib
WHERE r.blocking_session_id <> 0
OR s.session_id IN (SELECT blocking_session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0)
ORDER BY r.blocking_session_id, s.session_id;Two columns do the triage work. wait_type tells me the flavor of the fight: LCK_M_ waits are plain lock waits and the ones this article cares about, while PAGELATCH_ waits are latches on data pages, a different disease with a different playbook. And wait_time on the head blocker is meaningless, because the head blocker is not waiting on anything — which is exactly the problem. I also pull the blocking session's status, open_transaction_count, and last_request_end_time from sys.dm_exec_sessions in the same breath, because the first question about any lead blocker is whether it is doing anything at all.
What is the lead blocker actually holding?
sys.dm_tran_locks answers it, per session: request_mode gives the lock type (X, IX, U, Sch-M), request_status separates GRANT from WAIT, and resource_type gives the granularity. The rows that matter most in triage are KEY and RID, row locks in clustered indexes and heaps, and OBJECT, which usually means someone took a table or schema lock during a maintenance job that ran long. For KEY rows, resource_description carries the hashed key value, for RID rows the file:page:slot triple, and resource_associated_entity_id carries the hobt id, so joining through sys.partitions and sys.indexes gets me the table and index names. Nine times out of ten the answer is an exclusive row lock from an uncommitted write, and the table name alone tells me which application team to go find.
Page-level waits with no row locks in sight usually mean lock escalation already happened: around five thousand locks on one object get traded for a single table lock, and suddenly everyone with a shared lock request joins the chain. sys.dm_tran_locks shows the one OBJECT X grant that replaced the crowd.
Why is the lead blocker so often a sleeping session?
Because the classic pattern is an application that opened a transaction and never closed it. Code runs BEGIN TRANSACTION, does an update, then throws an exception the catch block swallows without rolling back. The session goes quiet with the transaction still pinned open — a TransactionScope the app process is still holding, a driver that never sent the reset, a code path that simply never closed anything — and it sits in sys.dm_exec_sessions with status = sleeping and open_transaction_count of one, holding every lock it took. SQL Server will happily let that session sleep until the connection dies. Nothing in the engine times it out.
The sleeping-with-open-tran pattern is my first hypothesis whenever last_request_end_time is old and status is sleeping. The second is an orphan: an app server that was OOM-killed mid-batch, or a developer laptop that went to sleep with a debugger paused on a breakpoint inside a transaction, where the TCP connection is half-open and the server has not noticed yet. Both look identical from the DMV side, which is why the kill-or-wait decision deserves a framework instead of a reflex.
What did the lead blocker run last?
sys.dm_exec_input_buffer, available since SQL Server 2014 SP2, is the answer — one call with the session id returns the exact batch text the session last submitted, replacing the elderly DBCC INPUTBUFFER. Do not lean on most_recent_sql_handle joined to sys.dm_exec_sql_text for this: it works often enough to tempt you and lies often enough to waste your night, because the plan handle can point at something already evicted from cache while the session kept sleeping. input_buffer hands you the raw batch regardless of plan cache state. Batch text plus the lock list is usually the whole story: an UPDATE missing its WHERE clause, or an ORM save that wrapped a slow external service call inside a transaction scope.
How do I catch blocking when I am not watching?
Polling misses short chains, so let the engine report them. Set sp_configure 'blocked process threshold (s)' to something like 10, and the lock monitor — which wakes every few seconds on its own schedule, not on the threshold's — walks the waiter list and fires a blocked process report for any chain that has been blocked longer than the threshold — deadlock-graph-shaped XML carrying the full waiter stack and both input buffers. Capture it with an Extended Events session on sqlserver.blocked_process_report writing to a file target. I leave this on permanently on every production instance I own. It costs nothing when there is no blocking, and it is the difference between a postmortem with evidence and a postmortem with vibes. Keep the threshold below the application's command timeout, or the report fires after the client has already given up and the chain has dissolved.
Kill or wait: how do I actually decide?
The framework I use is four questions, in order. First: is the lead blocker actively working? Check its status and whether its reads and writes in sys.dm_exec_sessions are still climbing — a running blocker making progress will usually finish, and killing it throws that work away. Second: what is it holding and how stale is it? A sleeping session with an old last_request_end_time is abandoned work, and waiting will never fix abandoned. Third: what does rollback cost? A KILL against a session that already wrote for forty minutes rolls back for roughly that long while holding the same locks the entire time — after the kill, sys.dm_exec_requests shows the session as KILLED/ROLLBACK with percent_complete and estimated_completion_time, which at least gives you a number to tell the business. Fourth: how many victims? One sleeping blocker behind forty waiting sessions is an easy kill; one blocker finishing a payroll batch behind two reports is an easy wait.
The wrong answer is KILL as a reflex. I once killed the head of a chain at 2 AM, felt clever for about eleven seconds, and then watched the rollback hold the same locks for thirty-eight minutes while percent_complete crept upward. The victims waited either way. Since then the rule stands: sleeping and abandoned, kill immediately. Running and progressing, wait one business-defined grace period and re-evaluate. Huge rollback risk, schedule the kill for the next lull instead of now. And write the session id, the batch text, and the lock list into the ticket before you kill anything — the evidence dies with the session.
Watching blocking chains with MonPG when SQL Server support lands
The counters that matter here are chain depth, chain duration, wait_time grouped by wait_type, and how often the lead blocker is a sleeping session with open_transaction_count above zero — that last one is the app-bug signal, and it deserves its own graph. MonPG monitors PostgreSQL in production today, and SQL Server monitoring is on the roadmap and in active development; the SQL Server monitoring (coming soon) page carries the honest status. Until it ships, the blocked process report plus the two DMVs above is the whole toolkit — which, honestly, is enough, as long as someone looks at it before the tickets arrive.