Deadlocks after index tuning
SQL Server resolves a deadlock by killing one of the transactions.
Every rolled-back transaction is something a user has to do again.
Problem
14 deadlocks in one day. A client's application was deadlocking every day, and on the worst days more than ten times.
In many cases you can fix or reduce deadlocks with index tuning. Queries that scan hold locks on far more rows than they need, and that is where sessions collide.
Solution
This is what we did to fix the issue: we added new indexes for the queries involved in the deadlocks.
Fewer rows read means fewer locks held, and for less time. That leaves less room for two sessions to block each other.
Before vs. After
Deadlocks dropped from 7.4 a day to 2.0 a day.
Deadlock counts on business days, before and after the change:
| Days counted | Total deadlocks | Average per day | Worst day | |
|---|---|---|---|---|
| Before index tuning | 9 | 67 | 7.4 | 14 |
| After index tuning | 11 | 22 | 2.0 | 3 |
That is 73% fewer deadlocks, and the worst day went from 14 to 3.
What indexes could not fix
Two deadlocks a day is still two too many. But the rest needed database and application changes. So we documented three root causes for the client:
- A poor clustering key. One table was clustered on a key that is not numeric and not sequential. Each insert moved rows around internally. We recommended a new
INT IDENTITYcolumn as the clustered key, keeping the primary key as it is. - An ad hoc `UPDATE` with too many conditions. It forced a table scan to change a single row. It looked like it was generated by an object-relational mapper (ORM). We recommended a stored procedure.
- An ad hoc `DELETE` with the same problem. It had the same cause, and we made the same recommendation.
Another example: two procedures behind 80% of deadlocks
In separate work on a third-party application, two stored procedures were involved in 80% of the deadlocks.
We created two new indexes and two new statistics for them. On the first procedure, a clustered index scan became an index seek, and the trace showed logical reads on the main table going from 507 to 0 for the same call.
We do not have daily deadlock counts for that system, so the 73% figure above covers only the first case.
Final thoughts
Most deadlocks start as a performance problem. Queries that scan hold more locks for longer.
Index tuning is the cheapest place to start, because it needs no application changes. Here it removed about three quarters of the problem.
Fix the scans first. Then deal with what is left in the schema and the application code.



