SQL Server: How to Find the Lead Blocker — DMV Queries and Blocking Chain Analysis

---

Blocking is one of the most disruptive and misdiagnosed SQL Server performance problems. When users report slow queries or application timeouts during what appears to be low activity, blocking is often the cause — not CPU or I/O pressure.

The challenge is that blocking chains can involve many sessions. Session 55 is blocked by session 42, which is blocked by session 38, which is blocked by session 17. Session 17 is the one causing the problem. That is the lead blocker. Kill the wrong session and nothing changes.

What Is a Lead Blocker?

In SQL Server, a lead blocker (also called a root blocker or head blocker) is a session that:

• Holds a lock on a resource
• Is not itself blocked by any other session
• Has one or more sessions waiting on its locks

In sys.dm_exec_requests and sysprocesses, the lead blocker is the session where blocking_session_id = 0 (not blocked by anyone), but other sessions show this session’s ID as their blocker.

Common causes of lead blockers include long-running explicit transactions left open, uncommitted transactions in application code, missing indexes causing large lock escalations, and batch operations that hold locks longer than expected.

Understanding the Blocking Chain

A blocking chain can be shallow (one blocker, one blocked session) or deep (many layers). The recursive CTE query in this article walks the entire chain regardless of depth.

Query 1 — Instant Snapshot with Lock and Object Details Recommended

This query uses modern DMVs to show active blocking with the requesting and blocking session query text, the locked object, lock type, and request mode — all in one result set. Best for live incident diagnosis.

-- Lead Blocker: Instant snapshot with lock details and both query texts
SELECT
    db.name                              AS DBName,
    tl.request_session_id                AS blocked_spid,
    wt.blocking_session_id               AS blocking_spid,
    OBJECT_NAME(p.OBJECT_ID)             AS BlockedObjectName,
    tl.resource_type,
    tl.request_mode,
    h1.TEXT                              AS RequestingText,
    h2.TEXT                              AS BlockingText
FROM sys.dm_tran_locks                   AS tl
INNER JOIN sys.databases                 db
    ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks       AS wt
    ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions                AS p
    ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections       ec1
    ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections       ec2
    ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2;

Columns Returned

Column	What It Tells You
DBName	Which database the lock is in
blocked_spid	The session waiting for the lock
blocking_spid	The session holding the lock — this is your lead blocker
BlockedObjectName	The table or object being locked
resource_type	Type of resource locked (OBJECT, PAGE, KEY, RID)
request_mode	Lock mode being requested (S, X, U, IS, IX)
RequestingText	Full query text of the blocked session
BlockingText	Full query text of the blocking session — what is holding the lock

Query 2 — Recursive Blocking Chain with Lead Blocker and Level Updated

This recursive CTE walks the entire blocking chain from the lead blocker down to the deepest blocked session, showing each session’s level in the chain and its query text. Ideal for deep chains involving many sessions.

-- Recursive Lead Blocker Chain (modern version — works on SQL Server 2012+)
WITH LeadBlockers (blocker, blockee, level, sql_text) AS
(
    -- Anchor: sessions that are NOT blocked but ARE blocking others (lead blockers)
    SELECT
        s.blocked   AS blocker,
        s.spid      AS blockee,
        0           AS level,
        t.text      AS sql_text
    FROM master..sysprocesses s
    OUTER APPLY sys.dm_exec_sql_text(s.sql_handle) t
    WHERE s.blocked = 0
    AND EXISTS
    (
        SELECT 1 FROM master..sysprocesses inner_s
        WHERE inner_s.blocked = s.spid
    )

    UNION ALL

    -- Recursive: sessions blocked by sessions already in the CTE
    SELECT
        s.blocked   AS blocker,
        s.spid      AS blockee,
        lb.level + 1,
        t.text      AS sql_text
    FROM master..sysprocesses s
    INNER JOIN LeadBlockers lb ON s.blocked = lb.blockee
    OUTER APPLY sys.dm_exec_sql_text(s.sql_handle) t
)
SELECT
    blocker,
    blockee,
    level,
    LEFT(sql_text, 500) AS sql_text
FROM LeadBlockers
ORDER BY level, blocker;

How to Read the Results

Column	What It Means
blocker	SPID of the session causing the block. At level 0, this is 0 — meaning the lead blocker is blocked by nobody.
blockee	SPID of the session being blocked. At level 0, this is your lead blocker SPID.
level	Depth in the chain. Level 0 = lead blocker. Level 1 = directly blocked by lead blocker. Level 2 = blocked by a level-1 session, and so on.
sql_text	The query text currently executing or most recently executed in that session.

Query 3 — Simple Blocking Detection with Wait Info

The simplest and fastest blocking check. Returns all sessions currently blocked, their blocker, wait type, wait time, and wait resource. No joins required — runs instantly even under heavy load.

-- Simple blocking detection: all currently blocked sessions
SELECT
    r.session_id                         AS blocked_spid,
    r.blocking_session_id                AS blocking_spid,
    r.wait_type,
    r.wait_time                          AS wait_time_ms,
    r.wait_resource,
    DB_NAME(r.database_id)               AS database_name,
    LEFT(t.text, 500)                    AS blocked_query_text,
    r.status,
    r.cpu_time,
    r.logical_reads,
    r.start_time
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

To also see the blocking session’s query text, extend the query with a self-join:

-- Extended: blocked session + blocker query text side by side
SELECT
    r.session_id                         AS blocked_spid,
    r.blocking_session_id                AS blocking_spid,
    r.wait_type,
    r.wait_time                          AS wait_time_ms,
    r.wait_resource,
    DB_NAME(r.database_id)               AS database_name,
    LEFT(t_blocked.text, 300)            AS blocked_query,
    LEFT(t_blocker.text, 300)            AS blocker_query
FROM sys.dm_exec_requests                r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t_blocked
LEFT JOIN sys.dm_exec_requests           r2
    ON r2.session_id = r.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(r2.sql_handle) t_blocker
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

When to Use Each Query

Reading the Results — Lock Modes Explained

Query 1 returns a request_mode column. Understanding lock modes helps diagnose why sessions are incompatible and waiting:

Mode	Name	What It Means	Compatible With
S	Shared	Reading data — SELECT	S, IS, U
X	Exclusive	Modifying data — INSERT, UPDATE, DELETE	Nothing
U	Update	Intent to update — read phase of update	S, IS
IS	Intent Shared	Table-level intent before row shared lock	IS, S, U, IX, SIX
IX	Intent Exclusive	Table-level intent before row exclusive lock	IS, IX
SIX	Shared Intent Exclusive	Table shared, with intent to update rows	IS

The most common blocking scenario is an X lock (open transaction with an update) blocking an S lock (a read). The X lock is incompatible with everything — it holds until the transaction commits or rolls back.

What to Do After Finding the Lead Blocker

Once you have identified the lead blocker SPID, you have three options depending on the situation:

Option 1 — Wait

If the lead blocker is actively executing a legitimate long-running transaction (visible in sql_text), and the wait is within acceptable bounds, waiting is often the right answer. Killing a valid transaction forces a rollback which may take longer than waiting for it to complete.

Option 2 — Kill the Lead Blocker

If the session is idle (open transaction, no active query), or the blocking is causing unacceptable application impact, kill the lead blocker:

-- Kill the lead blocker — replace 17 with the actual SPID
KILL 17;

Option 3 — Investigate the Root Cause

After resolving the immediate incident, use the query text returned by these queries to investigate the root cause:

• Is the blocking query missing an index that would reduce lock scope?
• Is there an uncommitted transaction being held open by application logic?
• Is lock escalation occurring due to a large number of row locks?
• Would READ_COMMITTED_SNAPSHOT isolation level (RCSI) eliminate the blocking entirely?

-- Check if RCSI is enabled for a database (reduces read/write blocking significantly)
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = DB_NAME();

-- Enable RCSI on a database (test in non-production first)
ALTER DATABASE YourDatabase
SET READ_COMMITTED_SNAPSHOT ON;

References

• Microsoft Docs – sys.dm_tran_locks
• Microsoft Docs – sys.dm_os_waiting_tasks
• Microsoft Docs – sys.dm_exec_requests
• Microsoft Docs – sys.dm_exec_sql_text
• Microsoft Docs – SQL Server Locking and Row Versioning Guide
• Microsoft Docs – ALTER DATABASE SET READ_COMMITTED_SNAPSHOT