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