sql server hub / blocking guide

SQL Serverblocking guide

SQL Server blocking becomes a problem when one session holds locks long enough to slow users, time out requests, or hold up a business process.

Use this guide while the issue is active or shortly after it happened. The goal is to capture the blocker, waits, locks, query text, plan shape, and timing before the trail disappears. If SQL Server starts choosing victims and rolling sessions back, keep the SQL Server deadlocks guide nearby.

Use the SQL Server indexing guide when the blocker is holding locks too long because of a poor access path. Use the SQL Server monitoring guide when the same blocking pattern keeps returning without enough captured session data.

SQL Server blocking chain diagram with a head blocker and waiting sessions

Guide

Guide~8 min readUpdated 19 Apr 2026

LinkedIn X Email

01What SQL Server blocking is and why it happens
02How to check SQL Server blocking
03How to find the head blocker
04What waits, locks, and open transactions tell you
05SQL Server wait types, lock modes, and transaction states
06How to capture query text and execution plans
07Why transaction scope matters
08How indexing and access paths affect SQL Server blocking
09How isolation level and versioning change blocking
10What not to change during a blocking incident
11When to request a SQL Server performance review

What SQL Server blocking is and why it happens

Blocking means one session is waiting because another session holds a lock on a resource it needs. That is normal database behavior. SQL Server uses locks to protect consistency while transactions read or change data.

The problem starts when the blocking duration, frequency, or affected workload becomes visible to users. A short wait during normal concurrency is not the same thing as a blocking chain that stalls order entry, reporting, batch processing, or API traffic.

Start by capturing the chain. Find the blocked session, the blocking session, the head blocker, the wait type, the locked resource, the open transaction, and the query text. Do that before reaching for index changes, isolation-level changes, or session kills.

Term	What to check
Blocked session	The request waiting for a lock or other resource.
Blocking session	The session currently blocking another request.
Head blocker	The session at the top of the chain that explains the waits below it.
Wait type	The current wait SQL Server reports for the blocked request.
Wait resource	The locked object, key, page, row, or allocation resource involved.
Open transaction	The transaction that can keep locks held after the visible statement changes.

How to check SQL Server blocking

The first query should be read-only and quick. It should show which sessions are waiting, which sessions are blocking, what wait type is involved, how long the wait has lasted, and whether an open transaction is part of the picture.

Run this from a separate session. If the result is empty after the incident, the live blocker is gone and you need monitoring history, Query Store, application logs, job history, or captured snapshots.

How to capture current blocking

Shows active requests with waits or blocking relationships from sys.dm_exec_requests.

SELECT
    r.session_id,
    r.blocking_session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    r.wait_resource,
    r.open_transaction_count,
    r.cpu_time,
    r.logical_reads,
    r.reads,
    r.writes,
    DB_NAME(r.database_id) AS database_name,
    s.host_name,
    s.program_name,
    s.login_name
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
WHERE r.session_id <> @@SPID
  AND (r.blocking_session_id <> 0 OR r.wait_type IS NOT NULL)
ORDER BY
    CASE WHEN r.blocking_session_id = 0 THEN 0 ELSE 1 END DESC,
    r.wait_time DESC;

A non-zero blocking_session_id points to the session currently blocking the request. High wait_time means the current wait has lasted long enough to deserve attention. open_transaction_count greater than zero can explain why locks remain held. If wait_type is null, that request is not currently waiting even if last_wait_type shows a previous wait.

How to find the head blocker

The waiting sessions are downstream. The head blocker is the session that explains why the rest of the chain exists. Without it, the diagnosis stays noisy.

A blocker may be running a statement now, sleeping inside an open transaction, waiting on a different resource, or already between statements. That is why session data and transaction data both matter.

One blocker with many blocked sessions under it.

A blocker with an open transaction and no obvious current work.

A recurring blocker from the same application, host, login, job, or procedure.

A blocker tied to a business process before deciding whether termination is safe.

How to find the head blocker

Shows blocked sessions and attempts to attach current SQL text for the blocking request.

WITH blocking AS (
    SELECT
        session_id,
        blocking_session_id,
        wait_type,
        wait_time,
        wait_resource,
        open_transaction_count
    FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0
)
SELECT
    b.session_id AS blocked_session_id,
    b.blocking_session_id,
    blocker.status AS blocker_status,
    blocker.command AS blocker_command,
    blocker.open_transaction_count AS blocker_open_transactions,
    b.wait_type,
    b.wait_time,
    b.wait_resource,
    blocker_sql.text AS blocker_sql_text
FROM blocking AS b
LEFT JOIN sys.dm_exec_requests AS blocker
    ON blocker.session_id = b.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(blocker.sql_handle) AS blocker_sql
ORDER BY b.wait_time DESC;

The same blocking_session_id appearing many times usually deserves first attention. A null blocker_sql_text can happen when the blocker is sleeping or no longer has an active request. If blocker_open_transactions is greater than zero, check transaction age next. Map host_name, program_name, and login_name before taking operational action.

What waits, locks, and open transactions tell you

`wait_type`, `wait_time`, and `wait_resource` tell you what the blocked request is waiting on now. Lock data tells you what kind of resource is involved and whether the lock request is granted, waiting, or converting.

Open transactions explain why a lock can remain after the statement that took it is no longer the visible current request. That distinction matters during blocking investigations because the statement you can see now may not be the statement that originally took the lock.

How to inspect locks and waiting tasks

Combines sys.dm_tran_locks with sys.dm_os_waiting_tasks to show lock mode, status, wait duration, and blocked resource details.

SELECT
    tl.request_session_id,
    tl.resource_type,
    tl.resource_database_id,
    DB_NAME(tl.resource_database_id) AS database_name,
    tl.resource_associated_entity_id,
    tl.request_mode,
    tl.request_status,
    tl.request_owner_type,
    tl.request_owner_id,
    wt.wait_duration_ms,
    wt.wait_type,
    wt.blocking_session_id,
    wt.resource_description
FROM sys.dm_tran_locks AS tl
LEFT JOIN sys.dm_os_waiting_tasks AS wt
    ON wt.resource_address = tl.lock_owner_address
WHERE tl.request_session_id <> @@SPID
ORDER BY
    tl.request_session_id,
    tl.resource_type,
    tl.request_status;

request_status = WAIT means the lock request is waiting; GRANT means the session currently holds that lock. KEY, PAGE, OBJECT, and RID resource types point to different levels of locking detail. resource_description and wait_resource help connect a wait to the object or key range involved. This query can return many rows on busy systems, so filter by session_id once you know the chain.

How to check open transactions

Shows active transaction age and uses DBCC OPENTRAN to identify the oldest transaction in the current database.

SELECT
    st.session_id,
    at.transaction_id,
    at.name AS transaction_name,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds,
    at.transaction_type,
    at.transaction_state,
    s.host_name,
    s.program_name,
    s.login_name
FROM sys.dm_tran_active_transactions AS at
JOIN sys.dm_tran_session_transactions AS st
    ON st.transaction_id = at.transaction_id
LEFT JOIN sys.dm_exec_sessions AS s
    ON s.session_id = st.session_id
WHERE st.session_id <> @@SPID
ORDER BY transaction_age_seconds DESC;

DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS;

Long transaction_age_seconds on a blocker is a strong clue. DBCC OPENTRAN is database scoped unless you pass a database name or ID. A session can hold locks because of an earlier statement inside the same transaction. Check the application owner before ending a session with an active business transaction.

SQL Server wait types, lock modes, and transaction states

SQL Server has many wait types, and new ones appear between versions. For blocking work, start with lock waits, the lock resource and mode, the request status, and the transaction state. Those columns usually explain whether the session is waiting, holding, converting, or rolling back.

Read these values together. A lock wait without the transaction age is incomplete, and a granted lock without the waiting task may only show the lock a session already owns.

Blocking wait type	What it usually means
LCK_M_S	Waiting for a shared lock, often a reader blocked by an incompatible writer.
LCK_M_U	Waiting for an update lock, often before a row is changed.
LCK_M_X	Waiting for an exclusive lock needed to change data.
LCK_M_IS, LCK_M_IU, LCK_M_IX	Waiting for intent locks used before lower-level row, page, key, or object locks.
LCK_M_SCH_S	Waiting for schema stability, often during compilation, metadata access, or a concurrent schema change.
LCK_M_SCH_M	Waiting for schema modification, often DDL, index work, partition switches, or other metadata-changing work.
LCK_M_BU	Waiting for a bulk update lock.
LCK_M_R*	Waiting on a key-range lock, usually tied to serializable behavior or range protection.
LCK_M_*_LOW_PRIORITY	Waiting under a low-priority lock wait option, commonly seen around online index operations.
LCK_M_*_ABORT_BLOCKERS	Waiting under an operation that may abort blockers after the configured low-priority wait behavior.

Lock mode	How to read it
NULL	No access is granted; it is a placeholder mode.
Sch-S	Schema stability. It protects metadata from being changed while it is being referenced.
Sch-M	Schema modification. It is incompatible with many normal operations and can block broadly.
S	Shared lock. Usually a read lock.
U	Update lock. Used when SQL Server may later convert to an exclusive lock.
X	Exclusive lock. Used for data changes and incompatible with normal reads and writes.
IS, IU, IX	Intent locks. They show SQL Server plans lower-level shared, update, or exclusive locking.
SIU, SIX, UIX	Combined shared/update/exclusive intent modes. These show mixed lock intent on the same resource.
BU	Bulk update lock.
RangeS_S, RangeS_U	Serializable range locks for shared or update range scans.
RangeI_N, RangeI_S, RangeI_U, RangeI_X	Insert key-range locks and conversion forms used while checking or inserting into ranges.
RangeX_S, RangeX_U, RangeX_X	Exclusive key-range conversion locks, usually from serializable range update behavior.

Lock resource or status	What it tells you
DATABASE, FILE, OBJECT	The lock is scoped to a broad database, file, or object resource.
PAGE, KEY, RID	The lock is at page, index key, or row identifier level.
EXTENT, HOBT, ALLOCATION_UNIT	The lock is tied to allocation or heap/B-tree structures.
APPLICATION, METADATA, XACT	The lock is application-defined, metadata-related, or transaction-related.
OIB, ROW_GROUP	The lock is tied to online index build or columnstore row-group work.
GRANTED	The session currently holds the lock.
WAIT	The session is waiting to acquire the lock.
CONVERT	The session holds one mode and is waiting to convert it to another mode.
LOW_PRIORITY_WAIT, LOW_PRIORITY_CONVERT, ABORT_BLOCKERS	The request is using low-priority lock wait behavior, usually from online maintenance or DDL options.

Transaction value	How to read it
transaction_type 1	Read/write transaction.
transaction_type 2	Read-only transaction.
transaction_type 3	System transaction.
transaction_type 4	Distributed transaction.
transaction_state 0	Not fully initialized yet.
transaction_state 1	Initialized but not started.
transaction_state 2	Active. This is the common state to check during blocking.
transaction_state 3	Ended. Used for read-only transactions.
transaction_state 4	Distributed transaction commit has started; still active but no more work can happen.
transaction_state 5	Prepared and waiting for resolution.
transaction_state 6	Committed.
transaction_state 7	Rolling back. Expect locks to remain until rollback finishes.
transaction_state 8	Rolled back.

How to capture query text and execution plans

The lock tells you where the pain is. The query text and plan help explain how the session reached that lock. This is where blocking work becomes useful instead of generic.

Look for broad scans on hot tables, key lookups repeated under load, stale or misleading estimates, missing supporting indexes, and statements doing more work inside the transaction than the business action needs.

How to capture query text

Uses sys.dm_exec_sql_text to show the active statement and surrounding batch for blocked and blocking requests.

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    DB_NAME(r.database_id) AS database_name,
    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 running_statement,
    sql_text.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS sql_text
WHERE r.session_id <> @@SPID
  AND (r.blocking_session_id <> 0 OR r.session_id IN (
      SELECT blocking_session_id
      FROM sys.dm_exec_requests
      WHERE blocking_session_id <> 0
  ))
ORDER BY r.wait_time DESC;

running_statement is the active statement slice when offsets are available. batch_text gives surrounding context, which matters for stored procedures and multi-statement batches. If the blocker is sleeping, the active request may be gone and the batch text may not be available. Capture this output during the incident if possible.

How to capture the current execution plan

Uses sys.dm_exec_text_query_plan with the plan handle and statement offsets from sys.dm_exec_requests.

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    qp.query_plan
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_text_query_plan(
    r.plan_handle,
    r.statement_start_offset,
    r.statement_end_offset
) AS qp
WHERE r.session_id <> @@SPID
  AND (r.blocking_session_id <> 0 OR r.wait_type IS NOT NULL)
ORDER BY r.wait_time DESC;

The plan can be null if the plan was evicted, the request ended, or the statement is not available in cache. Look for scans, poor estimates, expensive lookups, and operators touching more rows than expected. Do not add an index from this output alone; compare it with workload frequency and write cost. For recurring issues, Query Store or captured plans are usually better than one live snapshot.

Why transaction scope matters

Many blocking problems are transaction-shape problems. The code may do valid work, but it opens the transaction too early, holds it across unrelated steps, waits on the application while locks are held, or batches too much work into one unit.

If transaction scope is the cause, hardware and broad tuning may only hide the issue for a while. The better fix is usually a smaller transaction, a cleaner access path, a changed batch size, or moving non-critical work outside the lock-holding section.

Transactions opened before the real critical section begins.

Application calls, user prompts, or remote work inside a transaction.

Large updates or deletes without batching where batching is safe.

One transaction covering several business actions that could be separated.

How indexing and access paths affect SQL Server blocking

Weak indexing does not cause every blocking issue, but it can make locks last longer. A query that scans a hot table, touches far more rows than needed, or uses a poor plan can hold locks long enough to become the visible blocker.

The practical question is not "is there an index missing?" It is whether the blocked or blocking statement is touching too much data for the concurrency level the application needs.

Pattern	Why it matters
Wide scan on a hot table	The statement may hold locks across too many rows for too long.
Plan regression	A formerly safe statement can become the blocker under the wrong plan.
Missing or weak supporting index	The query takes a slower path and extends the lock window.
Heavy maintenance overlap	Index rebuilds, statistics work, or cleanup can collide with user traffic.

How isolation level and versioning change blocking

Read/write blocking depends heavily on isolation choices. `READ COMMITTED SNAPSHOT` can reduce reader-writer blocking, but it moves work into row versioning and tempdb. That may be the right tradeoff, but it is not a switch to flip casually during an incident.

`NOLOCK` is not a serious fix for production correctness. It can read uncommitted data and produce results the business should not trust. If the correct fix is versioning, design and test that deliberately.

Capture whether the pattern is read/write or write/write contention.

Check whether shorter transactions or better access paths would solve it first.

Confirm the application can tolerate row-versioning semantics.

Check tempdb capacity and monitoring before enabling version-heavy behavior.

What not to change during a blocking incident

Blocking incidents create pressure to do something visible. That pressure is dangerous when the chain has not been captured. The wrong fix can hide the problem, create data-quality issues, or make the next incident harder to diagnose.

Do not kill a session before identifying the business process, transaction state, and rollback impact.

Do not change isolation level as a blanket fix during the incident.

Do not add indexes from one blocked snapshot without checking workload frequency, write cost, and plan stability.

Do not treat NOLOCK as a reliable answer to blocking.

Do not rebuild indexes during active blocking unless that maintenance task is clearly the blocker and stopping or rescheduling it is the safest move.

How to check SQL Agent timing

Use this only when blocking seems to line up with scheduled jobs, maintenance, ETL, reporting, or vendor batches.

SELECT TOP (50)
    j.name AS job_name,
    h.step_id,
    h.step_name,
    h.run_date,
    h.run_time,
    h.run_duration,
    h.run_status,
    h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j
    ON j.job_id = h.job_id
WHERE h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -7, GETDATE()), 112))
ORDER BY h.instance_id DESC;

run_status helps separate successful jobs from failed or retried steps. run_duration is stored as HHMMSS, so read it carefully before comparing durations. Match job timing against blocking captures before blaming a job. A job can be the visible overlap without being the real root cause.

When to request a SQL Server performance review

A performance review makes sense when blocking is recurring, business-visible, or hard to explain from one live snapshot. The useful output is a clear chain: what blocked, what held the lock, why it lasted too long, and which fix should happen first.

Send the timing, blocking screenshots or DMV output, query text, execution plans, job windows, and what changed recently. That is enough to start separating transaction scope, access paths, workload timing, and operational response.

Request performance review Read the deadlocks guide

Next step

If blocking is already costing production time, use the SQL Server performance review page or request the review above.

Next useful reads: the SQL Server deadlocks guide for cycle diagnosis, the SQL Server indexing guide for access-path fixes, and the SQL Server monitoring guide for earlier capture of contention.