AI SQL Tuner Recommendations

Conclusion: blocking is active.

• BlockingChains: session 62 blocks session 84 on LCK_M_X for 25,118ms.
• BlockingChains: session 62 blocks session 69 on LCK_M_X for 23,127ms.
• BlockingChains: session 69 blocks session 62 on LCK_M_S for 1,403ms, showing bidirectional contention / deadlock-prone behavior.
• LockInventory: waiting locks are on PAGE resources for tpch10.orders, index o_orderdate_ind, with session 62 waiting on S and session 69 waiting on X.
• Long-Running Transactions: user transactions for sessions 69 and 84 have been open for 28-29 seconds.

Observed blocking tree:

Session 62 - MERGE in trigger dbo.trg_orders_UpdateCustomerStats
  Statement:
  WITH ChangedCustomers AS (...)
  MERGE dbo.CustomerOrderStats AS tgt
  USING (
      SELECT o.o_custkey, COUNT(*), MAX(o.o_orderdate)
      FROM dbo.orders AS o
      JOIN ChangedCustomers AS c ON c.o_custkey = o.o_custkey
      GROUP BY o.o_custkey
  ) AS src ...
  ├─ blocks Session 84 - UPDATE dbo.orders ... WHERE o_orderdate BETWEEN @FromDate AND @ToDate
  └─ blocks Session 69 - UPDATE dbo.orders ... WHERE o_orderdate BETWEEN @FromDate AND @ToDate

Session 69 - UPDATE dbo.orders ... WHERE o_orderdate BETWEEN @FromDate AND @ToDate
  └─ blocks Session 62 - trigger MERGE waiting for S lock on PAGE in orders.o_orderdate_ind

• Lead blocker by fan-out: session 62.
• Mutual blocker in cycle: session 69.
• Most important waiter statement: session 84 running UPDATE o SET o.o_comment = LEFT(CONCAT(...),79) FROM dbo.orders AS o WHERE o.o_orderdate BETWEEN @FromDate AND @ToDate.

• High Confidence Hotspot table or index — concentrated contention on orders / o_orderdate_ind.
  • Evidence: LockInventory repeatedly shows page locks on orders, index o_orderdate_ind, with waiting S and X requests and tens of thousands of granted X locks.
  • Evidence: Both the UPDATE and the trigger MERGE touch dbo.orders in the same transaction window.
• High Confidence Long-running transaction — the user transaction stays open while trigger work executes.
  • Evidence: Long-Running Transactions shows session 69 at 29s and session 84 at 28s.
  • Evidence: Waiter batch context explicitly includes BEGIN TRAN in dbo.usp_UpdateOrdersAndLineitem.
• High Confidence Missing index causing lock amplification — no DMV recommendation appeared, but workload evidence strongly suggests a missing support path for the trigger aggregation by customer.
  • Evidence: Trigger statement joins ChangedCustomers to dbo.orders on o_custkey and aggregates COUNT(*), MAX(o_orderdate).
  • Evidence: IndexUsage shows orders.o_orderdate_ind exists, but no evidence of an efficient customer-based access path; Query Store for query 480 shows 12,966,375 and 4,862,472 logical reads for the trigger MERGE plans.
• Medium Confidence Lock escalation risk — not explicitly observed as a granted table X lock in the snapshot, but escalation settings and page-lock volume materially increase risk.
  • Evidence: Database Settings shows orders lock escalation = TABLE.
  • Evidence: LockInventory shows session 69 with 10,771 X page locks and session 62 with thousands more on the same index.
• Medium Confidence TempDB contention masquerading as blocking — not the primary issue.
  • Evidence against primary TempDB cause: blocking waits are LCK_M_X and LCK_M_S on orders.o_orderdate_ind.
  • Evidence of secondary contribution: TempDB shows heavy spill activity, including 75,221 spills for related MERGE/UPDATE queries, which can prolong transaction duration and therefore amplify blocking.
• Low Confidence Parameter sniffing causing large scans — possible but not provable from provided data because blocker plan XML is unavailable.

1) Rewrite the orders update into key-based chunks

Before

BEGIN TRAN;

UPDATE o
SET o.o_comment = LEFT(CONCAT(
    o.o_comment,
    ' | BATCH_UPDATE ',
    CONVERT(varchar(10), @FromDate, 120),
    '-',
    CONVERT(varchar(10), @ToDate, 120)
), 79)
FROM dbo.orders AS o
WHERE o.o_orderdate BETWEEN @FromDate AND @ToDate;

-- more work here...

COMMIT;

After

DECLARE @BatchSize int = 5000;

WHILE 1 = 1
BEGIN
    ;WITH c AS
    (
        SELECT TOP (@BatchSize) o.o_orderkey
        FROM dbo.orders AS o
        WHERE o.o_orderdate BETWEEN @FromDate AND @ToDate
          AND o.ProcessedFlag = 0   -- example batching predicate
        ORDER BY o.o_orderkey
    )
    BEGIN TRAN;

    UPDATE o
    SET o.o_comment = LEFT(CONCAT(
            o.o_comment,
            ' | BATCH_UPDATE ',
            CONVERT(varchar(10), @FromDate, 120),
            '-',
            CONVERT(varchar(10), @ToDate, 120)
        ), 79),
        o.ProcessedFlag = 1
    FROM dbo.orders AS o
    JOIN c ON c.o_orderkey = o.o_orderkey;

    IF @@ROWCOUNT = 0
    BEGIN
        ROLLBACK;
        BREAK;
    END

    COMMIT;
END

• Use a stable batching key such as o_orderkey.
• Commit each batch to release locks early.
• If a processing flag is not acceptable, use a temp table of target keys or a driving key-range approach.

2) Rewrite trigger maintenance to avoid rescanning orders in the same transaction

Current blocker pattern

WITH ChangedCustomers AS (
    SELECT o_custkey FROM inserted
    UNION
    SELECT o_custkey FROM deleted
)
MERGE dbo.CustomerOrderStats AS tgt
USING (
    SELECT o.o_custkey,
           COUNT(*) AS order_count,
           MAX(o.o_orderdate) AS last_orderdate
    FROM dbo.orders AS o
    JOIN ChangedCustomers AS c
      ON c.o_custkey = o.o_custkey
    GROUP BY o.o_custkey
) AS src
ON tgt.c_custkey = src.o_custkey
...

Safer pattern: stage changed customers and process after commit

CREATE TABLE dbo.CustomerOrderStatsQueue
(
    c_custkey int NOT NULL PRIMARY KEY,
    queued_at datetime2 NOT NULL DEFAULT sysdatetime()
);

-- Trigger keeps only narrow writes
INSERT INTO dbo.CustomerOrderStatsQueue(c_custkey)
SELECT DISTINCT o_custkey FROM inserted
UNION
SELECT DISTINCT o_custkey FROM deleted;

Then process asynchronously

;WITH src AS
(
    SELECT q.c_custkey,
           COUNT_BIG(*) AS order_count,
           MAX(o.o_orderdate) AS last_orderdate
    FROM dbo.CustomerOrderStatsQueue AS q
    JOIN dbo.orders AS o
      ON o.o_custkey = q.c_custkey
    GROUP BY q.c_custkey
)
UPDATE tgt
SET tgt.order_count = src.order_count,
    tgt.last_orderdate = src.last_orderdate
FROM dbo.CustomerOrderStats AS tgt
JOIN src ON src.c_custkey = tgt.c_custkey;

INSERT INTO dbo.CustomerOrderStats(c_custkey, order_count, last_orderdate)
SELECT src.c_custkey, src.order_count, src.last_orderdate
FROM src
WHERE NOT EXISTS
(
    SELECT 1
    FROM dbo.CustomerOrderStats AS tgt
    WHERE tgt.c_custkey = src.c_custkey
);

• This keeps the OLTP transaction narrow.
• It removes immediate read-after-write pressure on the hot orders index pages.

3) Support the trigger path with an index

CREATE INDEX IX_orders_custkey_orderdate
ON dbo.orders (o_custkey, o_orderdate);

• Purpose: improve customer-based aggregation and reduce broad scans/reads.
• Validate this candidate with the Index Tuning goal for dbo.orders before finalizing.

Recurring pattern is confirmed.

• Deadlock History: a deadlock occurred at 2026-05-05 00:35:47 UTC in tpch10 during the same workload window, involving PAGE wait resources and a user transaction under read committed.
• BlockingChains + LockInventory: current waits are also page-level on orders.o_orderdate_ind, so this is not an isolated one-off.
• Query Store: the top resource consumers are the same family of batch UPDATE and MERGE maintenance statements.

Long-term mitigations

• Move summary maintenance to asynchronous processing or scheduled ETL-style refresh.
• Keep maintenance operations for orders and lineitem in separate, shorter transactions when business rules permit.
• Consider application-level serialization for overlapping date windows if concurrency on the same date range is expected.
• Use retry logic for deadlock victims, but only after reducing the lock cycle root cause.
• Review whether the trigger-based design duplicates work already performed by the top Query Store MERGE maintenance statements for OrderTotals and CustomerOrderStats.

• Query Store: enabled and valuable here; it proved the recurring high-read statements and should remain on.
• Deadlock capture: already available through Extended Events / system_health; keep it enabled.
• BlockerPlans: no blocker plans found at collection time. Additional actual/estimated plans for session 62 and 69 would help confirm whether customer-based scans, memory grants, or parallel hash operations are driving the long trigger runtime.
• TempDB: current latch profile does not indicate primary TempDB allocation contention masquerading as blocking; PAGELATCH waits are small, and version store is 0.0MB. The main issue is lock contention on user table pages, with spills acting as a duration multiplier.