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 ran very frequently (multiple times per minute) and consumed a lot of server resources (CPU, Disk reads and Duration).
Pre-tuning Metrics
Solution
We added certain indexes to improve the performance.
Before vs. After
There was a dramatic improvement: the overall duration of the procedure plummeted from 44,177 ms to only 1,037 ms!
Here's a comparison highlighting the gains in efficiency:
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 | 37,061 | 391 | 64,446 | 281 | 932,204 | 3,687 |
| Run 2 | 44,002 | 218 | 66,730 | 203 | 633,620 | 2,658 |
| Run 3 | 44,584 | 2,811 | 66,403 | 765 | 617,282 | 4,395 |
| Run 4 | 42,819 | 821 | 62,434 | 515 | 617,162 | 3,613 |
| Run 5 | 52,422 | 946 | 64,134 | 484 | 630,433 | 6,067 |
| AVG | 44,177 | 1,037 | 64,829 | 449 | 686,140 | 4,084 |
As you can see, we successfully cut down on the average number of disk reads:
Disk, number of reads (average)
| Before Tunning | After Tunning | Improvement (%) | Improvement (x) | |
|---|---|---|---|---|
| Duration (ms)* | 44,177 | 1,037 | 4,260 | 43 |
| CPU (ms)* | 64,829 | 449 | 14,439 | 144 |
| Disk (number of operations)* | 686,140 | 4,084 | 16,801 | 168 |
Overall, we observed a staggering improvement, making the query execution 354 times faster!
Final thoughts
Most SQL Servers bottleneck is on Disk access (or disk “reads”).
It’s not CPU or RAM – which most customers often suspect first.
And that makes much sense. Here is why:
- Inefficient queries scan (or read) much data.
- Data read in is stored in RAM. As more data is read in, “older” data is pushed out from RAM.
- If the available RAM isn't sufficient to hold all data in memory (a common scenario), SQL Server must resort to disk reads, which are the slowest operations it can perform.
- When the query can be tuned to read 10 rows vs. 10 million – less CPU and RAM automatically are necessary.
Since 95% of SQL Server bottlenecks are due to disk resources, cutting down on disk reads is the main goal.
To the end-user, nothing is more important than Speed (or Duration of the query), in any case.
Tuning to lower CPU/RAM usage is also beneficial.
When queries are tuned to need less CPU & RAM, it means that the same server now has more capacity.
