SQL Server

How to Find Blocking in SQL Server

Find blocked requests, trace blocking chains to the head blocker, and inspect the responsible session and transaction safely.

By JaviPublished Updated 15 min readIntermediate

Three SQL Server sessions connected in a blocking chain, with lock indicators on waiting requests

Short answer: query sys.dm_exec_requests for requests with a non-zero blocking_session_id, follow each blocker until you reach a session that blocks others but is not blocked itself (the head blocker), then inspect that session’s transaction age, owner and last SQL. Collect that evidence before anyone acts.

Is blocking the active incident? Once blocking is confirmed and users are affected, switch to the SQL Server Blocking Runbook for the step-by-step operational procedure. This page explains how to find and read the evidence.

Not sure it is blocking? To see all active work first, use Find Running Queries.

Blocking occurs when one session must wait for another session to release an incompatible lock. That coordination is fundamental to transactional correctness. The production problem is prolonged or unexpected blocking that exceeds the workload’s latency objective, creates a queue, or prevents log reuse.

A blocked request is usually the victim of the dependency, not the root cause. Ending blocked sessions reduces the visible queue but does not explain why the blocking transaction remains open.

Problem

Users report that SalesDb requests are timing out. Several sessions show LCK_M_* waits. You need to map who blocks whom, find the session at the head of the chain, and inspect its current or most recent batch and transaction age.

Quick diagnostic

This query lists active blocked requests. Run it more than once; a brief block that disappears can be normal, while a stable chain with increasing wait time is more concerning.

SQL query: Quick diagnosticT-SQL
SELECT
    SYSDATETIMEOFFSET() AS captured_at,
    r.session_id AS blocked_session_id,
    r.request_id,
    r.blocking_session_id,
    r.status,
    r.wait_type,
    r.wait_time AS wait_time_ms,
    r.wait_resource,
    DB_NAME(r.database_id) AS database_name,
    s.login_name,
    s.host_name,
    s.program_name,
    r.start_time AS request_start_time,
    r.open_transaction_count,
    SUBSTRING(
        sql_text.text,
        (r.statement_start_offset / 2) + 1,
        ((CASE r.statement_end_offset
            WHEN -1 THEN DATALENGTH(sql_text.text)
            ELSE r.statement_end_offset
          END - r.statement_start_offset) / 2) + 1
    ) AS blocked_statement
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS sql_text
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;
T-SQL29 linesRead-only query display. It is not connected to a database.

Direct blocker, blocking chain and head blocker

  1. BlockedSession 92LCK_M_S, blocking_session_id = 81
  2. waits on
    Blocked and blockingSession 81LCK_M_X, blocking_session_id = 65
  3. waits on
    Head blockerSession 65Sleeping, open transaction, no active request
Direct blocker, chain and head blocker. Session 92 waits on 81 and 81 waits on 65. Only 65 waits on nothing, so it is the head blocker, and it is often a sleeping session holding an open transaction.
  • Direct blocker: the session named in a request’s blocking_session_id. For session 92 that is 81.
  • Blocking chain: the sequence of waits from a blocked request up to the session that holds what everyone waits for: 92 → 81 → 65. Several chains can converge on one blocker.
  • Head blocker: a session that blocks others and is not blocked itself. Investigating only 81 misses the beginning of the chain; 65 is the session whose transaction matters.
  • Sleeping head blocker: a session with status sleeping, no row in sys.dm_exec_requests and open_transaction_count > 0. The application started a transaction and stopped sending work without committing or rolling back: an error path that skipped the rollback, a pooled connection returned mid-transaction, or a user prompt inside a transaction. Active-request queries cannot show it, which is why the head-blocker query below starts from sys.dm_exec_sessions.

Query: show the blocking chain

The recursive query begins with blocked requests and walks upward through active blockers. It guards against cycles and caps recursion. It describes the current request graph; a sleeping head blocker is enriched later because it has no active request row.

SQL query: Show the blocking chainT-SQL
;WITH blocking_chain AS
(
    SELECT
        r.session_id,
        r.blocking_session_id,
        r.database_id,
        r.wait_type,
        r.wait_time,
        r.wait_resource,
        0 AS chain_level,
        CAST(CONCAT('/', r.session_id, '/') AS varchar(max)) AS visited
    FROM sys.dm_exec_requests AS r
    WHERE r.blocking_session_id > 0

    UNION ALL

    SELECT
        blocker.session_id,
        blocker.blocking_session_id,
        blocker.database_id,
        blocker.wait_type,
        blocker.wait_time,
        blocker.wait_resource,
        chain.chain_level + 1,
        CAST(CONCAT(chain.visited, blocker.session_id, '/') AS varchar(max))
    FROM blocking_chain AS chain
    INNER JOIN sys.dm_exec_requests AS blocker
        ON blocker.session_id = chain.blocking_session_id
    WHERE chain.visited NOT LIKE CONCAT('%/', blocker.session_id, '/%')
)
SELECT DISTINCT
    chain.chain_level,
    chain.session_id,
    NULLIF(chain.blocking_session_id, 0) AS blocking_session_id,
    DB_NAME(chain.database_id) AS database_name,
    session_info.login_name,
    session_info.host_name,
    session_info.program_name,
    chain.wait_type,
    chain.wait_time AS wait_time_ms,
    chain.wait_resource
FROM blocking_chain AS chain
INNER JOIN sys.dm_exec_sessions AS session_info
    ON session_info.session_id = chain.session_id
ORDER BY chain.chain_level, chain.session_id
OPTION (MAXRECURSION 32);
T-SQL46 linesRead-only query display. It is not connected to a database.

Because multiple blocked leaves may converge on the same blocker, DISTINCT removes repeated chain nodes. It does not aggregate counters. The highest chain level reached from a leaf is not by itself a severity score; focus on sessions that block others and have no positive blocker of their own.

Query: inspect the head blocker

Set the identified head session once. This query works for an active or sleeping session and exposes transaction age and its current or most recent text.

SQL query: Inspect the head blockerT-SQL
DECLARE @HeadBlockerSessionId int = 65;

SELECT
    SYSDATETIMEOFFSET() AS captured_at,
    s.session_id,
    s.login_time,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(COALESCE(r.database_id, s.database_id)) AS database_name,
    r.status AS request_status,
    r.command,
    r.start_time AS request_start_time,
    r.wait_type,
    r.cpu_time AS cpu_time_ms,
    r.logical_reads,
    r.writes,
    s.open_transaction_count,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds,
    log_use.log_bytes_used,
    sql_text.text AS current_or_most_recent_batch
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections AS c
    ON c.session_id = s.session_id
LEFT JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
LEFT JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
OUTER APPLY (
    SELECT SUM(dt.database_transaction_log_bytes_used) AS log_bytes_used
    FROM sys.dm_tran_database_transactions AS dt
    WHERE dt.transaction_id = st.transaction_id
) AS log_use
OUTER APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle, c.most_recent_sql_handle)) AS sql_text
WHERE s.session_id = @HeadBlockerSessionId;
T-SQL39 linesRead-only query display. It is not connected to a database.

How to read the result

Lock waits start with LCK_M_; the suffix describes the requested lock mode. The wait resource can identify a database, object, page, key, or metadata resource, but decoding it is a step toward the locked operation, not an automatic fix. Determine the statements, transaction boundaries, isolation level, affected rows, and index access paths.

An active head blocker may be doing legitimate work. A sleeping head blocker with an old open transaction is particularly important because the client may have stopped sending work without committing or rolling back. The most recent batch provides a clue but may not be the statement that acquired every retained lock.

What the result may mean

Long transactions, large updates, unselective predicates, scans that touch more keys, user interaction inside a transaction, and missing commit/rollback paths can all extend blocking. Poor indexing can increase the number and duration of locked resources, but adding an index is not automatically the correct response. Isolation-level changes alter consistency semantics and should be architectural decisions, not incident shortcuts.

Deadlocks are different. In a deadlock, sessions form a cycle and SQL Server chooses a victim so another transaction can continue. Ordinary blocking is a line or tree of waiting that can persist. Investigate deadlocks with deadlock graphs; do not expect this snapshot alone to reconstruct a deadlock that already ended.

What to check next

Inspect the head blocker’s plan, transaction code, and application ownership. Check how many rows it changes, whether predicates are selective, and whether errors can bypass rollback. For recurring incidents, capture blocked-process reports or an appropriate Extended Events session so the evidence survives completion.

Evidence to collect before acting

Evidence Where it comes from Why it matters
Head blocker session_id and login_time sys.dm_exec_sessions Session IDs are reused; login_time confirms you are still looking at the same connection
Number of blocked requests and the longest wait The first query, run several times Shows whether the chain is growing, stable or clearing
Transaction begin time and age sys.dm_tran_active_transactions An old transaction holds locks longer and can prevent log reuse
Transaction log bytes used sys.dm_tran_database_transactions A rough indication of how much work a rollback would have to undo
Login, host and application sys.dm_exec_sessions Who owns the session, and who to contact
Current or most recent SQL sys.dm_exec_sql_text, or sys.dm_exec_input_buffer What the transaction was doing; not proof of the statement that took every lock
Wait type and wait resource of the blocked requests sys.dm_exec_requests The lock mode requested and the object, page or key involved
Database and request start time sys.dm_exec_requests Scope of the impact and how long it has lasted

Timestamp every capture and store it outside the affected database. During an incident, the runbook has a fill-in evidence template to complete before any mitigation.

Common mistakes

  • Killing the blocked session while leaving the head transaction open.
  • Assuming all blocking is abnormal and weakening isolation without requirements analysis.
  • Reading one snapshot without measuring how long the condition persists.
  • Treating most_recent_sql_handle as definitive proof of the statement holding every lock.
  • Using NOLOCK as a universal solution despite inconsistent and missing results.
  • Confusing a blocking chain with a deadlock cycle.

Production notes and the risk of KILL

If you are handling an active production incident, use the SQL Server Blocking Runbook.

KILL is not a diagnostic query. Ending a head blocker rolls back its open transaction, and rollback can take as long as—or longer than—the original work while continuing to hold locks. It can also interrupt a critical business operation. If termination is necessary to protect availability, verify the session identity again, coordinate with the owner, capture evidence, and plan for rollback monitoring.

DMV output is transient and permissions vary by SQL Server version. Persist timestamped samples securely. A durable solution normally reduces transaction scope or duration, corrects application transaction handling, improves the accessed plan, or changes concurrency design while preserving the required consistency.

Continue the performance path

Use blocking evidence within the performance tuning workflow. If the report is a broad slowdown rather than a known blocking problem, start with SQL Server Is Slow: the first 15 minutes. Return to running queries, inspect the blocker with the session ID/SPID guide, and use Query Store regressions when the slowdown is historical. Quick references: Blocking and Open Transactions.

Sources

Checked November 2026. To see other sessions, SQL Server 2019 and earlier need VIEW SERVER STATE; SQL Server 2022 and later document VIEW SERVER PERFORMANCE STATE for the session and transaction DMVs.

Tags

  • SQL Server
  • Performance
  • Troubleshooting
  • DMV
  • Blocking
  • Concurrency