SQL Server problems
SQL Server Blocking: Find the Head Blocker and Clear the Chain
22 November 2026
When an application freezes on SQL Server, the list of blocked sessions is long and nearly all of it is noise. The sessions wait behind each other in a chain, and one session at the head of the chain holds everybody. Find that one and the rest clears by itself.
Who waits behind whom
Every request that waits on a lock carries the session that blocks it:
SELECT r.session_id, r.blocking_session_id,
r.wait_type, r.wait_time / 1000 AS wait_s,
r.wait_resource,
DB_NAME(r.database_id) AS database_name,
SUBSTRING(t.text, 1, 60) AS sql_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;
Wait types that start with LCK_M_ are lock waits. The list shows the waiters. The session to find is the one that appears as a blocker and is not blocked itself.
The head blocker
WITH waiters AS (
SELECT session_id, blocking_session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
)
SELECT s.session_id, s.status, s.login_name,
s.host_name, s.program_name,
s.open_transaction_count AS open_tran,
s.last_request_end_time,
(SELECT COUNT(*) FROM waiters w
WHERE w.blocking_session_id = s.session_id)
AS direct_waiters,
SUBSTRING(t.text, 1, 60) AS last_sql
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c
ON c.session_id = s.session_id
AND c.parent_connection_id IS NULL
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE s.session_id IN (SELECT blocking_session_id
FROM waiters)
AND s.session_id NOT IN (SELECT session_id FROM waiters);
Two kinds of head show up, and they need different answers:
- A running head. Status
runningorsuspended: a long update, an index rebuild, a batch. It will finish, and the question is only whether the application can wait. - A sleeping head. Status
sleepingwithopen_tranabove zero: the application opened a transaction, ran its statement and never committed. It will not finish by itself.last_request_end_timesays since when.
What the head holds
SELECT l.resource_type,
DB_NAME(l.resource_database_id) AS database_name,
l.resource_associated_entity_id AS entity_id,
l.request_mode, l.request_status,
COUNT(*) AS locks
FROM sys.dm_tran_locks l
WHERE l.request_session_id = 57
GROUP BY l.resource_type, l.resource_database_id,
l.resource_associated_entity_id,
l.request_mode, l.request_status
ORDER BY locks DESC;
For a row of type OBJECT, OBJECT_NAME(entity_id, database_id) gives the table. An X lock on an object, or thousands of KEY locks that escalated to one, explains why every other session of that table waits.
The fix, in order
- Decide on the head, not on the waiters. Killing waiters frees nothing: the next request queues behind the same head.
- A sleeping head: end its transaction. Ask the owner to commit or roll back. When nobody owns it,
KILL 57;returns its locks. - A running head: weigh the rollback. A statement that worked for an hour rolls back for a long time too, and holds its locks while it does.
KILL 57 WITH STATUSONLY;shows the progress of a rollback. Often waiting is faster. - Stop readers from waiting on writers.
ALTER DATABASE [Sales] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;makes readers see the last committed version instead of waiting. Test it first: it changes what a long report reads, and it keeps row versions. - Shorten what holds the lock. An update without a useful index locks far more rows than it changes. Batches that commit every few thousand rows never escalate to a table lock.
- Bound the wait in the application.
SET LOCK_TIMEOUT 5000;returns error 1222 after five seconds instead of holding a thread forever.
See it coming
A chain builds over minutes before anybody calls. Radar Engine, the detection layer of Scops, reads the chains on every instance, with no agent on the server, and tells a real pile-up from the short chains every busy system has:
| Detector | When it fires |
|---|---|
| Blocking pile-up | Warning: at least 10 lock waiters behind one head, the longest waiting 3 minutes or more. Warning too: a sleeping head with an open transaction that holds 3 or more waiters for 5 minutes. Critical: at least 20 lock waiters that are at least half of the active requests. |
| Deadlock burst | Watch: 3 or more deadlocks in 10 minutes, at 3 times the rate of the hour before. Warning: 5 or more in 10 minutes, more than in the whole hour before. |
| Wait wave | The instance's waits move to one category, far from the usual level of that hour. |
Waiters of unrelated chains are never added up, so ten short chains do not look like one long one. The finding names the head session, its program and what it does, and Live Activity shows the chain as a tree, now or at any moment of the past.
Questions
What is a head blocker in SQL Server?
The session at the top of a blocking chain: it blocks other sessions and waits for nobody. Every other session of the chain waits, directly or through others, for the locks it holds.
Why does a sleeping session block others?
Because it has an open transaction. The application began a transaction, ran a statement and did not commit, so the locks stay until it commits, rolls back or disconnects. In sys.dm_exec_sessions it shows as sleeping with open_transaction_count above zero.
Is blocking the same as a deadlock?
No. In blocking, sessions wait in a line and move again when the head finishes. In a deadlock, two sessions wait for each other, and SQL Server ends one of them with error 1205.
Does NOLOCK fix blocking?
It stops a reader from waiting, at the price of reading rows that are not committed, twice or not at all. READ_COMMITTED_SNAPSHOT gives readers that do not wait and still read committed data.
Related problems
- Error 9002: the transaction log is full
- tempdb is full: what uses it
- Error 1205: read the deadlock graph
- All SQL Server problems
- SQL Server monitoring without agents