Locking and Blocking Analysis – RockyPC/WideWorldImporters – 2026-09-27T17:10:29Z

Scope and platform

The snapshot also contains blocking activity in SQLStorm and deadlock history from DeadlockDemo. Those entries are reported separately and are not treated as WideWorldImporters evidence unless explicitly stated.

Executive summary

Top priorities

1. Resolve session 64's open transaction. It began at 2026-09-26 05:20:45 and has duration 114,583 seconds. It holds an X key lock on Application.StateProvinces.PK_Application_StateProvinces. Confidence: High.
2. Remove the deliberate transaction delay and enforce consistent table access order. The captured SQL contains WAITFOR DELAY '00:00:10' between updates and the deadlock history shows repeated opposite-order updates of StateProvinces and Countries. Confidence: High.
3. Add the missing supporting index on Application.Cities(StateProvinceID) including CityName, subject to index-tuning confirmation. The waiter plan scans all estimated 37,940 Cities rows and assigns 64% of statement cost to that scan. Confidence: Medium.
4. Consider RCSI only after workload validation. RCSI is OFF, so ordinary readers can wait behind writers; however, RCSI will not solve the open transaction or writer-writer deadlocks. Confidence: Medium.

Blocking confirmation and impact

Explicit evidence: LockInventory reports session 64 with an X key lock on StateProvinces and session 65 with an S key lock request in WAIT status on the same index. ActiveTransactions reports session 64 as an open user transaction with 114,583 seconds duration.

Blocking tree

The most important waiter statement is the complete session 65 query: SELECT CityName, StateProvinceName, sp.LatestRecordedPopulation, CountryName FROM Application.Cities AS city JOIN Application.StateProvinces AS sp ON city.StateProvinceID = sp.StateProvinceID JOIN Application.Countries AS ctry ON sp.CountryID = ctry.CountryID WHERE sp.StateProvinceCode = N'VA'. Its batch context begins with BEGIN TRAN;, so the read transaction remains open while it waits.

The lead blocker is session 64, not session 65. Its current statement is empty, its transaction is open, and LockInventory shows the held X key on StateProvinces. The supplied blocker SQL includes BEGIN TRAN; UPDATE Application.StateProvinces ...; WAITFOR DELAY '00:00:10';, but the transaction has remained open far longer than the ten-second delay.

Root-cause classification

Detailed prioritized recommendations

1. Terminate or complete session 64's abandoned transaction immediately

   Fix: Have the owning application or SSMS session issue COMMIT if the update is valid, or ROLLBACK if it is a test/abandoned transaction. If ownership cannot be established, terminate session 64 under operational change control.

   Rationale: Session 64 has been open for 114,583 seconds with no active statement and holds the X key required by session 65. This directly removes the observed LCK_M_S wait.

   Risk and rollback: COMMIT makes the pending StateProvinces update durable. ROLLBACK or terminating the session discards uncommitted work and may cause application errors. A rollback itself can take time. There is no safe database-level rollback after a committed transaction; preserve the intended business choice before acting.

   Expected effect: Removes the session 64 to session 65 key conflict. Labelled estimate: approximately 100% reduction of this specific observed wait if no new holder immediately acquires the same key; basis: one identified holder and one identified waiter on the same key.

   Confidence: High.

2. Remove WAITFOR from production transaction code and enforce one resource acquisition order

   Fix: Both code paths that update Application.StateProvinces and Application.Countries must update the tables in the same order and commit immediately. The supplied deadlocks show one path taking StateProvinces then Countries and the other taking Countries then StateProvinces.

   Rationale: DeadlockHistory contains five repeated WideWorldImporters deadlocks on 2026-09-20 and 2026-09-26. The statements explicitly contain a ten-second WAITFOR while holding the first X lock. This is a deterministic circular-wait pattern.

   Risk and rollback: Changing order can expose application assumptions about intermediate state; test both updates atomically. Removing the delay changes timing but preserves the two increments and timestamp assignments. Rollback is a code deployment rollback to the prior module version; do not restore the deliberate delay.

   Expected effect: Prevents the specific StateProvinces/Countries circular waits shown in the five WideWorldImporters deadlock graphs and reduces lock hold time. Labelled estimate: up to 100% reduction for this exact two-table deadlock pattern; basis: all five reported WideWorldImporters graphs show the same inverse ordering.

   Confidence: High.

3. Add the Cities lookup index after confirming with the Locking and Blocking index-tuning goal

   Fix: Create a nonclustered index on Application.Cities(StateProvinceID) including CityName. Confirm the recommendation by running the Index Tuning goal for the specific schema and table for a comprehensive workload analysis.

   Rationale: BlockerPlans reports a clustered scan of all estimated 37,940 Cities rows, own cost 0.411, or 64% of statement cost. MissingIndexes reports equality column StateProvinceID, included CityName, impact 59.5; the plan's missing-index detail reports 93.0% impact.

   Estimated size: The input does not report candidate-index storage, column widths, or the existing Cities clustering-key width. Estimate: approximately 1.5–4.0 MB after build, based on 37,940 rows and an assumed combined stored width of approximately 24–64 bytes per row for the StateProvinceID key, CityName include, clustering key, row overhead, and page overhead, with no compression assumption. This is an estimate, not a reported size. Existing PK_Application_Cities is reported as 3.9 MB.

   Risk and rollback: Additional storage, write maintenance, statistics, and build I/O. Online creation is supported by the reported Enterprise engine capability, but the Developer Edition may still require appropriate maintenance-window testing. Rollback is dropping the new index after confirming no dependency; the estimated reclaimed storage is the same 1.5–4.0 MB range.

   Net storage effect: One new index: estimated +1.5–4.0 MB. No index is recommended for dropping or modifying.

   Expected effect: May replace the 37,940-row Cities scan with an index seek/lookup and reduce the reader's lock footprint and elapsed time. Labelled estimate: 50–90% reduction in the Cities component of this statement's logical work; basis: the current plan reads all estimated 37,940 rows and assigns 64% of statement cost to that scan. It is not expected to release session 64's held X lock.

   Confidence: Medium.

4. Evaluate enabling Read Committed Snapshot Isolation for reader-versus-writer blocking

   Fix: Test and, if compatible with application semantics, enable RCSI for WideWorldImporters. RCSI is currently OFF, snapshot isolation is OFF, and the version store is 0.0 MB.

   Rationale: The session 65 read requests an S key lock while session 64 owns an X key lock. RCSI can allow ordinary ReadCommitted readers to use row versions instead of waiting for many writer locks.

   Risk and rollback: RCSI changes read-consistency behavior and increases version-store and TempDB usage under write load. The input reports TempDB LATCH_EX and PAGEIOLATCH_SH waits, so capacity testing is required. Rollback is disabling RCSI after all connections are drained and after confirming application behavior; this does not undo transactions.

   Expected effect: Potentially removes reader-writer waits such as session 65's LCK_M_S, but does not remove writer-writer deadlocks or the need to close session 64. Labelled estimate: 0–100% reduction for compatible reader-writer waits; basis: RCSI addresses the S-versus-X pattern, but workload compatibility and version-store capacity are not established.

   Confidence: Medium.

Historical deadlocks and Query Store

Deadlock capture status: CAPTURED. The system_health session is running and reports 7 deadlocks. Capture is working, so enabling deadlock capture is not recommended.

Five deadlocks are recurring in WideWorldImporters on the same StateProvinces/Countries inverse-order pattern. Two additional deadlocks are in DeadlockDemo and one is in tempdb; those are separate workloads. The rolling system_health history is short, but the supplied seven events are sufficient to prioritize code ordering and removal of the deliberate delay. A dedicated longer-retention Extended Events session is not required by this snapshot because recurrence frequency sufficient to lose the current pattern is not established.

Query Store is enabled. Its top entries include deadlock-history-reading queries with TotalCPU of 18,832 ms, 14,472 ms, and 10,785 ms, plus metadata/index diagnostics. The supplied Query Store top list does not identify the sp00215 or sp00199 SQLStorm procedures as top resource consumers. Query Store therefore supports workload history but does not provide a stronger causal attribution for the current WideWorldImporters lock holder.

For SQLStorm, session 52 runs a truncated WITH RankedPosts AS ... ROW_NUMBER() OVER (PARTITION BY p.OwnerUserId ORDER BY p.CreationDate DESC) statement in sp00215, with parallel synchronization waits. Session 53 runs a truncated WITH UserStatistics AS ... COUNT(DISTINCT p.Id) ... statement in sp00199, with HTMEMO waits. TempDB reports 1,341 total spills for a UserBadgeCounts query and 105 total spills for a UserPostStats query. These are performance amplifiers in SQLStorm, but the supplied evidence does not establish them as blockers of WideWorldImporters session 65.

Scripts

Create the supporting Cities index — implements recommendation 3

USE [WideWorldImporters];
GO

CREATE NONCLUSTERED INDEX [IX_Application_Cities_StateProvinceID]
ON [Application].[Cities] ([StateProvinceID])
INCLUDE ([CityName])
WITH (ONLINE = ON, SORT_IN_TEMPDB = OFF);
GO

Apply both updates in a consistent order without an artificial delay — implements recommendation 2

USE [WideWorldImporters];
GO

SET XACT_ABORT ON;
GO

BEGIN TRANSACTION;

UPDATE [Application].[StateProvinces]
SET [LatestRecordedPopulation] = [LatestRecordedPopulation] + 1,
    [ValidFrom] = SYSUTCDATETIME()
WHERE [StateProvinceCode] = N'VA';

UPDATE [Application].[Countries]
SET [LatestRecordedPopulation] = [LatestRecordedPopulation] + 1,
    [ValidFrom] = SYSUTCDATETIME()
WHERE [IsoNumericCode] = 840;

COMMIT TRANSACTION;
GO

No RCSI enablement script is included because changing database isolation behavior is a deployment decision and the input lacks workload-compatibility evidence. The two scripts above are the recommended implementation changes and should be tested according to the stated risks.

Supporting scripts (not part of the change)

No supporting scripts are included. The snapshot already provides the required blocking, lock, transaction, plan, deadlock, Query Store, and TempDB evidence for this report.