SQL Server Performance Tuning

How One Index Cut 60 Minutes of Daily SQL Server Wait Time

Updated October 3, 20252 min read

One index cut 60 minutes of daily waits. A dripping tap has slowly filled a bucket below it to the brim.

Improvement after tuning

6xFEWER READS PER RUN
~60 minDAILY WAIT TIME REMOVED

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.

BeforeAfter
Logical reads per executionabout 15,000about 2,500
Wait time per executionabout 320 msnear zero
CPU and memory waits per dayabout 60 minutesnear zero
Executions per dayabout 11,500about 11,500
Values are approximate. They are read from the monitoring tool's daily charts for the two weeks around the change.

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.

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 ➜