Improvement after tuning
Reading from disk is the slowest operation SQL Server does.
Therefore, tuning for less disk “reads” is often the target.
Problem
The stored procedure showed up in the top resource-consuming report. It was putting too much stress on the CPU.
Pre-tuning Metrics
Solution
To fix this issue, we added two new indexes.
Before vs. After
The improvement was significant: the total number of disk reads decreased to just 266 from 12,805, resulting in a 5x reduction in CPU time!
CPU (ms)
| Before | After | Improvement (x) | |
|---|---|---|---|
| Duration (ms)* | 142 | 32 | ~4x (or 444%) |
| CPU(ms)* | 157 | 31 | ~5x (or 444%) |
| Disk (number of reads)* | 12,805 | 266 | ~48x (or 4,814%) |
Overall, we observed a staggering improvement, making the query execution 4 times faster!
Why does disk improvement matter for stored procedure speed?
It’s simple. The less you access the disk, the more disk capacity is left over.
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 crucial to speed-tune the most critical resources.
The fewer hits to the storage, the more capacity available.
In this tuning session, better performance was achieved by overhauling a stored procedure and adding two new indexes.
As a result - 4 times faster query!
