SQL Server Performance Tuning

How We Cut a Query's Disk Reads by 896 Times

Updated May 12, 20262 min read

A query’s disk reads, cut 896x. Ploom flips through a giant book page by page, next to the same book with index tabs.

Improvement after tuning

896xDISK

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.

BeforeAfterImprovement
Disk (number of read operations)*16,650,00018,589896x
*The numbers are an average of multiple T-SQL runs.

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.

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜