SQL Server Blocking: How to Find the Head Blocker and Fix It

Originally published at SQL & Cloud Lab.

Blocking happens when one session holds a lock and another session needs it. A little blocking is normal and clears in milliseconds. The problem is a blocking chain: one session holds locks for a long time, others queue behind it, and then more queue behind them. Users see a frozen application. The key is to find the session at the front of the chain, the head blocker.

Step 1: see who is blocked right now

SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time / 1000 AS wait_seconds, r.status, DB_NAME(r.database_id) AS database_name, t.text AS 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;

Each row is a session that is waiting. blocking_session_id tells you who it is waiting for, and lock waits show up as LCK_M_* wait types. But the blocker shown here may itself be blocked by someone else. You need the head of the chain.

Step 2: find the head blocker

The head blocker is a session that blocks others but is not blocked itself. Very often it is not even running: it is a "sleeping" session that started a transaction and never committed. This query finds it, including sleeping sessions, and shows their last statement:

SELECT s.session_id, s.status, s.login_name, s.host_name, s.program_name, s.open_transaction_count, s.last_request_end_time, t.text AS last_statement FROM sys.dm_exec_sessions s JOIN sys.dm_exec_connections c ON c.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t WHERE s.session_id IN (SELECT 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);

What to look for:

status = sleeping with open_transaction_count > 0: the application opened a transaction and did not commit or roll back, often after an unhandled exception, or while waiting for user input.
status = running: a long query or batch is genuinely working; check what it is doing and how far it has got.
host_name and program_name tell you which server and application to investigate.

Step 3: decide whether to wait or kill

Killing the head blocker releases its locks, but it also rolls back everything it changed. A session that has been updating rows for an hour can take a long time to roll back, and the blocking continues until it finishes. Before using KILL:

Confirm with the application owner what the session is doing.
Prefer to let a legitimate long job finish if it is nearly done.
For a forgotten sleeping transaction with little work done, KILL is usually quick and safe.

KILL 87; -- the head blocker's session_id KILL 87 WITH STATUSONLY; -- check rollback progress

Step 4: capture blocking you missed

Blocking often happens when nobody is watching. Set a blocked process threshold so SQL Server reports any block that lasts longer than a number of seconds, and capture it with Extended Events (the blocked_process_report event):

EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'blocked process threshold (s)', 15; RECONFIGURE;

Each report shows the blocked and blocking statements, so you can find patterns over days instead of guessing.

Step 5: fix the root cause

Open transactions in the app: make sure every BEGIN TRAN path commits or rolls back, including error paths. In .NET, wrap transactions in using blocks so they are disposed.
Long transactions: move validation and slow work outside the transaction and keep only the writes inside.
Missing indexes: an UPDATE or DELETE that scans a table locks far more rows than it changes. Index the columns in its WHERE clause.
Huge single statements: break large deletes and updates into batches to avoid lock escalation.
Readers blocked by writers: consider enabling READ_COMMITTED_SNAPSHOT so readers use row versions instead of waiting.