SQL Server Performance Tuning

How Index Tuning Cut SQL Server Deadlocks by 73%

Updated March 20, 20253 min read

Index tuning cut deadlocks by 73%. A chest of drawers wrapped in chains stands next to one with a single small padlock.

Deadlocks after index tuning

73%FEWER DEADLOCKS
7.4PER DAY BEFORE
2.0PER DAY AFTER

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 countedTotal deadlocksAverage per dayWorst day
Before index tuning9677.414
After index tuning11222.03
Counts come from the server's deadlock monitoring.

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 IDENTITY column 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.

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜