Case study – Detecting and Resolving SQL Deadlocks

Problem Summary

Deadlocks are a common problem handled by SQL Server automatically. Nevertheless, you have to make sure that you minimize deadlocks as much as possible because every rolled back transaction negatively influences your end-users.

In many cases, we can fix or reduce them using index tuning. Sometimes, we need to go deeper into every detail to find the main cause of the problem.

Resolving deadlocks

After adding new indexes to help the deadlocking problem, there was a significant decrease in deadlocks per day for this client. The average number of deadlocks was clear reduced, as shown below:

Figure 1- Total of remaining deadlocks per day after index tunings.

To deal with remaining deadlocks required DB changes and most likely app too.

The problem was split into three scenarios, as described below:

Problem # 1: clustering key issue.

  • Analysis: Table’s clustered index key is chosen badly (not numeric, not sequential).
  • Object: Estimates.dbo. insert_tblNugenRuleldentifiers
  • Solution: Need a new clustering key for this table. We recommend creating a new column as INT identity and using that as a clustered key. The primary key remains the same.
Figure 2- Bad Table’s clustered index –  Column RuleIdentifierID.

Problem internals: The clustered key isn’t sequential. When new records are inserted, records need to be moved around internally, and SQL does that without the user seeing it, but that causes more time for the operation to complete.

Problem # 2: ad-hoc UPDATE

  • Analysis: Ad-hoc UPDATE has too many conditions. This forces SQL Server to perform a table scan. This table scan leads to a deadlock when other operations are also updating the table, even when it should only affect a single row and should be fast.
  • Object: ad-hoc update on tblLineltems. (TSQL script was sent to client)
  • Solution: UPDATE seems to be generated by an object-relational mapping, which often creates inefficient SQL. Replace with stored proc.

Problem # 3: ad-hoc DELETE

  • Analysis: Ad-hoc DELETE has too many conditions, which forces SQL Server to perform a table scan. Deletion of a single row should be fast and should not cause this.
  • Object: ad-hoc delete on [tblLineltems] . (TSQL script was sent to client)
  • Solution: DELETE seems to be generated by an object-relational mapping, which often creates inefficient SQL. Replace with stored proc.


As soon as we begin providing our services, you will be able to monitor the progress in every segment influenced by our activities.

You will notice how your system performance improves. In addition, the number of critical incidents will dramatically decrease. As a result, your overall productivity and efficiency will be enhanced beyond the most optimistic expectations.

We’re confident we are the only database services, support, or consulting firm you will need to provide SQL Services to your business.

See more SQL Server Performance Optimization examples here.

We tune slow SQL Servers every day. For over a DECADE now. Contact us!

Mark Varnas

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

Your email address will not be published. Required fields are marked *