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:
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.
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.
Conclusion
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.
