--- name: sqlblocking-review description: Analyze SQL Server lock blocking from sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_os_waiting_tasks, sys.dm_tran_locks, open-transaction DMVs, blocked process reports, index operational stats, Query Store lock waits, and community tool output such as sp_WhoIsActive, sp_BlitzWho and sp_HumanEvents. Applies 54 checks (BL1–BL54) covering blocking chain topology and head-blocker identification, head-blocker state classification against the six documented blocking scenarios, lock-level evidence such as escalation and Sch-M and key-range locks, transaction and isolation-level design faults, historical and aggregate blocking evidence when nobody was watching, structural engine-level causes such as statistics updates and lock partitioning, and client, tooling and platform patterns. Use this skill whenever sessions are blocked, applications report lock timeouts, LCK_M waits dominate, or a DBA pastes blocking chain output and asks who is blocking whom. Trigger when blocked_session_id, blocking_session_id, blocked process report XML, sp_WhoIsActive or sp_who2 BlkBy output is present. triggers: - /sqlblocking-review - /blocking-review - /head-blocker - /sqlblocking --- # SQL Server Blocking Review Skill ## Purpose Identify the head of a blocking chain, explain why it holds its locks, and give a ranked remediation path. Applies 54 checks (BL1–BL54) across eight categories: - **BL1–BL7** — Blocking chain topology: head blocker identification, block duration, chain depth, fan-out, cross-database chains, concurrency exhaustion, and chronic recurrence across captures - **BL8–BL15** — Head-blocker state classification: maps the head blocker to the documented blocking scenarios (long-running query, sleeping session with an open transaction, orphaned transaction, rollback, client not fetching results, client/server distributed deadlock), plus non-lock waits and maintenance work at the head - **BL16–BL23** — Lock-level evidence: lock escalation to table locks, schema modification locks, hot resource contention, key-range locks, lock conversion waits, application locks, and lock footprint size - **BL24–BL30** — Transaction and isolation design: long-running open transactions, elevated isolation levels, implicit transactions, transactions held across client round-trips, blocking lock hints, reader-writer blocking curable by row versioning, and row-versioning side effects - **BL31–BL36** — Observability and platform configuration: blocked process threshold, blocked process report capture, lock escalation overrides, scan-driven lock footprints, accelerated database recovery, and optimized locking This skill analyzes captured artifacts only — it never opens a connection to SQL Server. The user runs the capture queries below and pastes the output. Blocking is normal and self-clearing in a lock-based engine; the question this skill answers is which blocking is *persistent*, *what holds the locks*, and *whether it will resolve on its own*. ## Input Accept any of: - **DMV output** from the blocking-chain capture queries below — `sys.dm_exec_requests`, `sys.dm_exec_sessions`, `sys.dm_os_waiting_tasks`, `sys.dm_tran_locks`, `sys.dm_tran_active_transactions`, `sys.dm_exec_input_buffer`, `sys.dm_exec_sql_text` (preferred; two or more captures minutes apart give the strongest evidence) - **A blocked process report** — the XML payload of the `blocked_process_report` Extended Event, or the equivalent Profiler event, containing `` and `` elements - **`sp_who2` / Activity Monitor output** — the `BlkBy` column, SSMS "Activity - All Blocking Transactions" report text, or a screenshot transcription - **Community tool output** — `sp_WhoIsActive` (ideally run with `@find_block_leaders = 1, @sort_order = '[blocked_session_count] DESC'`), `sp_BlitzWho`, `sp_BlitzFirst @SinceStartup = 1` wait totals, `sp_HumanEvents @event_type = 'blocking'` or `sp_HumanEventsBlockViewer` output, or a blocking-tree script's indented chain. Read `blocked_session_count`, `blocking_session_id`, `sql_text`, `status`, `open_tran_count`, `wait_info` and map them onto the same checks — the columns differ in name, not in meaning - **Historical / aggregate artifacts**, when the incident is over: `sys.dm_db_index_operational_stats` lock wait columns, `sys.query_store_wait_stats` rows with `wait_category_desc = 'Lock'`, `sys.dm_os_performance_counters` rows for *Processes blocked* and the *Locks* object, or a table of logged `sp_WhoIsActive` samples - **Wait statistics** showing `LCK_M_*` waits when the user asks "what is blocking?" — analyze what is available and name the extra capture needed - **A natural language description** of symptoms ("every morning at 09:05 all order inserts stall for two minutes, then clear on their own") Partial input is workable: state which checks could not be evaluated and which capture closes the gap, rather than guessing. ### Recommended capture queries ```sql -- 1. Blocking chain with head blocker, statement text, and session state -- Run this first. Level 0 rows are head blockers. WITH cteHead AS ( SELECT sess.session_id, req.request_id, req.blocking_session_id, wait_type = LEFT(ISNULL(req.wait_type, ''), 50), wait_resource = LEFT(ISNULL(req.wait_resource, ''), 60), last_wait_type = LEFT(ISNULL(req.last_wait_type, ''), 50), req.wait_time, request_status = LEFT(ISNULL(req.status, ''), 15), session_status = LEFT(sess.status, 15), req.command, req.open_transaction_count, session_open_tran = sess.open_transaction_count, sess.transaction_isolation_level, sess.is_user_process, sess.host_name, sess.program_name, sess.login_name, sess.last_request_start_time, sess.last_request_end_time, req.cpu_time, req.logical_reads, req.percent_complete, req.[sql_handle], conn.most_recent_sql_handle FROM sys.dm_exec_sessions AS sess LEFT JOIN sys.dm_exec_requests AS req ON req.session_id = sess.session_id LEFT JOIN sys.dm_exec_connections AS conn ON conn.session_id = sess.session_id ), cteChain AS ( SELECT head_blocker_session_id = h.session_id, h.session_id, h.blocking_session_id, h.wait_type, h.wait_time, h.wait_resource, h.request_status, h.session_status, h.command, h.open_transaction_count, h.session_open_tran, h.transaction_isolation_level, h.host_name, h.program_name, h.login_name, h.last_request_start_time, h.last_request_end_time, h.percent_complete, h.[sql_handle], h.most_recent_sql_handle, [level] = 0 FROM cteHead AS h WHERE (h.blocking_session_id IS NULL OR h.blocking_session_id = 0) AND h.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id <> 0) UNION ALL SELECT c.head_blocker_session_id, b.session_id, b.blocking_session_id, b.wait_type, b.wait_time, b.wait_resource, b.request_status, b.session_status, b.command, b.open_transaction_count, b.session_open_tran, b.transaction_isolation_level, b.host_name, b.program_name, b.login_name, b.last_request_start_time, b.last_request_end_time, b.percent_complete, b.[sql_handle], b.most_recent_sql_handle, c.[level] + 1 FROM cteHead AS b INNER JOIN cteChain AS c ON c.session_id = b.blocking_session_id AND c.session_id <> b.session_id -- avoid infinite recursion on latch-type blocking WHERE c.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') OR c.wait_type IS NULL ) SELECT c.*, blocker_or_last_query = txt.text, input_buffer = ib.event_info FROM cteChain AS c OUTER APPLY sys.dm_exec_sql_text (ISNULL(c.[sql_handle], c.most_recent_sql_handle)) AS txt OUTER APPLY sys.dm_exec_input_buffer (c.session_id, NULL) AS ib ORDER BY c.head_blocker_session_id, c.[level], c.session_id; -- 2. Open transactions with age — finds sleeping sessions holding locks SELECT tst.session_id, database_name = DB_NAME(s.database_id), tat.transaction_begin_time, transaction_duration_s = DATEDIFF(SECOND, tat.transaction_begin_time, SYSDATETIME()), transaction_type = CASE tat.transaction_type WHEN 1 THEN 'Read/write' WHEN 2 THEN 'Read-only' WHEN 3 THEN 'System' WHEN 4 THEN 'Distributed' END, transaction_state = tat.transaction_state, session_open_tran = tst.open_transaction_count, request_status = r.status, s.status AS session_status, s.host_name, s.program_name, s.login_name, s.is_user_process, s.last_request_start_time, s.last_request_end_time, s.transaction_isolation_level, input_buffer = ib.event_info FROM sys.dm_tran_active_transactions AS tat JOIN sys.dm_tran_session_transactions AS tst ON tst.transaction_id = tat.transaction_id JOIN sys.dm_exec_sessions AS s ON s.session_id = tst.session_id LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_input_buffer (s.session_id, NULL) AS ib ORDER BY tat.transaction_begin_time; -- 3. Waiting tasks joined to the locks they are waiting for SELECT wt.session_id, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description, tm.resource_type, tm.resource_subtype, tm.request_mode, tm.request_status, tm.resource_associated_entity_id, database_name = DB_NAME(tm.resource_database_id) FROM sys.dm_tran_locks AS tm JOIN sys.dm_os_waiting_tasks AS wt ON tm.lock_owner_address = wt.resource_address ORDER BY wt.wait_duration_ms DESC; -- 4. Lock footprint per session — escalation risk and what the head blocker holds SELECT request_session_id, database_name = DB_NAME(resource_database_id), resource_type, resource_subtype, request_mode, request_status, lock_count = COUNT(*), sample_resource = MIN(resource_description), sample_entity = MIN(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id <> @@SPID -- exclude this capture session GROUP BY request_session_id, resource_database_id, resource_type, resource_subtype, request_mode, request_status ORDER BY lock_count DESC; -- 5. Blocking observability and database-level concurrency settings SELECT name, value_in_use FROM sys.configurations WHERE name IN ('blocked process threshold (s)'); SELECT name, snapshot_isolation_state_desc, is_read_committed_snapshot_on, is_accelerated_database_recovery_on, -- NULL where optimized locking is not available (before SQL Server 2025) is_optimized_locking_on = DATABASEPROPERTYEX(name, 'IsOptimizedLockingOn'), recovery_model_desc FROM sys.databases WHERE database_id > 4; SELECT session_name = s.name, s.startup_state, event_name = e.name FROM sys.server_event_sessions AS s JOIN sys.server_event_session_events AS e ON e.event_session_id = s.event_session_id WHERE e.name IN ('blocked_process_report', 'lock_escalation', 'xml_deadlock_report', 'locking_stats'); ``` ```sql -- 6. Historical evidence, for blocking that has already ended (BL37-BL42) -- 6a. Lock wait hot spots per index (run in the affected database). -- Counters reset when the index's metadata cache object is evicted, -- so treat them as "since roughly the last restart", not as exact history. -- A wait is recorded when it ends, so run this after the chain clears. -- Escalated tables block at OBJECT level, which these wait columns do not -- count, so rows with escalation attempts are kept even at zero wait (BL39). SELECT table_name = OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id), index_name = ISNULL(i.name, '(heap)'), i.index_id, os.row_lock_wait_count, os.row_lock_wait_in_ms, os.page_lock_wait_count, os.page_lock_wait_in_ms, total_lock_wait_ms = os.row_lock_wait_in_ms + os.page_lock_wait_in_ms, avg_row_lock_wait_ms = os.row_lock_wait_in_ms / NULLIF(os.row_lock_wait_count, 0), avg_page_lock_wait_ms = os.page_lock_wait_in_ms / NULLIF(os.page_lock_wait_count, 0), os.index_lock_promotion_attempt_count, os.index_lock_promotion_count FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS os JOIN sys.indexes AS i ON i.object_id = os.object_id AND i.index_id = os.index_id WHERE os.row_lock_wait_in_ms + os.page_lock_wait_in_ms > 0 OR os.index_lock_promotion_attempt_count > 0 ORDER BY total_lock_wait_ms DESC, os.index_lock_promotion_attempt_count DESC; -- 6b. Query Store lock wait history (SQL Server 2017 and later, Azure SQL). -- Check the capture mode first: under AUTO a blocked query that uses -- little CPU may never be stored (BL40), so an empty result proves nothing. SELECT query_capture_mode_desc, wait_stats_capture_mode_desc, actual_state_desc FROM sys.database_query_store_options; -- Both views hold several rows per plan, interval, and execution type; -- aggregate each on that key before joining. WITH lock_waits AS ( SELECT plan_id, runtime_stats_interval_id, execution_type, lock_wait_ms = SUM(total_query_wait_time_ms) FROM sys.query_store_wait_stats WHERE wait_category_desc = 'Lock' GROUP BY plan_id, runtime_stats_interval_id, execution_type ), runs AS ( SELECT plan_id, runtime_stats_interval_id, execution_type, executions = SUM(count_executions) FROM sys.query_store_runtime_stats GROUP BY plan_id, runtime_stats_interval_id, execution_type ) SELECT TOP (25) qsq.query_id, qsp.plan_id, total_lock_wait_ms = SUM(lw.lock_wait_ms), executions = SUM(r.executions), avg_lock_wait_ms = SUM(lw.lock_wait_ms) * 1.0 / NULLIF(SUM(r.executions), 0), query_sql_text = MIN(qst.query_sql_text) FROM lock_waits AS lw LEFT JOIN runs AS r ON r.plan_id = lw.plan_id AND r.runtime_stats_interval_id = lw.runtime_stats_interval_id AND r.execution_type = lw.execution_type JOIN sys.query_store_plan AS qsp ON qsp.plan_id = lw.plan_id JOIN sys.query_store_query AS qsq ON qsq.query_id = qsp.query_id JOIN sys.query_store_query_text AS qst ON qst.query_text_id = qsq.query_text_id GROUP BY qsq.query_id, qsp.plan_id ORDER BY total_lock_wait_ms DESC; -- 6c. Blocking performance counters (sample twice to get a rate) SELECT object_name = RTRIM(object_name), counter_name = RTRIM(counter_name), instance_name = RTRIM(instance_name), cntr_value, cntr_type FROM sys.dm_os_performance_counters WHERE (object_name LIKE '%General Statistics%' AND counter_name = 'Processes blocked') OR (object_name LIKE '%Locks%' AND counter_name IN ('Lock Waits/sec', 'Lock Wait Time (ms)', 'Lock Timeouts/sec', 'Number of Deadlocks/sec', 'Average Wait Time (ms)')); -- 6d. Instance-wide LCK_M_* share for BL37: use section 6d of -- scripts/capture-blocking.sql, which removes the full list of idle and -- background waits. Without that list the denominator is dominated by -- waits such as SOS_WORK_DISPATCHER and the share is badly understated. ``` > Optimized-locking instances also show `XACT` lock resources in `sys.dm_tran_locks`; section 5 reports `is_optimized_locking_on` (NULL before SQL Server 2025). See BL36. > > Live capture beats every historical source: sections 1–4 name the blocker, sections 6a–6c only narrow down where and when. Lock waits are recorded by the *blocked* session, never by the blocker, so no wait-based artifact can name the head blocker on its own — see BL37. --- ## Analysis Order 1. **Find the head blocker** — the session whose `blocking_session_id` is 0 or NULL and that appears as another session's `blocking_session_id`. Everything else is a consequence of it (BL1). 2. **Classify the head blocker's state** — `status`, `wait_type`, and `open_transaction_count` decide whether the block resolves on its own or needs intervention (BL8–BL15). 3. **Read the lock evidence** — what resource, what mode, whose locks, how many (BL16–BL23). 4. **Explain the transaction design** — how long the transaction has been open, at what isolation level, with what hints (BL24–BL30). 5. **Check observability** — whether this will be captured automatically next time (BL31–BL36). Report the head blocker's *identity, statement, and state* before any recommendation; a fix aimed at a victim session treats the symptom. --- ## Thresholds Reference | Metric | Info | Warning | Critical | |--------|------|---------|----------| | Lock wait duration of any blocked session (`wait_time` / `wait_duration_ms`) | < 5 s | 5–29 s | ≥ 30 s | | Blocking chain depth (levels below the head blocker) | 1 | 2 | ≥ 3 | | Blocking fan-out (sessions blocked by one head blocker) | 1–2 | 3–9 | ≥ 10 | | Blocked sessions as a share of active user requests | < 10% | 10–24% | ≥ 25% | | Open transaction age on a session holding locks | < 60 s | 60–299 s | ≥ 300 s | | Idle time of a sleeping session with an open transaction (`last_request_end_time` to capture time) | < 30 s | 30–299 s | ≥ 300 s | | Locks held by a single session on one table or index | < 2,500 | 2,500–4,999 | ≥ 5,000 (escalation threshold) | | `blocked process threshold (s)` setting | 5–30 | 31–86,400 | 0 (off) or 1–4 (ineffective) | | Repeat appearances of the same head-blocker statement across captures | 1 | 2 | ≥ 3 | | `LCK_M_*` share of instance-wide wait time | < 5% | 5–19% | ≥ 20% | | Average lock wait per index (`row_lock_wait_in_ms` / `row_lock_wait_count`) | < 200 ms | 200–999 ms | ≥ 1,000 ms | | Total lock wait per index (`row_lock_wait_in_ms` + `page_lock_wait_in_ms`) | < 1 min | 1–5 min | > 5 min | | `index_lock_promotion_attempt_count` per index | 0 | 1–10 | > 10 | | *Processes blocked* performance counter, sustained across samples | 0 | 1–4 | ≥ 5 | | Logical CPUs at which lock partitioning changes table-lock behaviour | < 16 | — | ≥ 16 | > **Threshold provenance:** The 5,000-lock escalation threshold (per single reference to a table, re-checked every 1,250 new locks) and the `blocked process threshold (s)` range (5 to 86,400, with a 5-second lock-monitor wake interval) are Microsoft-documented values, as is lock partitioning being enabled automatically on instances with a larger number of logical CPUs. The per-index cutoffs (1 s average lock wait, 5 minutes total, 10 escalation attempts) follow the First Responder Kit's `sp_BlitzIndex` "aggressive indexes" rule. The wait-duration, chain-depth, fan-out, transaction-age, recurrence, and counter cutoffs are operational heuristics for prioritisation — compare them against the workload's own baseline before calling a number a problem. --- ## Blocking Chain Topology Checks (BL1–BL7) ### BL1 — Head Blocker Identified - **Trigger:** A session has `blocking_session_id` of 0 or NULL and appears as the `blocking_session_id` of at least one other session - **Severity:** Info when the chain clears within the Info duration band; escalates with BL2–BL4 - **Fix:** Report the head blocker's `session_id`, `login_name`, `host_name`, `program_name`, current statement (`sys.dm_exec_sql_text`) or last statement (`sys.dm_exec_input_buffer`), `status`, `wait_type`, and `open_transaction_count`. Every subsequent finding attaches to this session. When several independent chains exist, report one head blocker per chain and rank the chains by total blocked session count and longest wait. ### BL2 — Long Lock Wait - **Trigger:** Any blocked session shows a lock wait (`wait_type` starting `LCK_M_`) with `wait_time` or `wait_duration_ms` at or above the Warning band in the Thresholds Reference - **Severity:** Warning in the 5–29 s band; Critical at 30 s or more - **Fix:** Waits past the client's command timeout become application errors, so treat this as the user-visible severity of the incident. Record the longest wait, the resource it waits on, and the head blocker's state from BL8–BL15, which determines whether waiting is enough or the head blocker needs a `KILL`. Compare `wait_time` across two captures: a falling value means locks are being acquired and released (progress), a rising value on the same `wait_resource` means a stalled head blocker. ### BL3 — Deep Blocking Chain - **Trigger:** The chain from the head blocker reaches the Warning depth or more, measured as levels of `blocking_session_id` indirection - **Severity:** Warning at 2 levels; Critical at 3 or more - **Fix:** Depth means blocked sessions are themselves holding locks other sessions want, so the queue drains serially after the head blocker releases. Resolve the head blocker; do not kill intermediate sessions, whose rollback adds work and can lengthen the outage. Deep chains on a single hot table usually point at BL19 (hot resource) or BL16 (escalation). ### BL4 — Wide Blocking Fan-Out - **Trigger:** One head blocker directly blocks sessions at or above the Warning fan-out count - **Severity:** Warning at 3–9 sessions; Critical at 10 or more - **Fix:** Wide fan-out with a shallow chain is the signature of one coarse lock (table or page) against many short readers/writers — check BL16 (escalation), BL17 (object-level X), and BL18 (Sch-M). Quantify the impact as blocked sessions multiplied by the longest wait so the business cost is explicit in the report. Count fan-out by resource, not only by `blocking_session_id`: lock requests queue in arrival order, so a session waiting on a row the head blocker holds is reported as blocked by the *earlier waiter* queued ahead of it on that row. Sessions whose `wait_resource` matches a lock the head blocker holds belong to its fan-out even when they appear one level down. ### BL5 — Blocking Chain Spans Multiple Databases - **Trigger:** Sessions in one chain hold or wait for locks whose `resource_database_id` resolves to more than one database, or the head blocker's `database_name` differs from a victim's - **Severity:** Info; Warning when a distributed transaction (`transaction_type` = Distributed) or a linked-server call is involved - **Fix:** Cross-database chains usually come from a transaction that spans databases (including one enlisted through MS DTC or a linked server), which holds locks in the first database for the duration of remote work. Shorten the transaction so remote calls happen outside it, or stage the remote data first and modify locally. A `request_session_id` of `-2` in `sys.dm_tran_locks` marks an orphaned distributed transaction that has to be resolved through `KILL` with the transaction UOW value. ### BL6 — Concurrency Exhaustion Risk - **Trigger:** Blocked sessions reach the Warning share of active user requests, or `THREADPOOL` waits appear alongside the lock waits - **Severity:** Warning at 10–24% blocked; Critical at 25% or more, or on any `THREADPOOL` wait - **Fix:** Each blocked request pins a worker thread. Once blocked requests approach the worker thread limit, new connections — including the DBA's — cannot be scheduled and the instance looks down. Connect through the dedicated administrator connection, kill the head blocker, and afterwards address the root cause. Raising `max worker threads` treats the symptom and delays recovery. ### BL7 — Chronic Head Blocker Across Captures - **Trigger:** Two or more captures (or repeated blocked process reports) show the same statement, `query_hash`, procedure, or client program at the head of a chain - **Severity:** Warning at 2 appearances; Critical at 3 or more - **Fix:** Recurrence moves the problem from incident to design defect. Pin the recurring statement, then decide which structural fix applies: query or index tuning (BL35), transaction shortening (BL24), isolation change (BL26/BL29), or scheduling (BL15). Set up the blocked process report (BL31–BL33) so subsequent occurrences are captured without a DBA at the keyboard. --- ## Head-Blocker State Classification (BL8–BL15) These map the head blocker to the documented blocking scenarios. `status`, `wait_type`, and `open_transaction_count` are the three columns that separate them. ### BL8 — Head Blocker Is a Long-Running Query - **Trigger:** Head blocker has `status` of `running`, `runnable`, or `suspended`, a non-NULL `wait_type` that is not a lock wait, and growing `cpu_time`, `reads`, or `logical_reads` across captures - **Severity:** Warning; Critical when its runtime exceeds the blocked sessions' command timeout - **Fix:** This blocking resolves itself when the query finishes, so treat it as a query performance problem rather than a locking problem. Capture the statement and plan and route to `/sqlplan-review` and `/sqlindex-advisor`; a scan that touches far more rows than it returns both lengthens the transaction and widens the lock footprint (BL35). If the query cannot be tuned in place, move the workload to a read-only replica or a reporting copy rather than killing it repeatedly. ### BL9 — Sleeping Head Blocker with an Open Transaction - **Trigger:** Head blocker has `status` of `sleeping`, `wait_type` NULL, and `open_transaction_count` greater than 0 in `sys.dm_exec_sessions` - **Severity:** Critical - **Fix:** The session is idle inside an open transaction and still holds every lock the transaction acquired; this does not resolve on its own. The usual cause is a client that hit a query timeout or issued a cancel without a matching `ROLLBACK` or `COMMIT` — SQL Server ends the batch but keeps the transaction. Short-term: `KILL `. Durable fix: roll back in the client's error handler (`IF @@TRANCOUNT > 0 ROLLBACK TRAN`), or use `SET XACT_ABORT ON` in the connection or in procedures that open transactions, remembering that statements after an aborting error will not run. Confirm the age of the transaction with BL24. ### BL10 — Orphaned Transaction - **Trigger:** Head blocker is sleeping with `open_transaction_count` greater than 0 **and** `last_request_end_time` is older than the Warning idle band, or the client host is known to have disconnected or restarted - **Severity:** Critical - **Fix:** A batch-aborting error, a client crash, or a workstation restart left the transaction open while the connection still appears alive to SQL Server, so the locks are held until the session is killed or the instance restarts. `KILL ` releases them, and the kill can take up to 30 seconds because of the interval between kill checks. Prevent recurrence with `Try-Catch-Finally` cleanup in application code and `SET XACT_ABORT ON`. If connection pooling is in play, note that the transaction survives until the pooled connection is reused or aged out. ### BL11 — Head Blocker Is Rolling Back - **Trigger:** Head blocker shows `command` of `KILLED/ROLLBACK`, a `status` of `rollback`, or a populated `percent_complete` on a rollback - **Severity:** Warning; Critical when `percent_complete` advances slowly or `estimated_completion_time` exceeds the outage tolerance - **Fix:** The rollback has to finish — it cannot be killed again or chosen as a deadlock victim, and restarting the instance makes it worse by moving the same work into startup recovery with the database inaccessible. Report `percent_complete` and `estimated_completion_time` and wait. To avoid a repeat, break large write batches into smaller transactions and schedule them off-hours (BL15), and consider accelerated database recovery, which makes lengthy rollbacks rare (BL36). ### BL12 — Client Is Not Consuming Results - **Trigger:** Head blocker has `wait_type` of `ASYNC_NETWORK_IO` while holding locks, typically with `status` of `runnable` or `suspended` and a client that fetches row by row - **Severity:** Warning; Critical when the wait persists across captures with the same `wait_resource` on the victims - **Fix:** The server has produced rows the client has not fetched, so the statement — and its locks — stay open at the pace of the client. Rewrite the client to fetch the result set to completion promptly (server-side paging with `OFFSET`/`FETCH` is fine), avoid per-row processing inside the result loop, and keep result sets small. Poorly behaved reporting clients that cannot be changed belong on a reporting copy rather than the OLTP database. ### BL13 — Client/Server Distributed Deadlock - **Trigger:** The head blocker's `host_name` matches the `host_name` of a session it blocks, one side waits on `ASYNC_NETWORK_IO` and the other on a lock, and the situation does not clear - **Severity:** Critical - **Fix:** Only one side of this cycle is a SQL Server lock, so the lock monitor cannot detect or resolve it — the other side is the client's own thread or connection scheduling. A query timeout on the client breaks the cycle; set one. The durable fix is in the application: avoid holding one connection's result set open while a second connection on the same thread modifies the same table, and do not feed rows read on one connection into writes on another inside the same logical unit. ### BL14 — Head Blocker Waits on a Non-Lock Resource - **Trigger:** Head blocker's `wait_type` is an I/O, memory, log, or parallelism wait — `PAGEIOLATCH_*`, `WRITELOG`, `RESOURCE_SEMAPHORE`, `IO_COMPLETION`, `CXPACKET`/`CXCONSUMER`, `THREADPOOL`, `SOS_SCHEDULER_YIELD` - **Severity:** Warning - **Fix:** The blocking here is downstream of a different bottleneck: the head blocker holds locks because something else makes it slow. Fixing locking will not help until that resource is fixed. Route by wait: `PAGEIOLATCH_*`/`WRITELOG` to `/sqldiskio-review` and `/sqlwait-review`, `RESOURCE_SEMAPHORE` to `/sqlmemory-review`, `CXPACKET`/`CXCONSUMER` and `SOS_SCHEDULER_YIELD` to `/sqlwait-review` and `/sqlplan-review`. ### BL15 — Head Blocker Is Maintenance or System Work - **Trigger:** Head blocker's `command` is a maintenance operation (`BACKUP DATABASE`, `DBCC`, `ALTER INDEX`, `UPDATE STATISTICS`, bulk load) or `is_user_process` is 0 - **Severity:** Warning; Critical during business hours on an OLTP database - **Fix:** Maintenance that blocks production is a scheduling and options problem, not a query problem. Move the job to a low-activity window; use `ALTER INDEX ... REBUILD WITH (ONLINE = ON)` where the edition supports it, with `WAIT_AT_LOW_PRIORITY` so the operation yields instead of queueing behind and in front of user work; chunk large deletes and updates; and keep statistics maintenance off peak. Backups do not block DML but do conflict with other backups and with some file and bulk operations — check `resource_subtype` values such as `DATABASE.BULKOP_BACKUP_DB`. --- ## Lock-Level Evidence Checks (BL16–BL23) ### BL16 — Lock Escalation to a Table Lock - **Trigger:** `sys.dm_tran_locks` shows the head blocker holding an `OBJECT` lock with `request_mode` of `S` or `X` while its row or page locks have disappeared, or a `lock_escalation` Extended Event fired for the same object - **Severity:** Critical when the escalated table lock is what victims wait for; Warning when escalation happened but is not the blocking lock - **Fix:** Escalation converts many row or page locks into one table lock, which blocks every other user of the table. An `OBJECT` lock in an intent mode (`IS`, `IU`, `IX`) is not escalation and points elsewhere. Preferred fixes, in order: break the operation into smaller transactions (delete or update in batches of a few hundred to a few thousand rows); tune the statement and its indexes so fewer locks are taken (BL35); make predicates SARGable so a seek replaces a scan. Disabling escalation is a last resort — `ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE)` on the one table, and the instance-wide trace flags 1211 and 1224 only to mitigate severe blocking while a real fix is prepared, because they can push lock memory to the point of error 1204. ### BL17 — Exclusive Object-Level Lock Held - **Trigger:** Head blocker holds `resource_type` of `OBJECT` with `request_mode` of `X` and the escalation evidence in BL16 is absent - **Severity:** Critical - **Fix:** A whole-table exclusive lock usually comes from a `TABLOCKX` hint, a bulk load with `TABLOCK`, or DDL. Remove the hint unless the operation genuinely needs table-level exclusivity, and schedule bulk loads that require `TABLOCK` for a maintenance window. When the object is a staging table used by a nightly load, partition switching moves the data in with a brief metadata-only operation instead of a long table lock. ### BL18 — Schema Modification Lock Blocking - **Trigger:** Any session holds or waits for `request_mode` of `Sch-M`, or waits for `Sch-S` behind a held `Sch-M` - **Severity:** Critical - **Fix:** A schema modification lock conflicts with every other lock mode, including the schema stability lock that ordinary queries and even `READUNCOMMITTED` readers take, so one DDL statement stalls all access to the object. Typical sources are offline `ALTER INDEX ... REBUILD`, `ALTER TABLE`, truncate, partition switch, and statistics or index metadata operations (`OBJECT.INDEX_OPERATION`, `OBJECT.UPDSTATS` subtypes). Run DDL in a maintenance window, use online index operations where available, and wrap DDL with `SET LOCK_TIMEOUT` plus `WAIT_AT_LOW_PRIORITY` so a failed attempt backs off instead of queueing the whole workload behind itself. ### BL19 — Hot Resource Contention - **Trigger:** Multiple waiting sessions show the same `resource_description` or the same `resource_associated_entity_id` (a `KEY`, `PAGE`, or `RID` resource) in `sys.dm_os_waiting_tasks` - **Severity:** Warning; Critical when the queue on one resource reaches the Critical fan-out threshold - **Fix:** Contention is concentrated on one row, page, or key range rather than spread across the table. Identify the object with `sys.partitions` (join `hobt_id` to `resource_associated_entity_id`) and, for a `PAGE` resource, `sys.dm_db_page_info`. Common shapes: a counter or "next ID" row every transaction updates — replace with a sequence or identity; last-page insert contention on an ever-increasing clustered key — consider a different key, a hash-distributed key, or `OPTIMIZE_FOR_SEQUENTIAL_KEY` on supporting versions; a queue table polled by many workers — use `READPAST` with row locks. ### BL20 — Key-Range Locks Present - **Trigger:** `request_mode` values beginning `Range` (`RangeS_S`, `RangeS_U`, `RangeI_N`, `RangeX_X`) appear on the head blocker or the victims - **Severity:** Warning - **Fix:** Key-range locks come from `SERIALIZABLE` — set explicitly, inherited from a `HOLDLOCK`/`SERIALIZABLE` hint, or supplied by a client library or distributed transaction default. They lock gaps as well as rows, so they block inserts into a range that has no rows yet. Confirm the isolation level (BL26) and lower it to the weakest level the correctness requirement allows; most code that reaches for `SERIALIZABLE` needs only a targeted `UPDLOCK, HOLDLOCK` on a single key to serialize one race. ### BL21 — Lock Conversion Wait - **Trigger:** `sys.dm_tran_locks` shows `request_status` of `CONVERT` (or a low-priority variant) for a waiting request - **Severity:** Warning; Critical when the converting session is itself the head of a chain - **Fix:** The session already holds a lock on the resource and waits to upgrade it — typically shared to exclusive, in a read-then-write pattern inside one transaction. Two transactions doing this against the same row deadlock rather than block. Take the stronger lock up front with `UPDLOCK` at the read step, or restructure to a single statement (`UPDATE ... WHERE` with the predicate, or `MERGE` with `HOLDLOCK`) so no upgrade is needed. ### BL22 — Application Lock Contention - **Trigger:** `resource_type` of `APPLICATION` appears among held or waited-for locks - **Severity:** Warning - **Fix:** These are explicit `sp_getapplock` mutexes taken by application code, not engine locks on data, and the wait usually means a critical section is held longer than intended or released on a path the code misses. Verify the lock owner (`Transaction` versus `Session`), that every acquire has a matching `sp_releaseapplock` — or that the owner is `Transaction` so commit or rollback releases it — and that `@LockTimeout` is set so a stuck holder produces a handled error instead of an indefinite wait. The `resource_description` carries the principal ID and the first 32 characters of the resource name, which identifies the critical section. Note that a deadlock involving an application lock does not roll back the transaction that requested it — a return code of -3 has to be handled with an explicit `ROLLBACK` in the calling code. ### BL23 — Large Lock Footprint - **Trigger:** One session holds locks on a single table or index at or above the Warning count in the Thresholds Reference - **Severity:** Warning at 2,500–4,999 locks; Critical at 5,000 or more (escalation is triggered when the count on one table or index exceeds 5,000, after the memory threshold is checked) - **Fix:** A large footprint is the precursor to BL16 and, on its own, already blocks widely. Reduce rows touched per transaction by batching, and reduce locks per row by removing scans: a bookmark lookup with `PREFETCH` can raise part of a read-committed query to repeatable-read behaviour and take thousands of key locks, so a covering index removes both the lookup and the locks. Escalation also fires on memory pressure — lock memory is capped at 60 percent of the visible buffer pool and escalation is considered at 40 percent of that — so check `/sqlmemory-review` when footprints are large instance-wide. --- ## Transaction and Isolation Checks (BL24–BL30) ### BL24 — Long-Running Open Transaction - **Trigger:** `sys.dm_tran_active_transactions` shows a read/write transaction whose age reaches the Warning band while the session holds locks - **Severity:** Warning at 60–299 s; Critical at 300 s or more - **Fix:** Lock duration equals transaction duration for write locks, so transaction length is the single biggest lever on blocking. Move everything that does not need to be transactional out of the transaction: validation reads, remote or linked-server calls, file or queue work, and user or service round-trips. Commit per batch in loops rather than wrapping the loop. Where a long read must stay, row versioning (BL29) removes its blocking effect without shortening it. ### BL25 — Transaction Held Across Client Round-Trips - **Trigger:** A session alternates between `sleeping` with `open_transaction_count` greater than 0 and short bursts of activity across captures, or a blocked process report shows the blocker's `` containing only `BEGIN TRAN` or a single statement of a larger unit - **Severity:** Critical - **Fix:** The transaction spans client-side work — a screen, a service call, or a loop that fetches and then decides. Every lock taken before the round-trip is held for the user's think time. Restructure so the transaction opens after all input is gathered and closes in the same batch or procedure, and use optimistic concurrency (a rowversion check on update) instead of holding read locks to guard against concurrent edits. ### BL26 — Elevated Transaction Isolation Level - **Trigger:** `transaction_isolation_level` is 3 (`RepeatableRead`) or 4 (`Serializable`) on the head blocker or on a blocked session - **Severity:** Warning; Critical when key-range locks (BL20) are the blocking resource - **Fix:** Above read committed, shared locks are held to the end of the transaction instead of being released after each read, so readers block writers for the whole transaction. Confirm whether the level is intentional: some client libraries and distributed-transaction stacks set `SERIALIZABLE` by default, and a `SET TRANSACTION ISOLATION LEVEL` left in a procedure persists for the connection, which pooling then hands to unrelated work. Set the level explicitly per connection to the weakest level correctness allows, and use a targeted hint at the one statement that needs stronger guarantees. ### BL27 — Implicit Transactions Enabled - **Trigger:** A blocking session is sleeping with an open transaction, no explicit `BEGIN TRAN` appears in its input buffer, and its `program_name` or driver indicates a client that sets `IMPLICIT_TRANSACTIONS ON` - **Severity:** Critical - **Fix:** With implicit transactions on, a bare `SELECT` or `UPDATE` opens a transaction that stays open until the client commits — which an autocommit-style application never does, so locks accumulate until the connection is reset. Turn the setting off at the driver or connection level (`SET IMPLICIT_TRANSACTIONS OFF`), or make the client commit explicitly after each unit of work. This is the most common cause of a sleeping session holding locks with an innocuous-looking last statement. ### BL28 — Blocking Lock Hints in the Blocker's Statement - **Trigger:** The head blocker's statement or input buffer contains `HOLDLOCK`, `SERIALIZABLE`, `TABLOCK`, `TABLOCKX`, `XLOCK`, `UPDLOCK`, `PAGLOCK`, or `REPEATABLEREAD` - **Severity:** Warning; Critical for `TABLOCKX`, or `XLOCK`/`HOLDLOCK` held for the length of a long transaction - **Fix:** Hints override the engine's lock choice and are frequently copied forward long after the reason for them has gone. Keep only those a correctness requirement needs, scope them to the single statement and table that needs them rather than the whole query, and prefer `UPDLOCK` on a narrow seek over `TABLOCKX` on the table. Note the mirror-image risk: `NOLOCK`/`READUNCOMMITTED` is not a blocking fix — it permits dirty, missing, and duplicated rows, and still waits behind a `Sch-M` lock (BL18). ### BL29 — Reader-Writer Blocking Curable by Row Versioning - **Trigger:** Victims wait in `LCK_M_S` or `LCK_M_IS` behind a writer holding `X` or `IX` locks, the victims' statements are read-only, and the database has `is_read_committed_snapshot_on` = 0 - **Severity:** Warning; Critical when read-only reporting sessions are the majority of the victims - **Fix:** Under read committed snapshot isolation, readers take a row version instead of a shared lock, which removes this entire class of blocking without changing query text. Enable it with `ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON` — while the statement runs, the connection issuing it has to be the only open connection to the database (single-user mode is not required), so run it in a maintenance window, with `WITH ROLLBACK IMMEDIATE` if disconnecting the other sessions is acceptable. Budget for the costs before enabling: version store space and I/O in TempDB, 14 bytes added per row as rows are updated, and different semantics for read-then-write patterns, which may need `UPDLOCK` to stay correct. Writers still block writers. ### BL30 — Row-Versioning Side Effects - **Trigger:** The database has RCSI or snapshot isolation enabled and the capture shows long-running transactions, a growing version store (TempDB, or the in-database persistent version store when ADR is on), or update-conflict errors (3960) in the application logs - **Severity:** Warning - **Fix:** Row versioning trades lock waits for version-store growth: the oldest open transaction pins versions for everything newer, so one forgotten transaction grows the version store until its home fills. Check where that store lives — with ADR enabled the versions go to the in-database persistent version store, otherwise to TempDB. Keep BL24 under control, monitor `sys.dm_tran_version_store_space_usage`, and size the relevant store for the peak. Under `SNAPSHOT` isolation, concurrent updates raise error 3960 instead of blocking, so the application needs retry logic; if that is not viable, use RCSI, which does not produce update conflicts. --- ## Observability and Platform Configuration Checks (BL31–BL36) ### BL31 — Blocked Process Threshold Not Configured - **Trigger:** `blocked process threshold (s)` has `value_in_use` of 0 - **Severity:** Warning - **Fix:** With the default of 0, no blocked process reports are produced, so the next incident leaves no automatic record of who blocked whom and the DBA has to be present while it happens. Set a threshold in seconds — a value around 5 to 30 matches most command timeouts — with `sp_configure 'blocked process threshold', 20` followed by `RECONFIGURE`; the setting takes effect without a restart. Pair it with a capture target (BL33) or the report has nowhere to land. ### BL32 — Blocked Process Threshold Set Ineffectively - **Trigger:** `blocked process threshold (s)` is between 1 and 4, or is set so high that real incidents never reach it - **Severity:** Warning - **Fix:** The lock monitor wakes every 5 seconds, so a threshold below 5 cannot detect anything sooner and only adds work; the supported range is 5 to 86,400 seconds. Reports are generated once per reporting interval per blocked task and are best-effort, not real-time. Set the value just under the application's command timeout so a report exists for every block the users actually see. ### BL33 — No Capture Target for Blocking Events - **Trigger:** No Extended Events session with the `blocked_process_report` event is defined or started; optionally also no `lock_escalation` or `xml_deadlock_report` event - **Severity:** Warning - **Fix:** Create a lightweight session with `blocked_process_report` (plus `lock_escalation` when escalation is suspected, and `attention` to catch the client timeouts that create orphaned transactions) writing to an `event_file` target, and set it to start with the server. For ad-hoc capture during an incident, `sql_batch_completed`/`rpc_completed` alongside the blocked process report shows what the blocker ran earlier in its transaction — the last statement alone often is not the one holding the locks. Deadlock graphs are captured by default in the `system_health` session; blocked process reports are not. ### BL34 — Lock Escalation Overrides in Effect - **Trigger:** Trace flag 1211 or 1224 is enabled instance-wide, or tables in the chain have `LOCK_ESCALATION` set to `DISABLE` or (on partitioned tables) left at `TABLE` when `AUTO` is wanted - **Severity:** Warning; Critical when 1211 is on, since it disables escalation unconditionally including under memory pressure - **Fix:** Escalation exists to cap lock memory, which is finite — with it disabled, a large transaction can exhaust lock memory and produce error 1204, which aborts the statement and rolls back the transaction. Prefer per-table `LOCK_ESCALATION = DISABLE` over the trace flags, treat either as a temporary mitigation while the query and transaction are fixed, and on partitioned tables consider `LOCK_ESCALATION = AUTO` so escalation stops at the partition instead of the table. ### BL35 — Scan-Driven Lock Footprint - **Trigger:** The head blocker's statement shows a table or index scan, a bookmark lookup over many rows, or a non-SARGable predicate (a function or conversion applied to a column), and its lock count or duration is in the Warning band - **Severity:** Warning - **Fix:** A query that reads more rows than it needs locks more rows than it needs and holds them longer, so index and query tuning is a locking fix as well as a performance fix. Route the statement to `/sqlplan-review` and `/sqlindex-advisor` for a covering index or a seek-enabling rewrite; make predicates SARGable by moving functions and conversions off the column side; and check implicit conversions, which turn a seek into a scan and are a common hidden cause. ### BL36 — Row-Versioning and Locking Platform Features Not Evaluated - **Trigger:** Repeated or long blocking is present and the instance-level mitigations are unused — `is_accelerated_database_recovery_on` = 0 where long rollbacks occur (BL11), or, on platforms that offer it, `is_optimized_locking_on` = 0 - **Severity:** Info; Warning when BL11 or BL16 fires repeatedly - **Fix:** Accelerated database recovery (SQL Server 2019 and later) makes lengthy rollbacks rare, which directly shortens the BL11 outage class, and it is a prerequisite for optimized locking. Optimized locking (SQL Server 2025 and Azure SQL Database / Managed Instance, where it is always on) replaces many row and key locks with a single transaction-ID lock and avoids most escalation; its lock-after-qualification component needs RCSI enabled to take effect. Under optimized locking the diagnostic shape changes — expect `XACT` resources in `sys.dm_tran_locks` and `LCK_M_S_XACT*` wait types — so read blocking chains with that in mind. Enable with `ALTER DATABASE SET OPTIMIZED_LOCKING = ON` after ADR is on, and validate the workload first: lock hints reduce the benefit, and code that relies on strict execution ordering under RCSI can see different results. --- ## Historical and Aggregate Blocking Evidence (BL37–BL42) Most blocking is reported after it ends. These checks work the artifacts that survive the incident, and say what to turn on so the next one is captured live. ### BL37 — Lock Waits Dominate but No Chain Was Captured - **Trigger:** `LCK_M_*` waits reach the Warning share of instance-wide wait time in `sys.dm_os_wait_stats` (or `sp_BlitzFirst @SinceStartup = 1`), and the input contains no blocking chain, blocked process report, or sampled capture - **Severity:** Warning; Critical at the Critical share - **Fix:** Lock waits are accumulated by the *blocked* session, never by the blocker, so wait statistics prove blocking happened and can never name who caused it. Treat this input as sizing, not diagnosis: report the share and the dominant lock modes (`LCK_M_S`/`LCK_M_IS` means readers waiting on writers and points at BL29; `LCK_M_X`/`LCK_M_U` means writer-on-writer; `LCK_M_SCH_S` means something holds `Sch-M`, see BL18 and BL43), then name the capture that closes the gap — the blocked process report (BL31–BL33) for blocks over five seconds, a sampled `sp_WhoIsActive` log (BL42) for shorter ones, and the per-index evidence in BL38 for where. Compute the share only after removing idle and background waits (the `lck_m_share_pct` column of the capture script's section 6d does this); against raw totals, waits such as `SOS_WORK_DISPATCHER` or `LOGMGR_QUEUE` swamp the denominator and a Critical lock share can read as Info. ### BL38 — Lock Wait Hot Spots by Index - **Trigger:** `sys.dm_db_index_operational_stats` shows an index whose average lock wait, total lock wait, or escalation attempts reach the Warning bands in the Thresholds Reference - **Severity:** Warning; Critical when one index carries the majority of the database's lock wait time - **Fix:** This is the only widely available artifact that attributes historical blocking to a specific object without a live capture, so it answers "where" when the chain is gone. Rank objects by `row_lock_wait_in_ms + page_lock_wait_in_ms`, then treat the top object as the target for index and query work (BL35, BL46) and for transaction shortening (BL24). Two caveats belong in the report: the counters are cumulative since the index's metadata entered the cache and reset when it is evicted or the object is rebuilt, so they are "roughly since restart" rather than a true history; and they cover row and page lock waits only — object, metadata, and application lock waits do not appear, so a `Sch-M` or `sp_getapplock` problem is invisible here. ### BL39 — Lock Escalation Attempts Recorded Against an Index - **Trigger:** `index_lock_promotion_attempt_count` exceeds the Warning threshold, especially when it substantially exceeds `index_lock_promotion_count` - **Severity:** Warning; Critical when attempts exceed the threshold on a table that also appears in a live chain - **Fix:** Attempts far above completions mean the engine repeatedly tried to escalate and was refused because another session held an incompatible table lock; it then kept acquiring fine-grained locks and retried at every 1,250 further locks. That pattern is both a large lock footprint (BL23) and a warning that a table lock is one scheduling accident away. Fix the statement that accumulates the locks — batch it, or index it so it touches fewer rows (BL16, BL35) — rather than reaching for the escalation overrides in BL34. A non-zero `index_lock_promotion_count` means an escalation succeeded: the table lock that results blocks others at `OBJECT` level, which the row and page wait columns do not record, so an escalated table can show zero lock wait in BL38 while having caused the incident. Do not dismiss it for that reason. ### BL40 — Query Store Lock Wait History Unused or Concentrated - **Trigger:** SQL Server 2017 and later (or Azure SQL): Query Store is off, or `sys.query_store_wait_stats` shows queries with `wait_category_desc = 'Lock'` accumulating significant wait time - **Severity:** Info when Query Store is off; Warning when a small set of queries carries most lock waits - **Fix:** Query Store attributes lock waits to individual queries and keeps them after the incident, which plain wait statistics cannot do. Turn it on where it is off, then rank queries by lock wait category to identify the repeat victims. Remember the direction of the evidence: these are the queries that *waited*, not the ones that blocked, so pair the result with BL38 (which object) and the blocked process report (which blocker) before concluding anything about root cause. Check `query_capture_mode_desc` before reading an empty result as good news: under `AUTO` (the default from SQL Server 2019) a query is stored only after 30 executions, 1 second of compile CPU, or 100 ms of execution CPU, and a blocked query spends its time waiting, not on CPU — so infrequent blocking victims are often never captured. Where lock-wait history per query matters, use `CUSTOM` capture with a lower threshold or `ALL` for the investigation window. ### BL41 — Blocking Performance Counters Not Baselined - **Trigger:** *Processes blocked* (`General Statistics` object) is non-zero across repeated samples, or *Lock Waits/sec* and *Lock Wait Time (ms)* are elevated with no baseline recorded - **Severity:** Info with an established baseline; Warning when the counter stays non-zero across samples with no alerting in place - **Fix:** *Processes blocked* is the cheapest continuous signal that blocking is happening at all, and it is what a monitoring alert should watch between deep captures. A steady non-zero value means blocking is chronic rather than incidental (pair with BL7). Collect the counters on a schedule from `sys.dm_os_performance_counters`, record a normal range for the workload, and wire the alert to fire the deeper capture — an alert with no capture attached produces a number nobody can act on. ### BL42 — No Sampled Blocking Log for Short-Duration Blocking - **Trigger:** Users report repeated short stalls or timeouts, the blocked process report shows little or nothing, and no periodic capture (logged `sp_WhoIsActive`, `sp_BlitzWho`, or equivalent monitoring) exists - **Severity:** Warning - **Fix:** The blocked process report cannot see below its threshold, and the lock monitor only wakes every five seconds, so high-frequency blocking that clears in two or three seconds is invisible to it while still costing the workload. Log a blocking-aware snapshot to a table on a short interval — `sp_WhoIsActive` with `@find_block_leaders = 1` and a destination table is the standard pattern — and keep it running only while the problem is being chased, because continuous logging nobody reads is pure overhead. Sample often enough to catch the stall, and record the chain, not just the counts. --- ## Structural and Engine-Level Causes (BL43–BL48) ### BL43 — Benign Head Blocker with a Blocking Request Queued Behind It - **Trigger:** The head blocker is an ordinary reader or short statement, its direct waiter requests an incompatible table-level mode (typically `Sch-M`, or `X`/`S` on an `OBJECT`), and sessions behind that waiter are queued on a mode the head blocker itself would not conflict with - **Severity:** Critical - **Fix:** Lock requests queue: once an incompatible request is waiting, later arrivals queue behind it even when they are compatible with what is currently granted. A long `SELECT` holding `Sch-S` therefore stalls an index rebuild's `Sch-M`, and every query arriving after that rebuild — which the `SELECT` alone would never have blocked — stops too. Read the chain one level down before recommending anything: killing the apparent head blocker only lets the queued `Sch-M` proceed, which is often the more disruptive operation. The durable fixes belong to the queued request, not the head: run DDL and index maintenance with `ONLINE = ON` and `WAIT_AT_LOW_PRIORITY` so it yields instead of queueing, set the equivalent parameters in the maintenance solution that schedules it, and move the work to a quiet window (BL15, BL18). ### BL44 — Statistics Update Blocking on Schema Locks - **Trigger:** A session running `UPDATE STATISTICS`, an automatic statistics update, or a statistics-metadata operation holds or waits for `Sch-M`, with query compilations waiting on `Sch-S` behind it - **Severity:** Warning; Critical when compilations across the instance are queued behind it - **Fix:** Creating or updating statistics takes a schema modification lock on the statistics metadata object, and every compiling query needs schema stability on the same object, so a statistics update and a compile-heavy workload block each other. Synchronous automatic updates (the default) also make the triggering query wait for the update to finish, which shows up as intermittent timeouts on an otherwise fast query. Options, in order: enable `ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY` (SQL Server 2022 and later, Azure SQL) so the background update queues at low priority instead of blocking compiles; consider `AUTO_UPDATE_STATISTICS_ASYNC` where client timeouts are aggressive, accepting that the triggering query compiles on stale statistics; update statistics manually before planned index maintenance so an automatic update does not fire mid-window; and on readable secondaries, where temporary statistics take the same `Sch-M` and can stall redo, evaluate the `READABLE_SECONDARY_TEMPORARY_STATS_AUTO_CREATE` and `READABLE_SECONDARY_TEMPORARY_STATS_AUTO_UPDATE` database-scoped configurations. ### BL45 — Lock Partitioning Amplifies Table-Level Lock Acquisition - **Trigger:** The instance has at least the Thresholds Reference count of logical CPUs (lock partitioning is enabled automatically on instances with a larger number of logical CPUs, and logged in the ERRORLOG at startup), `sys.dm_tran_locks` shows non-zero `resource_lock_partition` values, and a session is stuck acquiring a table-level `S`, `X`, or `Sch-M` lock - **Severity:** Warning - **Fix:** With lock partitioning, `NL`, `Sch-S`, `IS`, `IU` and `IX` are taken on one partition, but `S`, `X`, `Sch-M` and other full modes are taken on every partition in ID order. A table-wide request therefore acquires partitions 0..n one at a time and stops at the first partition where an intent lock is held — so it can be half-granted, blocking new arrivals on the partitions it already holds while itself waiting on one session. This is why a single long reader can stall an index rebuild for an unexpectedly long time on a large machine, and why deadlock frequency can rise. Do not disable lock partitioning; remove the need for table-wide locks instead (online and low-priority DDL per BL43, no `TABLOCK`/`TABLOCKX` hints per BL28, batching per BL16). ### BL46 — Unindexed Foreign Key Child Table - **Trigger:** The blocking statement updates or deletes parent rows, the waiters or the lock evidence point at a child table, and the child's foreign key column has no supporting index - **Severity:** Warning; Critical when the child table is large and the parent operation is routine - **Fix:** Enforcing a foreign key on delete or on a key update makes the engine look for referencing rows in the child; without an index on the foreign key column that becomes a scan, which takes locks across the child table, extends the parent transaction, and can cross the escalation threshold. Create a nonclustered index leading with the foreign key column (add covering columns only if a query needs them). This is the cheapest high-yield fix in the whole blocking catalogue, because it usually removes a deadlock class as well — route the plan to `/sqlindex-advisor` for the exact definition. ### BL47 — Trigger or Cascading Constraint Extends the Transaction - **Trigger:** The head blocker's statement is a simple DML statement, but its lock footprint in `sys.dm_tran_locks` includes tables the statement does not name — audit tables, history tables, or children of a cascading foreign key - **Severity:** Warning - **Fix:** Triggers and `ON DELETE`/`ON UPDATE CASCADE` actions run inside the caller's transaction, so their locks are held until the caller commits and their duration is added to the caller's. An audit trigger writing to a single hot log table serialises every writer on the base table (see BL19). Options: move the secondary work out of the transaction (queue it, or use Change Data Capture / change tracking / a temporal table instead of a hand-written audit trigger), index the child tables the cascade touches (BL46), and keep trigger bodies free of lookups that scan. Confirm the trigger's own statements before blaming the caller — `sys.dm_exec_sql_text` shows the outer batch, not the trigger body. ### BL48 — Write Statement Locks Every Row It Reads - **Trigger:** An `UPDATE` or `DELETE` at the head of the chain has a predicate that cannot seek (a function or conversion on the column, a leading wildcard, a mismatched type), and its lock count far exceeds the rows it actually modifies - **Severity:** Warning; Critical when the lock count reaches the escalation threshold - **Fix:** A write statement takes update locks on the rows it *examines*, not only the rows that qualify, so a non-SARGable `UPDATE` scanning a million rows to change ten locks its way through the whole index. The fix is the predicate, not the locking: move functions and conversions off the column side, match parameter types to column types so no implicit conversion appears in the plan, and index the predicate so a seek replaces the scan. Where the statement legitimately touches many rows, batch it (BL16) so no single transaction crosses the escalation threshold. --- ## Client, Tooling, and Platform Patterns (BL49–BL54) ### BL49 — ORM or Driver Transaction Defaults - **Trigger:** The blocking session's `program_name` or `client_interface_name` identifies an ORM or managed driver, and its behaviour matches a framework default — an elevated isolation level with no `SET` in the statement text (BL26), an open transaction with no `BEGIN TRAN` (BL27), or several sessions from one host interleaving work (BL13) - **Severity:** Warning; Critical when it produces a sleeping session holding locks - **Fix:** Framework defaults, not application intent, are behind a large share of production blocking. Common ones worth checking by name: a .NET `TransactionScope` created without options defaults to `Serializable`, which brings key-range locks with it (BL20) — construct it with `ReadCommitted` explicitly; JDBC and several Python drivers open implicit transactions unless autocommit is set, producing the sleeping-with-open-transaction shape (BL27); and multiple active result sets on one connection change how statements interleave. Fix these in the connection or context configuration rather than in T-SQL, since the setting arrives with every new pooled connection. ### BL50 — No Client Timeout or Retry Policy - **Trigger:** Victims' waits exceed any sane client timeout with no evidence of cancellation, or the application reports hung requests rather than errors, or orphaned transactions (BL10) recur after timeouts - **Severity:** Warning - **Fix:** A blocked request that never gives up converts one stuck session into a pile of stuck sessions, and pushes the instance toward worker exhaustion (BL6). Give the client a command timeout, and for work that can safely fail fast, set `SET LOCK_TIMEOUT` so the statement returns error 1222 instead of waiting — but only alongside a handler that rolls back, because a timeout without a rollback is exactly how BL9 and BL10 are created. Add bounded retry with backoff for the operations that can be retried safely, and set `DEADLOCK_PRIORITY` deliberately on batch work so interactive sessions win. This check is about the policy existing, not about masking the blocking. ### BL51 — Azure SQL Platform Differences Not Accounted For - **Trigger:** The artifact comes from Azure SQL Database, Azure SQL Managed Instance, or Fabric SQL database, and the analysis or the recommendation assumes on-premises behaviour - **Severity:** Info; Warning when a recommendation would not apply on the platform - **Fix:** Adjust the reading rather than the checks. New databases in Azure SQL Database have read committed snapshot and snapshot isolation enabled by default, so reader-versus-writer blocking (BL29) should already be gone and remaining blocking is writer-versus-writer, an elevated isolation level set by the client, or RCSI having been turned off. Optimized locking is always on there, so expect `XACT` resources and `LCK_M_S_XACT*` waits (BL36). The blocked process threshold is not user-configurable (BL31/BL32 do not apply); wait statistics are database-scoped through `sys.dm_db_wait_stats`; Profiler is not supported, so capture is Extended Events only; and the scale-out answer for long readers is a read-only replica rather than a reporting server. Transient-fault retry is a platform expectation, which reinforces BL50. ### BL52 — Readable Secondary Redo Blocked by Report Queries - **Trigger:** The artifact comes from a readable secondary replica and shows redo progress stalled behind reader locks — `Sch-S` held by report queries against a `Sch-M` request from redo or from temporary statistics creation, or AG redo waits accumulating with active long readers - **Severity:** Critical - **Fix:** Redo on a readable secondary applies schema changes from the primary and needs schema modification locks to do it; a long-running report holding schema stability blocks redo, which stops the secondary from staying current and grows the redo queue, extending both failover time and the data-loss window. Automatic temporary statistics created for read workloads take the same lock. Keep report queries short, run them with a lock timeout, and consider the `READABLE_SECONDARY_TEMPORARY_STATS_AUTO_CREATE` and `READABLE_SECONDARY_TEMPORARY_STATS_AUTO_UPDATE` database-scoped configurations. Quantify the exposure with `/sqlhadr-review` before changing the reporting schedule. ### BL53 — Head Blocker Waiting on Commit Acknowledgement - **Trigger:** The head blocker's `wait_type` is `HADR_SYNC_COMMIT`, a distributed-transaction wait (`DTC`, `PREEMPTIVE_TRANSIMPORT`, `DTC_STATE`), or another commit-path wait, while it holds locks - **Severity:** Critical - **Fix:** This is BL14 in its highest-impact form: the transaction has finished its work and is waiting for someone else to acknowledge the commit, with every lock still held. For synchronous-commit availability groups, the ack is the secondary hardening the log, so secondary log-write latency or network latency is now the blocking cause — route to `/sqlhadr-review` and `/sqldiskio-review`, and consider whether that replica needs to be synchronous. For distributed transactions, the coordinator's round trip does the same thing, which is another reason to keep remote work outside the transaction (BL5, BL24). No amount of lock tuning helps until the commit path is fixed. ### BL54 — No Alerting or Escalation Path for Blocking - **Trigger:** Blocking is recurring (BL7) and there is no alert on blocked processes or on the blocked process report event, or no documented decision on who may issue `KILL` and under what conditions - **Severity:** Warning - **Fix:** Detection without a response path means every incident waits for a human to notice. Wire an Agent alert or monitoring rule to the blocked process report event or to a sustained *Processes blocked* value (BL41), and have it start the capture in BL42 automatically so the evidence exists before anyone logs in. Write down the escalation rule while nobody is under pressure: which head-blocker states justify an immediate `KILL` (BL9 and BL10 do; BL11 never does, because the rollback still has to finish), who is authorised, and what gets recorded afterwards so BL7 can be evaluated across incidents. --- ## Output Format Present findings in this order: ``` ## Blocking Analysis ### Blocking Summary - Head blocker: session ( / / ) - State: / wait_type / open_transaction_count — - Impact: sessions blocked, deepest chain levels, longest wait - X Critical, Y Warnings, Z Info ### Blocking Chain Map session 71 (head) UPDATE dbo.Orders ... sleeping, open_tran 1, idle 6m └─ 84 LCK_M_U 42s KEY 7:72057594045267968 SELECT ... FROM dbo.Orders └─ 92 LCK_M_S 38s KEY 7:72057594045267968 SELECT ... ### Critical Issues (labeled [C1], [C2], ...) ### Warnings (labeled [W1], [W2], ...) ### Info (labeled [I1], [I2], ...) ### Lock Evidence | Session | Role | Resource | Mode | Status | Count | Object | ### Historical Evidence (when the live chain is gone, or alongside it) | Source | Object / query | Metric | Reading | | index operational stats | dbo.Orders (CIX) | 412 s total lock wait, 38 escalation attempts | hot spot, escalation pressure (BL38, BL39) | ### Remediation Priority | # | Action | Addresses | Effect | Risk | Rollback | ### Passed Checks BL5, BL13, BL20, ... (explicitly verified clean) ``` Each finding states **Observed** (the value in the input) → **Impact** (what it costs) → **Fix** (what to do), with the check ID in parentheses after the issue name, for example `[C1] Sleeping session holding locks (BL9)`. Separate immediate relief from durable fixes in the Remediation Priority table: name the one action that ends the current incident first (usually `KILL` of the head blocker, or waiting when BL11 applies), then the design changes that stop recurrence. For any `KILL` recommendation, state the rollback cost — a kill on a large write transaction starts a rollback that can take as long as the original work. When the input does not cover a check, list it under a short "Not evaluated" note with the capture query that would cover it, rather than reporting it as passed. > Analyzed by: `sqlblocking-review` (BL1–BL54) --- *Analyzed by: [state the AI model and version you are running as, e.g. "Claude Sonnet 4.6", "DeepSeek R1", "GPT-4o"] · [current date and time in the user's local timezone, or UTC if timezone is unknown, e.g. "2026-05-16 20:15 NZST"]* --- ## Companion Skills - `/sqlwait-review` — instance-wide `LCK_M_*` wait totals (V-checks) show how much of the workload's wait time is blocking; this skill explains a specific chain, that one sizes the problem - `/sqldeadlock-review` — a deadlock is blocking that closed into a cycle; the same lock evidence (modes, resources, hints, isolation) drives both, so run it when the chain resolves itself by an error 1205 rather than a wait - `/sqlplan-review` and `/sqlindex-advisor` — BL35 and BL8 route here: scans and lookups in the blocker's plan are what make the lock footprint large and the transaction long - `/sqltrace-review` — shows the earlier statements in the blocker's transaction, which the last-statement view of the DMVs hides - `/sqlquerystore-review` — confirms whether a chronic head blocker (BL7) regressed recently and provides its plan history and lock wait category totals - `/sqldbconfig-review` — database-level concurrency settings behind BL29/BL30/BL36 (RCSI, snapshot isolation, ADR) are audited there - `/sqlmemory-review` — lock memory pressure is an escalation trigger (BL23) and `RESOURCE_SEMAPHORE` at the head blocker (BL14) is a memory problem - `/sqldiskio-review` — `PAGEIOLATCH_*`/`WRITELOG` at the head blocker (BL14) means slow storage is lengthening the lock hold - `/sqlhadr-review` — BL52 (redo blocked by readers on a readable secondary) and BL53 (`HADR_SYNC_COMMIT` at the head blocker) are AG problems wearing a locking costume; size the redo queue and commit latency there - `/tsql-review` — catches the source-level causes before deployment: missing `SET XACT_ABORT ON`, transactions around round-trips, stray isolation-level and lock hints --- ## VERSION_COMPATIBILITY See [skills/VERSION_COMPATIBILITY.md](../VERSION_COMPATIBILITY.md) for the full compatibility matrix. | Check | 2008 R2 | 2012 | 2014 | 2016 | 2017 | 2019 | 2022 | Azure SQL | |-------|---------|------|------|------|------|------|------|-----------| | BL1 Head blocker | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL2 Long lock wait | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL3 Chain depth | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL4 Fan-out | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL5 Cross-database chain | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | Partial | | BL6 Concurrency exhaustion | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL7 Chronic head blocker | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL8 Long-running query | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL9 Sleeping with open tran | Partial | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL10 Orphaned transaction | Partial | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL11 Rollback state | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL12 Client not fetching | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL13 Distributed deadlock | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL14 Non-lock wait at head | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL15 Maintenance at head | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | Partial | | BL16 Lock escalation | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL17 Object X lock | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL18 Sch-M blocking | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL19 Hot resource | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL20 Key-range locks | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL21 Conversion wait | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL22 Application lock | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL23 Lock footprint | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL24 Long open transaction | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL25 Transaction across round-trips | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL26 Elevated isolation | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL27 Implicit transactions | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL28 Lock hints | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL29 RCSI candidate | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | On by default | | BL30 Version store effects | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL31 BPR threshold off | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | N/A | | BL32 BPR threshold ineffective | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | N/A | | BL33 No capture target | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | Partial | | BL34 Escalation overrides | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | Partial | | BL35 Scan-driven footprint | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL36 ADR / optimized locking | — | — | — | — | — | ADR | ADR | ✓ | | BL37 Lock waits, no chain | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL38 Lock hot spots by index | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL39 Escalation attempts per index | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL40 Query Store lock waits | — | — | — | — | ✓ | ✓ | ✓ | ✓ | | BL41 Blocking counters | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | Partial | | BL42 No sampled blocking log | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL43 Queued Sch-M behind reader | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL44 Statistics update blocking | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL45 Lock partitioning | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | Partial | | BL46 Unindexed foreign key | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL47 Trigger / cascade | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL48 Write locks rows it reads | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL49 ORM / driver defaults | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL50 Timeout / retry policy | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | BL51 Azure platform differences | N/A | N/A | N/A | N/A | N/A | N/A | N/A | ✓ | | BL52 Readable secondary redo | — | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | Partial | | BL53 Commit acknowledgement wait | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | Partial | | BL54 No alerting path | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |