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.
On this page
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.
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;Direct blocker, blocking chain and head blocker
- BlockedSession 92LCK_M_S, blocking_session_id = 81
- waits onBlocked and blockingSession 81LCK_M_X, blocking_session_id = 65
- waits onHead blockerSession 65Sleeping, open transaction, no active request
- 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 insys.dm_exec_requestsandopen_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 fromsys.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.
;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);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.
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;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_handleas definitive proof of the statement holding every lock. - Using
NOLOCKas 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
- Microsoft Learn: Understand and resolve SQL Server blocking problems
- Microsoft Learn: sys.dm_exec_requests, sys.dm_tran_active_transactions, sys.dm_tran_database_transactions
- Microsoft Learn: blocked process threshold (off by default), the system_health session (records lock waits longer than 30 seconds and deadlocks), KILL
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.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetFind Blocking in SQL ServerIdentify blocked sessions, blockers, lock waits, and the head blocker so you can troubleshoot SQL Server blocking without killing the wrong session.Open cheat sheet
- RoadmapSQL Server Troubleshooting & DiagnosticsFind blocked requests, trace blocking chains to the head blocker, and inspect the responsible session and transaction safely.View roadmap
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.