SQL Performance Intelligence
SQL PerformanceIntelligence™
Use Cases/Blocking

Tracing a Blocking Storm Back to a Single Head Blocker

An OLTP system froze for seconds at a time during peak hours. We followed the blocking chain to one long-running transaction and resolved the contention without touching the schema.

6 min readSQL Server 2019OLTP workloadQuery Store ONRead Committed

Scenario

During the morning peak, order entry would intermittently freeze for 5–15 seconds. Users saw timeouts on checkout, but CPU and memory looked healthy on the host, so the on-call DBA could not explain the stalls from infrastructure metrics alone.

Symptoms

  • Application timeouts clustered between 09:00 and 10:30 local time.
  • No sustained CPU, memory, or disk pressure on the instance.
  • Wait time spiked even though throughput dropped — a classic contention signature.

How We Analyzed It

Each step maps a concrete question to the module and signal used to answer it — all read-only against production.

  1. 1
    Dashboard

    Confirmed the host was not resource-bound during the stalls.

    Signal: Stable CPU/memory/IO while active request count climbed sharply.

  2. 2
    Blocking Analysis

    Captured a live snapshot during a stall and expanded the blocking chain.

    Signal: A single head blocker holding key locks while 40+ sessions queued behind it.

    Blocking Analysis: the chain expanded from the head blocker. (Placeholder image.)
  3. 3
    Query Statistics

    Looked up the head blocker statement in Query Store.

    Signal: A reporting query running inside the same transaction as a small update.

Evidence

Evidence — Head blocker
One session (SPID 73) held LCK_M_X on the orders clustered index for ~12 seconds inside an explicit transaction that also ran an ad-hoc report.
Evidence — Blast radius
The blocking snapshot showed 41 dependent sessions, all waiting on the same key range — confirming a single root cause rather than general overload.
Note
Because the analysis is read-only, the snapshot was safe to capture against production during the incident.
Head blocker session detail and lock information. (Placeholder image.)

Recommendation

Split the long-lived reporting read out of the write transaction and shorten the transaction scope so locks release immediately after the update.

  • Move the ad-hoc report read outside the explicit transaction (separate connection / after COMMIT).
  • Wrap only the update in the transaction so exclusive locks are held for milliseconds, not seconds.
  • For the report path, evaluate READ COMMITTED SNAPSHOT to remove reader/writer blocking entirely.
-- Investigation only — review before applying any isolation change.
-- 1) Confirm the head blocker pattern (read-only):
SELECT  blocking_session_id, session_id, wait_type, wait_time, status
FROM    sys.dm_exec_requests
WHERE   blocking_session_id <> 0;

-- 2) Optional: evaluate RCSI in a non-production copy first.
-- ALTER DATABASE [Sales] SET READ_COMMITTED_SNAPSHOT ON;
Note
The application never applies changes automatically. Every script above is an example to review and run under your own change-control process.

Outcome

After scoping the transaction and moving the report read out, the morning stalls disappeared. Follow-up reviews over the next two weeks found no recurrence of the blocking chain.

Peak blocking chain depth
41 sessions→0–2 sessions
Max lock hold time
~12 s→< 50 ms
Checkout timeouts / day
~120→0

Modules used in this case

Blocking AnalysisQuery StatisticsDashboard
Back to Use CasesDownload