/sqlblocking-review

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 `<blocked-process>` and `<blocking-process>` 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

-- 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.