AI SQL Tuner Recommendations

Executive summary

Detailed priortized recommendations

1. Rewrite conflicting procedures and scripts to acquire locks in the same object order everywhere

   Impact: Very High   Confidence: 99%

   • For the Sales deadlocks, pick one order and enforce it globally. Example: always update Sales.Orders first, then Sales.OrderLines for a given OrderID.
   • For the Application deadlock, pick one order and enforce it globally. Example: always update Application.Countries first, then Application.StateProvinces.
   • Do not allow any competing code path, stored procedure, trigger, job, or ad hoc batch to reverse that order.
   • The deadlock graphs clearly show each session owns an X lock on one table and waits for the other table, forming a direct cycle.

   Recommended direction:

   -- Standardize on this order for Sales updates:
   BEGIN TRAN;
   
   UPDATE Sales.Orders
   SET Comments = ISNULL(Comments, '') + ' [Standard]'
   WHERE OrderID = @OrderID;
   
   UPDATE Sales.OrderLines
   SET Description = SUBSTRING(ISNULL(Description, '') + ' [Standard]', 1, 100)
   WHERE OrderID = @OrderID;
   
   COMMIT;

   -- Standardize on this order for Application updates:
   BEGIN TRAN;
   
   UPDATE Application.Countries
   SET LatestRecordedPopulation = LatestRecordedPopulation + 1,
       ValidFrom = SYSUTCDATETIME()
   WHERE IsoNumericCode = @IsoNumericCode;
   
   UPDATE Application.StateProvinces
   SET LatestRecordedPopulation = LatestRecordedPopulation + 1,
       ValidFrom = SYSUTCDATETIME()
   WHERE StateProvinceCode = @StateProvinceCode;
   
   COMMIT;

2. Replace the two Sales procedures with one canonical procedure

   Impact: Very High   Confidence: 97%

   • dbo.UpdOrdersandLines and dbo.UpdLinesandOrders encode opposite lock acquisition order and are the direct source of Deadlocks #1 and #3.
   • Keep only one procedure for that business action and retire the inverse-order version.
   • If both behaviors are needed, parameterize the data changes, not the physical update order.
   • This is safer than relying on developers to remember a convention across multiple procedures.

   CREATE OR ALTER PROCEDURE dbo.UpdOrdersAndLines_Canonical
       @OrderID int
   AS
   BEGIN
       SET NOCOUNT ON;
       SET XACT_ABORT ON;
   
       BEGIN TRAN;
   
       UPDATE Sales.Orders
       SET Comments = ISNULL(Comments, '') + ' [Proc]'
       WHERE OrderID = @OrderID;
   
       UPDATE Sales.OrderLines
       SET Description = SUBSTRING(ISNULL(Description, '') + ' [Proc]', 1, 100)
       WHERE OrderID = @OrderID;
   
       COMMIT;
   END

3. For tightly related parent/child updates, lock the driving parent row first and keep that rule universal

   Impact: High   Confidence: 92%

   • For order-related work, use the order header row as the transaction anchor.
   • Acquire the header row lock first, then update child rows. This gives every session the same starting point.
   • Where appropriate, explicitly probe the parent row first to establish a deterministic lock path before touching children.

   BEGIN TRAN;
   
   SELECT 1
   FROM Sales.Orders WITH (UPDLOCK, HOLDLOCK)
   WHERE OrderID = @OrderID;
   
   UPDATE Sales.Orders
   SET Comments = ISNULL(Comments, '') + ' [Proc]'
   WHERE OrderID = @OrderID;
   
   UPDATE Sales.OrderLines
   SET Description = SUBSTRING(ISNULL(Description, '') + ' [Proc]', 1, 100)
   WHERE OrderID = @OrderID;
   
   COMMIT;

   • Use this only as part of the broader standardized order strategy. Lock hints alone do not fix inverse ordering if different sessions still lock different objects first.

4. Enable Read Committed Snapshot Isolation for the database

   Impact: Medium-High   Confidence: 83%

   • RCSI is currently disabled.
   • RCSI reduces reader/writer blocking and can prevent additional deadlock classes involving shared locks.
   • It will not directly eliminate the writer/writer deadlocks shown here, so it should be treated as a secondary improvement, not the primary fix.
   • SQL Server 2022 supports this fully; your edition has no limitation here.

   ALTER DATABASE WideWorldImporters
   SET READ_COMMITTED_SNAPSHOT ON
   WITH ROLLBACK IMMEDIATE;

5. Shorten transaction lifetime and remove nonessential work between the first and second updates

   Impact: High   Confidence: 90%

   • The deadlock victims show explicit user transactions and trancount = 2, which increases the window in which cyclic blocking can form.
   • Move all validation, variable assignment, and non-DML logic before BEGIN TRAN whenever possible.
   • Commit immediately after the final update.
   • Avoid interactive SSMS testing patterns that hold transactions open longer than application code would.

6. Add deadlock retry handling in the application or calling batch

   Impact: Medium   Confidence: 94%

   • Even after the structural fix, a small number of deadlocks can still occur in busy systems.
   • Retry only on error 1205, with a short bounded backoff and full transaction replay.
   • This is mitigation, not root-cause removal, so prioritize it below code-order fixes.

   -- Pseudocode pattern
   retry up to 3 times:
     begin tran
     execute canonical procedure
     commit
   on error 1205:
     rollback
     wait 100-300 ms with jitter
     retry

7. Keep row targeting as selective and seek-based as possible

   Impact: Medium   Confidence: 78%

   • The current deadlocks occur on primary key keylocks, which is already relatively selective.
   • Still, preserve efficient seeks on the predicate columns:
     • Sales.Orders(OrderID)
     • Sales.OrderLines(... by OrderID path ...)
     • Application.Countries(IsoNumericCode)
     • Application.StateProvinces(StateProvinceCode)
   • If any nonclustered path causes extra lookup work or broader touching of rows in the real workload, tune that access path so locks are held for less time.
   • This is lower priority because the deadlock evidence already points primarily to order inversion, not missing-index scans.

8. Avoid mixing ad hoc scripts and procedural code that touch the same tables in different orders

   Impact: Medium   Confidence: 88%

   • Deadlock #2 came from ad hoc transactional batches in SSMS.
   • Document and enforce a single transaction template for all manual maintenance and test scripts touching these objects.
   • For shared environments, place canonical write logic behind stored procedures and limit direct table updates.

9. Use application locks only if business logic cannot be refactored to a single ordering rule

   Impact: Situational   Confidence: 74%

   • If multiple complex code paths must update the same logical entity and cannot be consolidated, serialize by business key using sp_getapplock.
   • This can be effective for hot entities like a single OrderID, but it introduces deliberate serialization and should not be the first fix.

   BEGIN TRAN;
   
   EXEC sys.sp_getapplock
       @Resource = CONCAT('Order:', @OrderID),
       @LockMode = 'Exclusive',
       @LockOwner = 'Transaction',
       @LockTimeout = 10000;
   
   -- Canonical update sequence here
   
   COMMIT;

Implementation notes