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
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.
| Before | After | |
|---|---|---|
| Logical reads per execution | about 350,000 | about 10,000 |
| Wait time per execution | about 0.5 s | near zero |
| CPU and memory waits per day | 4 to 6.5 minutes | under 1 minute |
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.



