Improvement after tuning
Reading from disk is SQL Server's slowest operation, making the reduction of disk reads a primary target for tuning.
Problem
A stored procedure that ran hundreds of times per hour was taking too long.
Pre-tuning Metrics
Solution
The fix involved multiple code changes in the stored procedure to reduce the number of disk reads.
Before vs. After
The transformation drastically cut execution time from 3,420 ms to just 30 ms!
Here’s the before-and-after snapshot that showcases the efficiency gains:
We took 6 separate runs to analyze the query performance before and after the fix:
| Run | Duration (ms) | CPU (ms) | Disk (8k page reads) | |||
|---|---|---|---|---|---|---|
| Before | After | Before | After | Before | After | |
| Run 1 | 3,833 | 32 | 3,844 | 31 | 53,658 | 231 |
| Run 2 | 193 | 26 | 188 | 31 | 25,799 | 162 |
| Run 3 | 4,654 | 24 | 4,656 | 15 | 65,655 | 167 |
| Run 4 | 3,287 | 31 | 3,250 | 31 | 45,807 | 225 |
| Run 5 | 4,187 | 43 | 4,156 | - | 61,039 | 151 |
| Run 6 | 4,367 | 28 | 4,375 | 15 | 66,738 | 214 |
| AVG | 3,420 | 30 | 3,411 | 24,6 | 53,116 | 191 |
We significantly lowered the total number of disk reads which also led to a decrease in CPU and Duration:
Disk, number of reads(average)
| Before | After | Improvement (%) | Improvement (x) | |
|---|---|---|---|---|
| Duration, ms* | 3,420 | 30 | 11,400 | 114 |
| CPU, ms* | 3,411 | 24,6 | 13,865 | 139 |
| Disk, number of reads* | 53,116 | 191 | 27,809 | 278 |
*The numbers are an average of multiple T-SQL runs
After our tuning, the stored procedure became significantly faster, showing an overall improvement of 56,264%!
That’s 562x faster!
Final thoughts
Most SQL Servers bottleneck is on Disk access (or disk “reads”).
It's neither the CPU nor the RAM, which are usually the first suspects for most customers.
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 there isn’t enough RAM to keep ALL data in memory (which is often not possible), SQL Server has to read from disk – and that is the slowest operation SQL Server can do.
- When the query can be tuned to read 10 rows vs. 10 million – less CPU and RAM automatically are necessary.
Disk resources are the cause of 95% of SQL Server bottlenecks, so reducing the disk reads is usually the primary objective.
For the end-user, nothing is more crucial than the speed (or duration) of the query, in every case.
Tuning to reduce CPU/RAM resources is helpful too.
When queries are tuned to need less CPU & RAM, it means that the same server now has more capacity.
