SQL Server blocking chains: finding the head blocker before workers run out

SQL Server is unresponsive. CPU and I/O counters are low. Connections succeed but queries hang. Timeouts and login failures follow. This is the shape of a blocking chain that has crossed into worker-thread exhaustion.

One session holds a lock. Conflicting sessions queue behind it, each waiting on an LCK_M_* wait and each pinning a worker from SQL Server’s fixed-size pool. As the chain deepens, the worker pool drains. Once exhausted, new requests get THREADPOOL waits and the instance appears down to applications, even though the OS shows sqlservr healthy and storage idle.

The window between “annoying block” and “instance effectively down” is measured in minutes. Raising max worker threads is not the fix. The fix is to identify the head blocker, kill it if necessary, and remove whatever condition allowed it to hold a lock while doing no useful work. The most dangerous head blockers are idle: sleeping sessions with an open transaction that will not self-resolve.

What this means

A blocking chain is a directed graph rooted at the head blocker. The head blocker holds a lock resource (row, page, table, or schema) that one or more other sessions need. Each blocked session acquires a worker, transitions to suspended, and waits. If those blocked sessions themselves hold locks that further sessions need, the chain deepens into a tree.

flowchart TD
    HB["Head blocker
(session_id=52)"] -->|"holds X lock on row"| B1["Session 60
LCK_M_X wait"]
    HB -->|"holds X lock on row"| B2["Session 65
LCK_M_S wait"]
    B1 -->|"holds intent lock"| B3["Session 71
LCK_M_IS wait"]
    B2 -->|"holds intent lock"| B4["Session 72
LCK_M_IS wait"]
    B3 --> B5["Worker pool drains
THREADPOOL appears"]
    B4 --> B5

Each node consumes a worker. SQL Server’s max worker threads is fixed at instance startup based on the formula 512 + ((logical_CPUs - 4) * 16) for 64-bit systems with 4 to 64 CPUs. Most production instances run with 512 to 2048 workers. A chain wide or deep enough to consume them all produces THREADPOOL waits, which is functionally equivalent to connection refusal.

The signature pattern, taken from sys.dm_os_wait_stats and the live DMVs, is:

  • Low OS CPU utilization (workers are suspended, not running)
  • Low disk I/O (no useful work is proceeding)
  • Many sessions in suspended state on LCK_M_* waits
  • One session at the root of the chain, often with status = sleeping and open_transaction_count > 0

The sleeping head blocker is the trap. It has no active row in sys.dm_exec_requests, so naive blocking-chain queries miss it. It will not self-resolve. Until the SPID is killed, every worker that conflicts with its locks piles up.

Common causes

CauseWhat it looks likeFirst thing to check
Application bug: open transaction not committedHead blocker status = sleeping, open_transaction_count > 0, wait_type IS NULLsys.dm_exec_sessions joined to sys.dm_tran_session_transactions
Lock escalation to table lockSudden spike in LCK_M_* waits across many sessions on the same object after a large updateDefault trace or Extended Events for lock escalation
DDL holding SCH_M locksSchema deployment, index rebuild, statistics update during business hourssys.dm_exec_requests command column
Long-running batch operationHead blocker status = running, single statement, high wait_time on a resource other than locksQuery text and execution plan
Interactive session abandonedSSMS session with open transaction, user walked awayprogram_name, host_name, last_request_start_time

Quick checks

Run these as soon as blocking is suspected. All are read-only.

-- Blocked requests ordered by wait time
SELECT
    r.session_id AS blocked_session,
    r.blocking_session_id AS blocker,
    r.wait_type,
    r.wait_time / 1000 AS wait_seconds,
    DB_NAME(r.database_id) AS database_name,
    t.text AS blocked_query_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;

-- Identify the head blocker: a session that blocks others but is not itself blocked.
-- Note: a sleeping head blocker has no row in sys.dm_exec_requests, so it is
-- captured here only because others report it as blocking_session_id.
SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.open_transaction_count,
    s.last_request_start_time,
    s.last_request_end_time,
    (SELECT COUNT(*) FROM sys.dm_exec_requests r2
     WHERE r2.blocking_session_id = s.session_id) AS sessions_blocked
FROM sys.dm_exec_sessions s
WHERE s.session_id IN (
    SELECT DISTINCT blocking_session_id FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0
)
AND s.session_id NOT IN (
    SELECT session_id FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0
)
ORDER BY sessions_blocked DESC;

-- THREADPOOL waits: confirms exhaustion has begun
SELECT wait_type, waiting_tasks_count, wait_time_ms,
       wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'THREADPOOL';

-- Worker pool saturation per scheduler
SELECT scheduler_id, current_tasks_count, runnable_tasks_count,
       active_workers_count, work_queue_count
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
ORDER BY work_queue_count DESC;

-- Instance max worker threads (reference, not a metric to tune up blindly)
SELECT max_workers_count FROM sys.dm_os_sys_info;

If work_queue_count > 0 appears alongside THREADPOOL waits and many LCK_M_* blocked sessions, the cascade is in progress. The Dedicated Admin Connection (DAC) remains accessible when normal connections are refused, and is the recommended path for intervention when the instance appears hung.

How to diagnose it

  1. Confirm the pattern. Check OS CPU and disk latency. If both are low while connections queue or fail, blocking is the leading hypothesis. Verify against sys.dm_os_waiting_tasks for the dominant wait type.
  2. List all blocked sessions. Run the first quick-check query. Note the depth and width of the chain, and which database and object are at the root.
  3. Find the head blocker. Run the second quick-check query. A head blocker that appears in sys.dm_exec_sessions but not in sys.dm_exec_requests is sleeping. That is the most dangerous case.
  4. Assess the head blocker. Look at:
    • status: sleeping indicates no active work
    • open_transaction_count > 0: an uncommitted transaction is holding locks
    • last_request_end_time: how long it has been idle with locks held
    • program_name and host_name: which application or user originated it
    • Query text from sys.dm_exec_sql_text(s.most_recent_sql_handle): the last statement, which often reveals the application bug
  5. Check for lock escalation. If the head blocker is not sleeping but is running a large update, the chain may be the result of escalation to a table lock. This usually requires query or index tuning, not killing the session.
  6. Capture evidence before action. Save the output of sp_whoisactive @find_block_leaders = 1, @sort_order = '[blocked_session_count] desc' if available, or the DMV queries above. You will want this for the post-mortem once the head blocker is killed and rollback begins.
  7. Decide on intervention. If the head blocker is sleeping with an open transaction and the chain is wider than a handful of sessions, kill the SPID. If it is running a legitimate workload, decide whether to let it finish or kill it based on business priority.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
Blocking chain count and depthLeading indicator of an approaching cascadeAny chain deeper than 5 sessions, or any single block longer than 60 seconds
LCK_M_* wait time as a proportion of total waitsReveals blocking before users complainSustained above 10% of total wait time
Worker thread utilization (active_workers_count / max_workers_count)Approaching 80% leaves no headroom for a chainSustained above 70%
THREADPOOL wait timeExhaustion has begunAny nonzero value with concurrent probe failures
Batch requests/sec with flat or rising user connectionsThroughput collapse with sessions stuckDrop without proportional connection drop
Transactions/sec relative to batch requests/secQueries arriving but not committingRatio falls sharply
open_transaction_count per sleeping sessionFuture head blockers in waitingSleeping sessions with open_transaction_count > 0

Fixes

Kill the head blocker when it is sleeping with an open transaction

KILL <session_id>;

This is the standard intervention when a sleeping session with open_transaction_count > 0 is blocking a meaningful chain. Two warnings apply.

First, the rollback may take as long as, or longer than, the original transaction. If the session modified many rows, rollback without Accelerated Database Recovery (ADR) is single-threaded and can take minutes or hours. With ADR enabled on SQL Server 2019 and later, rollback is significantly faster because ADR tracks versions and can revert changes without undoing each operation sequentially.

Second, killing a session that holds locks inside an application transaction surfaces to the application as an error. Make sure the application has retry logic, otherwise the kill just moves the problem one tier up.

Eliminate sleeping-with-open-transaction blockers at the source

These blockers exist because an application opened a transaction, did some work, and then returned the connection to the pool without committing or rolling back. Common root causes:

  • Client-side timeout that aborted the request but left the SQL Server transaction open
  • Application exception path that did not call rollback
  • Connection pooling reuse that abandoned the session state

The structural fix is to ensure every code path that opens a transaction has a matching commit or rollback. SET XACT_ABORT ON at the session or procedure level causes SQL Server to automatically roll back and unbind the transaction if the batch errors, and also handles client disconnects that would otherwise leave the transaction open.

Enable RCSI to remove reader/writer blocking

Read Committed Snapshot Isolation eliminates the most common form of blocking: readers waiting on writers and writers waiting on readers. Under RCSI, readers see the last committed version of a row from the version store in TempDB instead of acquiring a shared lock and waiting. This removes a large class of blocking chains entirely.

The trade-off is TempDB version-store overhead. Every modification generates a row version that must be retained until no active transaction needs it. Long-running transactions under RCSI cause the version store to grow, which can produce TempDB space pressure and elevated version_ghost_record_count. Before enabling RCSI, ensure TempDB has headroom and that long-running transactions are not a normal pattern. RCSI can also expose deadlocks that were previously masked by serial blocking.

Fix lock escalation and bad access paths

If the head blocker is running a legitimate workload but the chain is rooted in a table lock from escalation, the fix is query or index tuning:

  • Batch large updates in smaller chunks to stay below the escalation threshold (approximately 5000 locks per table per statement)
  • Ensure queries use indexes that limit the lock footprint
  • Review MAXDOP for queries that hold locks across parallel threads

Lock hints such as UPDLOCK, XLOCK, and HOLDLOCK change lock granularity and duration. Review them critically in hot paths.

Prevention

  • Monitor blocking chains continuously, not just during incidents. A 30-second poll for chains deeper than N sessions or longer than N seconds catches head blockers before the cascade.
  • Audit application code for transaction hygiene. Every BEGIN TRAN must have a guaranteed commit or rollback in every path. CI checks for unbalanced transaction scope prevent regressions.
  • Use SET XACT_ABORT ON as the default in stored procedures and application connections that issue multi-statement transactions.
  • Consider RCSI for OLTP databases, with TempDB sized for the version store and proactive monitoring of version_ghost_record_count.
  • Schedule DDL and index maintenance outside business hours. SCH_M locks are incompatible with everything.
  • Track worker thread utilization as a leading indicator. Sustained values above 70% of max_workers_count leave no room to absorb a blocking event.
  • Pre-provision the DAC. Document how to connect via the Dedicated Admin Connection before you need it.

How Netdata helps

Netdata surfaces the composite signals of a blocking cascade with per-second granularity, which is the resolution needed to see the chain form before it deepens into THREADPOOL.

  • Wait statistics trends show LCK_M_* waits rising sharply as the chain forms, and THREADPOOL appearing once exhaustion begins. Delta sampling exposes the current pattern rather than the cumulative since-startup view that masks it.
  • Batch requests/sec and transactions/sec together reveal the paradox signature: requests arriving, transactions not completing. A divergence between these two counters is one of the earliest indicators of a chain.
  • Worker thread utilization against max_workers_count is the saturation signal. Per-scheduler work_queue_count confirms active exhaustion.
  • Blocking chain length and duration, sampled continuously, gives the head blocker’s session ID and wait time without a manual query.
  • CPU and I/O latency correlation confirms the diagnostic pattern: low CPU, low I/O latency, and many suspended sessions is blocking, not a resource bottleneck.

Netdata’s Microsoft SQL Server monitoring brings these signals together with per-second metrics and anomaly detection.