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.

We can solve your database performance problems! Contact us!

Jose

Jose

I am an Enterprise SQL Server Database Administrator with 10+ years of experience working with complex transactional environments.

Leave a Reply

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

Call Us Now

Or, Let Us Know How We Can Help

Tight On Time?

Schedule A Call: