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
The stored procedure was too slow and performed an excessive number of disk reads, as shown in the chart below:
Pre-tuning metrics (average of 5 separate runs)
Solution
Here's what we did to fix the issue:
- Split a subquery into four parts using
UNION - Replaced
ISNULLfunction inWHEREclause
That’s it.
Before vs. After
The improvement was huge: average duration dropped from 31,025 ms to just 287 ms!
Below you will see a comparison of SQL procedure performance before and after tuning.

We took 5 separate runs to analyze query performance before and after the fix:
| Duration (ms) | CPU (ms) | Disk (8k page reads) | ||||
|---|---|---|---|---|---|---|
| Before | After | Before | After | Before | After | |
| Run 1 | 74,237 | 480 | 73,328 | 469 | 8,189,920 | 28,724 |
| Run 2 | 21,592 | 306 | 21,422 | 297 | 7,877,023 | 19,023 |
| Run 3 | 18,815 | 160 | 18,625 | 156 | 7,851,209 | 9,542 |
| Run 4 | 22,214 | 327 | 21,906 | 328 | 7,877,048 | 19,023 |
| Run 5 | 18,268 | 165 | 18,016 | 156 | 7,851,196 | 9,539 |
| AVG | 31,025 | 287 | 30,659 | 281 | 7,929,279 | 17,170 |
We managed to significantly reduce the total number of disk reads:
Disk, number of reads(average)
| Before | After | Improvement | |
|---|---|---|---|
| Duration, ms* | 31,025 | 287 | 108x |
| CPU, ms* | 30,659 | 281 | 109x |
| Disk, number of reads* | 7,929,279 | 17,170 | 462x |
After our tuning, the stored procedure ran 108 times faster.
CPU time dropped 109 times and disk reads dropped 462 times.
Why does disk improvement matter for stored procedure speed?
It's straightforward: the less often you access the disk, the more disk capacity remains available.
It works just like a highway. Imagine a 3-lane highway with just 5 cars driving on it every minute.
What if you add 50 cars? The speed remains the same because 55 cars don’t overload that highway.
What if you add another 500 or 5000? Now you are starting a slowdown in traffic.
They all still get home. But not at the same speed anymore.
It's the same deal with SQL Servers. That's why it's important to speed-tune the most critical resources.
The fewer hits to the storage, the more capacity available.
During this tuning, we achieved improved performance by dividing a subquery into four distinct parts through
UNIONand substituting theISNULLfunction in theWHEREclause.
As a result, the query runs 108 times faster.



