SQL Server Performance Tuning

How One Index Cut a Stored Procedure's Disk Reads 35 Times

Updated May 28, 20252 min read

One index, 35x fewer disk reads. Ploom holds a tiny teacup under the jet of a coiled fire hose and gets drenched.

Improvement after tuning

35xFEWER READS PER RUN

Reading from disk is the slowest operation SQL Server does.

Therefore, tuning for less disk “reads” is often the primary goal.

Problem

About 350,000 pages read on every run. One stored procedure did that hundreds of times a day, with several index scans each time.

On a busy day, that added up to over 300 million page reads from one procedure. And each run waited around half a second.

We found it during continuous tuning work for this client.

Solution

This is what we did to fix the issue: we added one non-clustered index.

Before vs. After

Reads per run dropped from about 350,000 to about 10,000.

BeforeAfter
Logical reads per executionabout 350,000about 10,000
Wait time per executionabout 0.5 snear zero
CPU and memory waits per day4 to 6.5 minutesunder 1 minute
Values are approximate. They are read from the monitoring tool's daily charts for the two weeks around the change.

That is about 35 times fewer page reads on every run.

We report reads for this case because the monitoring data tracks reads and waits per run, and that is where the change shows clearly.

Final thoughts

The less a query reads, the more capacity is left for everything else. Storage works like a highway. A few cars do not slow anyone down. A few thousand do.

So when you cut reads on a procedure that runs all day, you take cars off that road for every other query on the server.

One index on one procedure removed hundreds of millions of page reads a day.

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 ➜