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:

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

  1. Decide on the head, not on the waiters. Killing waiters frees nothing: the next request queues behind the same head.
  2. A sleeping head: end its transaction. Ask the owner to commit or roll back. When nobody owns it, KILL 57; returns its locks.
  3. 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.
  4. 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.
  5. 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.
  6. 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:

DetectorWhen it fires
Blocking pile-upWarning: 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 burstWatch: 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 waveThe 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

Sources