Locking and Blocking Analysis – VMEnt\SQL25 / ShopDemo – 2026-11-13 13:59:59 UTC

Tuning goal: Locking and Blocking Analysis

Server: VMEnt\SQL25  |  Version: SQL Server 2025 (17.0.1000.7), RTM  |  Database: ShopDemo

Enterprise Developer / Enterprise engine Blocking active RCSI OFF Optimized Locking OFF Deadlock capture: CAPTURED

Executive summary

Blocking is active. Session 102 is the top lead blocker: it owns an X object lock while an explicit transaction remains open during WAITFOR DELAY '00:15:00';. Session 103 is the principal downstream amplifier because it holds an X key lock on Customers.PK_Customers while its transaction waits on session 102.
5distinct blocked sessions: 101, 103, 104, 105, and 106
10,296 mslongest reported waiter duration
43,400 mssum of the five reported waiter durations
13 secondslongest open transaction duration shown: session 102

The highest-priority correction is to remove the deliberate delay from the transaction and commit or roll back immediately after the required work. Next, shorten the transaction scope in dbo.usp_ShipOrder, evaluate RCSI, and enable SQL Server 2025 optimized locking only after ADR is enabled and during a maintenance window.

  1. Immediate: stop holding the session 102 transaction across the 15-minute wait. Evidence: Active Blocking Chains identifies session 102 as WAITFOR; its batch contains BEGIN TRANSACTION; EXEC dbo.usp_ApplyLateFees; WAITFOR DELAY '00:15:00';; Lock Inventory shows session 102 with Mode=X, Status=GRANT at OBJECT level.
  2. Immediate: shorten and consistently order the transaction in dbo.usp_ShipOrder. Evidence: session 103 holds an X key lock on Customers.PK_Customers, waits on session 102, and blocks sessions 101, 104, and 105.
  3. High priority: enable RCSI after application testing to let ordinary read-committed readers use committed row versions. Evidence: RCSI is OFF; sessions 101 and 106 wait with LCK_M_S on a customer key.
  4. High priority: enable ADR and then optimized locking in a maintenance window. Evidence: SQL Server 2025 is available, ADR and optimized locking are OFF, and the inventory contains an OBJECT X lock plus transaction-held DML locks.
  5. Medium priority: add a covering access path for the customer balance query after confirming with the Index Tuning goal. Evidence: session 104 has a clustered scan reading 200,001 Orders rows, with 97% of statement cost in the scan; no missing-index candidate was emitted, so this is a manually inferred candidate rather than a captured recommendation.

Environment and scope

PropertyObserved valueInterpretation
EngineSQL Server 2025, Enterprise engine, Enterprise Developer EditionOnline index operations and optimized locking capability are available.
Observation periodServer uptime and database statistics cover approximately 2.4 days since 2026-11-11Usage and plan-cache figures are a recent window, not a long-term baseline.
Isolation configurationRCSI OFF; snapshot isolation OFFRead-committed readers acquire shared locks and can wait behind writers.
ADR and optimized lockingADR OFF; optimized locking OFFOptimized locking cannot be enabled until ADR is enabled.
StatisticsAutoUpdateStats ON; asynchronous update OFF; low-priority async wait OFFSynchronous statistics refresh can prolong some compilations.
TempDBVersion store 0.0 MB; no spilling queries; PAGELATCH_EX total 235 ms, maximum 4 msThe snapshot is not consistent with TempDB contention masquerading as the current lock waits.

Active blocking and blocking tree

The snapshot contains active LCK_M_S, LCK_M_IX, and LCK_M_IS waits. The relationships are:

Blocking tree for sessions 102, 103, 101, 104, 105, and 106 Session 102 blocks session 103. Session 103 blocks sessions 101, 104, and 105. Session 101 blocks session 106. Session 102WAITFOR; X object13 s transaction Session 103UPDATE Orders10,299 ms elapsed Session 101SELECT Customer8,277 ms wait Session 104CustomerBalance8,276 ms wait Session 105INSERT Orders8,276 ms wait Session 106SELECT Customer8,275 ms wait
Figure 1. Current blocking tree. Arrows indicate the blocker-to-waiter relationship reported in Active Blocking Chains; displayed wait durations are the snapshot values.
SessionRoleWait or lock evidenceMost important SQL
102Top lead blockerTransaction duration 13s; granted OBJECT X lock; status suspended on WAITFOR.WAITFOR DELAY '00:15:00'; inside an open transaction after EXEC dbo.usp_ApplyLateFees.
103Downstream blocker and waiterWaits on session 102; holds X on Customers.PK_Customers; blocks sessions 101, 104, and 105.UPDATE dbo.Orders SET Status = 'Shipped' WHERE OrderId = @OrderId.
101WaiterLCK_M_S on a Customers key for 8,275 ms; itself blocks session 106.SELECT CustomerId, Name, Email, Region, LastOrderDate FROM dbo.Customers WHERE CustomerId = @CustomerId.
104WaiterLCK_M_IS on an Orders OBJECT resource for 8,276 ms.Customer balance aggregation over Orders.
105WaiterLCK_M_IX on an Orders OBJECT resource for 8,276 ms.Single-row Orders INSERT.
106WaiterLCK_M_S on the same Customers key for 8,275 ms.Same point lookup as session 101.
Important statement distinction: the compact statement for session 102 is the currently executing WAITFOR, but the batch context explains the cause: BEGIN TRANSACTION; EXEC dbo.usp_ApplyLateFees; WAITFOR .... The compact statement for session 103 is the Orders update, while its batch context shows the transaction first updates Customers and commits only afterward.

Root-cause classification

CategoryAssessmentEvidence and confidence
Long-running transactionConfirmed; primary cause.Session 102 has a 13-second open transaction and deliberately waits for 15 minutes inside it. Lock Inventory shows a granted OBJECT X lock. High confidence.
Open transaction with no activityConfirmed as transaction amplification.Session 102 is suspended on WAITFOR while its transaction remains open. It is not an idle session, but it is performing no useful database work during the delay. High confidence.
Hotspot table or indexConfirmed for Customers and Orders during this event.Session 103 holds X on Customers.PK_Customers; two customer point reads wait on the same key. Orders has OBJECT-level IX/IS waiters. High confidence.
Lock escalationPossible, not proven.Lock Inventory reports a granted OBJECT X lock for session 102 and table escalation is TABLE for both Customers and Orders. The snapshot does not identify the locked object or provide an escalation event, so the OBJECT X lock may be directly acquired rather than escalated. Medium confidence.
SERIALIZABLE or REPEATABLE READ misuseNot supported.All listed sessions use ReadCommitted. High confidence.
Implicit transactionsNot supported.The batches show explicit BEGIN TRANSACTION where applicable; no implicit-transaction setting is supplied. Medium confidence.
Parameter sniffing causing large scansNot supported by the supplied plan.The point lookup estimates one row and uses a clustered seek. The Orders balance query scans, but no parameter-sensitive plan evidence is supplied. High confidence.
Missing index causing lock amplificationLikely secondary contributor for session 104.BlockerPlans shows a Clustered Index Scan on Orders reading 200,001 rows, 97% own statement cost, for predicates on CustomerId and Status. MissingIndexes reports no candidates, so confidence is medium rather than high.
RBAR/cursor patternsNot supported.No cursor or row-by-row loop appears in the blocking statements. High confidence.
TempDB contention masquerading as blockingRuled out for this snapshot.Current waits are lock waits; version store is 0.0 MB, no spills are found, and PAGELATCH_EX totals only 235 ms with a 4 ms maximum. High confidence.

Impact metrics

MetricValueBasis
Total sessions blocked5 distinct sessionsSessions 101, 103, 104, 105, and 106 appear as waiters. Session 103 appears as both waiter and blocker.
Longest wait10,296 msSession 103 waiting on session 102 with LCK_M_IX.
Cumulative reported waiter duration43,400 ms8,275 + 10,296 + 8,277 + 8,276 + 8,276 ms across the five distinct waiter entries.
Observed transaction durations13s, 10s, and 8sSessions 102, 103, and 105 respectively in Long-Running Transactions.
Business impact supported by inputFive sessions experienced lock waits, with the longest shown at 10.296 seconds.The snapshot does not provide failed requests, throughput loss, user counts, or monetary impact.

Detailed prioritized recommendations

  1. 1. Remove the WAITFOR from the open transaction in session 102

    Fix: In the SQL Agent job step that executes EXEC dbo.usp_ApplyLateFees, commit or roll back immediately after the procedure completes. If a delay is required for orchestration, place it outside the transaction and outside the database session that owns the locks.

    Rationale: Active Blocking Chains identifies session 102 as a lead blocker with WAITFOR DELAY '00:15:00';. ActiveTransactions reports a 13-second transaction. Lock Inventory reports a granted OBJECT X lock for session 102. The wait is therefore directly holding locks while no useful database operation is executing.

    Risk: Medium. Moving the delay outside the transaction changes the period during which the procedure's changes are protected as one unit, so the job's business semantics must preserve the required commit boundary.

    Rollback: Restore the prior job-step transaction placement if the application requires the original atomic scope; do not restore the deliberate delay inside the transaction as a permanent design.

    Confidence: High.

    Expected blocking effect: Removes the session 102-to-103 lock wait and releases the root lock earlier, allowing the downstream chain to drain. A percentage is not estimated because the snapshot does not show the transaction's normal work duration or execution frequency.

  2. 2. Shorten and standardize the transaction in dbo.usp_ShipOrder

    Fix: Keep the two required updates in one short transaction, but ensure there is no work or delay between the Customer update and the Orders update, and commit immediately after the Orders update. Acquire resources in a consistent order across related procedures. The supplied module context is truncated, so only the complete visible statement shape can be prescribed.

    Rationale: Session 103 begins a transaction, updates Customers, then updates Orders. It holds an X key lock on Customers.PK_Customers while waiting on session 102 and blocks the customer reads of sessions 101 and 106, as well as the Orders requests of sessions 104 and 105.

    Risk: Medium. Changing transaction scope or resource order can affect atomicity and concurrency behavior.

    Rollback: Restore the original dbo.usp_ShipOrder definition if application tests identify an atomicity regression; retain immediate commit and remove any nonessential work from the transaction.

    Confidence: High for reducing lock duration; medium for eliminating all downstream blocking because session 102 remains the root cause.

    Expected blocking effect: Reduces the duration of the Customers key X lock and the dependent waits for sessions 101, 104, 105, and 106. No percentage estimate is given because normal execution duration is not supplied.

  3. 3. Enable RCSI after application compatibility testing

    Fix: Enable read-committed snapshot isolation for ShopDemo after testing code that depends on locking read-committed semantics. RCSI causes ordinary read-committed readers to use the last committed row version rather than waiting for compatible committed data.

    Rationale: RCSI is OFF. Sessions 101 and 106 issue single-row Customer reads and wait with LCK_M_S on a Customers key held X by session 103. RCSI directly addresses this reader-writer conflict. Snapshot isolation itself is also OFF, and the version store is currently 0.0 MB.

    Risk: Medium. RCSI changes read consistency from lock-based read committed to statement-level versioned read committed and introduces version-store maintenance.

    Rollback: The setting can be returned to OFF after active transactions drain and after application testing confirms the change is safe. Monitor the database version store after enabling.

    Confidence: High for the two Customer reader waits; medium for the Orders OBJECT IS wait because the writer-side transaction and object lock still need correction.

    Expected blocking effect: Removes compatible reader waits such as sessions 101 and 106 waiting for session 103's Customer key X lock. It does not remove writer-writer conflicts, schema locks, or the root transaction's X lock.

  4. 4. Enable ADR and optimized locking in a maintenance window

    Fix: On this SQL Server 2025 Enterprise engine, enable ADR first and optimized locking second. Optimized locking holds a transaction ID lock to the end of the transaction and releases row and page X locks as rows are modified, making lock escalation less likely. Its lock-after-qualification behavior can avoid U-lock acquisition for qualifying DML when RCSI is ON.

    Rationale: DatabaseSettings reports ADR OFF and optimized locking OFF. Lock Inventory reports a granted OBJECT X lock for session 102, and session 103 holds transaction-scoped DML locks. The database uses TABLE lock escalation. These are evidence-supported conditions where optimized locking can reduce row/page lock retention and escalation pressure, but it cannot repair a transaction that deliberately waits for 15 minutes.

    Important limitation: ADR must be enabled before optimized locking. Each statement needs no other active connections to the database and may wait for connections to disconnect, so run it in a maintenance window. ADR stores row versions, including RCSI and snapshot versions, in the persistent version store inside the database rather than TempDB; monitor database storage after enabling. Optimized locking does not prevent deadlocks in general.

    Risk: Medium to high. ADR changes recovery and version-store behavior; optimized locking changes lock acquisition and retention behavior.

    Rollback: The documented order is optimized locking OFF first, followed by ADR OFF. Validate application behavior and database growth before considering rollback.

    Confidence: Medium. The lock inventory supports applicability, but the snapshot does not prove that the session 102 OBJECT X lock resulted from escalation.

    Expected blocking effect: May reduce OBJECT/page/row lock amplification and some DML-to-DML conflicts. It will not remove the root cause of an open transaction containing WAITFOR, and it does not generally prevent deadlocks.

  5. 5. Add a covering index candidate for dbo.Orders after Index Tuning confirmation

    Fix: Evaluate a nonclustered index on dbo.Orders(CustomerId, Status) including Total, LateFee for dbo.usp_CustomerBalance. Confirm this candidate by running the Index Tuning goal for the specific schema and table before deployment.

    Rationale: BlockerPlans shows session 104 scanning the clustered Orders index, reading 200,001 rows, with 97% of statement cost in the scan, for predicates on CustomerId and Status. Query Store shows the same query with 9,111 ms total duration and 1,306 logical reads. MissingIndexes has no recommendation, so this is a manually inferred candidate and not a guaranteed optimizer recommendation.

    Estimated size: The input reports Orders at approximately 200,001 rows and the clustered index at 10.2 MB. Column widths, nullability, existing clustering-key width, compression, and fill factor are not supplied. Estimated new-index size is approximately 2–8 MB, based on 200,001 rows, a two-column key, two included columns, the clustering key, and normal row/page overhead. This is a range estimate, not a measured size.

    Risk: Medium. The index adds write maintenance to INSERT and UPDATE operations, including the high-contention Orders paths.

    Rollback: Drop only the newly created named index after confirming no other workload depends on it; the expected reclaimed storage is approximately 2–8 MB.

    Confidence: Medium.

    Expected blocking effect: Reduces the scan duration and logical work for session 104, which can shorten its participation in the Orders OBJECT wait. It does not remove session 102's X lock or session 103's Customer key X lock. No percentage estimate is provided because actual selectivity and execution frequency are not supplied.

    Net storage change if implemented: Approximately +2–8 MB, using the estimate above; no existing index is dropped or modified.

  6. 6. Enable asynchronous statistics updates and low-priority waiting

    Fix: Because AutoUpdateStats is ON while AutoUpdateStatsAsync and ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY are OFF, enable both settings. This is a secondary mitigation for synchronous statistics-refresh compilation waits, not the primary fix for the current lock chain.

    Rationale: Query Store contains statistics-manipulation queries for Orders, including StatMan queries with 1,128 logical reads. The supplied active blockers are not compile-lock waits, but asynchronous statistics updates reduce the chance that a synchronous refresh prolongs compilation and causes other callers to queue.

    Risk: Low to medium. Statistics may remain temporarily stale while asynchronous updates complete.

    Rollback: Return the two settings to OFF if workload testing shows unacceptable plan-quality or statistics-freshness effects.

    Confidence: Medium for future compile-wait reduction; low as a fix for the current blocking event.

    Expected blocking effect: No direct removal of the current LCK_M_S, LCK_M_IX, or LCK_M_IS waits is claimed. The expected benefit is reduced duration of future synchronous statistics refresh and compile-related waits.

  7. 7. Correct the recurring SalesDemo deadlock resource order

    Fix: The deadlock history is for SalesDemo rather than ShopDemo, but it shows a recurring application pattern: usp_PayOrder and usp_RefundOrder acquire Accounts and Orders resources in opposite orders. Standardize both procedures on one resource acquisition order and keep the transaction short. Add retry handling for deadlock victims.

    Rationale: DeadlockHistory reports ten captured deadlocks. Repeated graphs show session 83/70 holding X on Accounts while waiting for Orders, while session 84/77 holds X on Orders while waiting for Accounts. Other graphs show usp_PayOrder waiting for S on an Orders page while usp_RefundOrder waits for X on an Accounts key.

    Risk: Medium. Procedure ordering changes can affect transaction behavior and application retry requirements.

    Rollback: Restore the prior procedure implementations if functional tests fail, while retaining deadlock retry handling.

    Confidence: High for the recurring cross-resource cycle in SalesDemo; this is not evidence of the current ShopDemo blocking chain.

    Expected blocking effect: Reduces the documented Accounts-versus-Orders deadlock cycle in SalesDemo. It does not directly alter the ShopDemo session 102/103 chain.

Deadlock capture status: CAPTURED. The system_health session is running, so no recommendation to enable or restart deadlock capture is made. The history is short rolling and shows recurring deadlocks over the supplied period; if this recurrence continues and older events are being lost, a dedicated Extended Events event-file session with longer retention is appropriate.

Deadlock history and long-term mitigation

Recurring SalesDemo deadlock cycle PayOrder holds an Accounts key and waits for Orders. RefundOrder holds an Orders key or page and waits for Accounts. The cycle leads to a deadlock victim. usp_PayOrder holds Accounts X wants Orders usp_RefundOrder holds Orders X/IX wants Accounts waits for Orders waits for Accounts deadlock cycle
Figure 2. Repeated SalesDemo deadlock pattern from Extended Events. The diagram is separate from the current ShopDemo blocking tree.

The deadlock capture is working. The repeated timestamps and repeated procedure/resource pattern justify standardizing resource order and adding bounded retry handling in the application. Optimized locking should not be described as a general deadlock-prevention mechanism; it may avoid certain lock patterns, but the application-level ordering defect remains.

Scripts

1. Enable ADR and optimized locking for ShopDemo

USE [ShopDemo];
GO

ALTER DATABASE [ShopDemo]
SET ACCELERATED_DATABASE_RECOVERY = ON;
GO

ALTER DATABASE [ShopDemo]
SET OPTIMIZED_LOCKING = ON;
GO

Run only in a maintenance window. ADR must be enabled before optimized locking. The database must have no other active connections for each statement; the statements may wait until those connections disconnect.

2. Enable read-committed snapshot isolation for ShopDemo

USE [ShopDemo];
GO

ALTER DATABASE [ShopDemo]
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;
GO

Test application semantics before production use. The rollback-immediate option terminates active transactions while changing the database option and therefore requires an approved maintenance window.

3. Enable asynchronous statistics updates and low-priority asynchronous statistics waits

USE [ShopDemo];
GO

ALTER DATABASE [ShopDemo]
SET AUTO_UPDATE_STATISTICS_ASYNC ON;
GO

ALTER DATABASE SCOPED CONFIGURATION
SET ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = ON;
GO

4. Create the candidate covering index for the CustomerBalance query

USE [ShopDemo];
GO

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Orders')
      AND name = N'IX_Orders_CustomerId_Status_CoveringBalance'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Orders_CustomerId_Status_CoveringBalance]
    ON [dbo].[Orders] ([CustomerId], [Status])
    INCLUDE ([Total], [LateFee])
    WITH (ONLINE = ON);
END;
GO

Deploy only after confirming the candidate with the Index Tuning goal for dbo.Orders. Estimated size is approximately 2–8 MB for approximately 200,001 rows because exact column widths and compression details are not supplied.

Evidence limitations

  • The blocking snapshot is a point-in-time observation. It proves the reported waits existed at capture time but does not establish daily frequency.
  • The batch context for dbo.usp_ShipOrder and other modules is truncated. No unobserved procedure code or business rule has been invented.
  • MissingIndexes reports no candidates. The Orders covering index is therefore a reasoned candidate based on the supplied plan and should be validated before deployment.
  • The OBJECT resource for several waits has no object name in Lock Inventory. The report does not assert that every OBJECT-level wait is a confirmed lock escalation.
  • Deadlock history concerns SalesDemo, while the active blocking chain concerns ShopDemo. The historical deadlock recommendations are explicitly scoped to SalesDemo and are not presented as the cause of the ShopDemo event.
  • Query Store is available because Query Store queries and statistics appear in the input. No recommendation to enable Query Store is required for this report.