SQL Performance is faster by 915 times! How did we do it?

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

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

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