Fixing deadlocks on hot SQL Server tables with an index redesign
A production incident, a deadlock graph, and the composite and covering indexes that made it stop.
The incident
Orders stalled during a peak hour. The deadlock graph pointed at the most-queried order table, where two access patterns fought over the same pages.
The diagnosis
- Queries filtered by tenant and store, then by order ID when present.
- The existing index led with the order ID, so tenant-and-store queries scanned far more than they needed.
- Scans held locks longer, and longer locks met each other.
The fix
- A composite index leading with tenant ID and store ID, matching the common predicate order.
- Covering columns for the fields the hot queries read, so they never touch the base table.
- A separate narrow index for lookups by order ID.
The result
Fewer scans, shorter locks, and the deadlock did not come back.
Takeaway
Match index column order to how the queries actually filter. The query plan tells you; the schema does not.