Improvement after tuning
Reading from disk is the slowest operation SQL Server does.
Therefore, tuning for less disk “reads” is often the primary goal.
Problem
16.65 million page reads per run. We found this stored procedure during a database assessment, and it was causing a high number of deadlocks during peak hours.
Its query plan had an Eager Index Spool right after a data access operator. That means the database was building a temporary index in tempdb on every run, because the index it needed did not exist.
An eager spool like this is a missing index that the database does not report as one. It is expensive to build, and the query waits for the whole spool before it returns a row.
Here’s a glance at the pre-tuning metrics:
- Disk (number of read operations): 16,650,000
Solution
This is what we did to fix the issue: we added the index the plan was trying to build for itself.
The index scan and the Eager Index Spool were replaced by a single index seek.
Before vs. After
The number of disk reads dropped from 16,650,000 to 18,589.
| Before | After | Improvement | |
|---|---|---|---|
| Disk (number of read operations)* | 16,650,000 | 18,589 | 896x |
Reads fell 896x.
Final thoughts
Read the query plan as well as the missing index list. The database will not always tell you an index is missing. Sometimes it quietly builds one on every execution.
So if you see an Eager Index Spool next to a scan in a plan that runs often, treat it as an index request.
One index removed the spool and the scan, so the procedure holds far fewer locks during peak hours.



