Improvement after tuning
A query that runs all day does not need to be slow to be expensive.
Its cost is the wait per run multiplied by the number of runs.
Problem
One stored procedure spent roughly 60 minutes every day waiting on CPU and memory. It stood out in a routine review for a client whose databases we tune on a regular schedule.
It ran about 11,500 times a day. And each run read around 15,000 pages and waited about 320 ms.
Solution
This is what we did to fix the issue: we added one non-clustered index.
A single index can change performance in many places at once: other queries, other stored procedures, SQL Server Agent jobs and SSIS (Integration Services) packages that touch the same table.
Before vs. After
Reads per run dropped about 6 times, and the daily wait time went to near zero.
| Before | After | |
|---|---|---|
| Logical reads per execution | about 15,000 | about 2,500 |
| Wait time per execution | about 320 ms | near zero |
| CPU and memory waits per day | about 60 minutes | near zero |
| Executions per day | about 11,500 | about 11,500 |
Look at the last row. The procedure ran just as often after the change, so a quieter week does not explain the drop.
The workload stayed the same, reads fell about 6 times, and an hour of daily waiting went away.
Final thoughts
Small queries that run all day are where the time goes. A 320 ms wait looks harmless until you multiply it by 11,500.
When your server reads less, it has more capacity. And that can push a hardware upgrade further into the future.
Watch the queries that run most often. A small gain on each run adds up fast.



