Case study – Dealing with deadlocks

Problem Summary

Two procedures were involved in 80% of deadlocks related to a third-party app.

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.

What was done

To improve the stored procedure’s performance and solve the deadlocks between them, we created 2 new indexes and 2 news statistics.

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.

Technical background

Indexes operate “behind the scenes”: adding and removing indexes rarely changes an application’s code.

The SQL Server engine uses an index, as a reader uses a book index. Without it, the reader needs to look at every page in a book to find a word.

Indexes are the method by which SQL Server sorts and organizes the rows in tables. It allows you to quickly narrow in on the rows to satisfy the query and avoid scanning every row in the table.

In database terms, a table scan (or just scan) happens when there is no index available to help a query. They have a terrific impact on performance on large tables, but sometimes are inevitable.

The proper index can drastically increase the performance of an application. However, there are tradeoffs to almost any feature, and indexes are no exception.

Learn how to improve your SQL Server performance using Indexes

Check out more case studies here!

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 *