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

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.

Quick checklist

1. List blocked sessions with sys.dm_exec_requests.
2. Find the head blocker, including sleeping sessions with open transactions.
3. Read its last statement, host and application.
4. Decide carefully whether to wait or KILL.
5. Capture blocked process reports to find recurring patterns.
6. Fix the code, indexes or isolation level behind it.

Related reading:

• How to Fix SQL Server Deadlocks
• How to Find Slow Queries in SQL Server
• SQL Server Isolation Levels Explained