SQL Performance Tuning – 915x faster!

Real sample

The query plan below was extracted from a  stored procedure that was found during database Assessments.

It was causing a high number of deadlocks during peak hours in the client’s server.

The query plan

Figure 1- Store procedure’s query plan before tuning.

In query plans where an Eager Index Spool is directly after a data access operator, a missing index should be missing index suggestion, but it is not reported as such from SQL Server.

In this case, the eager Index Spools is expensive compared to the overall plan. It caches the entire result set in tempdb and index it. This makes them more prone to spill to disk and sometimes block operations while the data is spooled and indexed.

What was done

Added a new index to reduce reads.

It replaces the index scan and the Eager Index Spool with an Index seek operator (as you can see below):

Figure 2-Store procedure’s query plan after tuning.

The results

Sometimes changes can make TSQL run 20,000% faster, sometimes 50%, other times slicing 20% off the cost is a great help.

Tuning results vary. They vary based on the query’s complexity, how much tuning has gone into it already, etc.

But we rarely can’t make T-SQL perform faster.

 

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.