Improvement after tuning
Reading from the disk is the slowest operation in SQL Server.
Thus, optimizing for fewer disk reads is often the target.
Problem
The stored procedure was continuously involved in deadlocking due to an index scan operation.
Pre-tuning Metrics
Solution
By adding a new index, we can turn a scan operation into a seek, making it much more efficient.
Before vs. After
The improvement was huge: the total number of disk reads dropped from 313,137 to just 5,337!
That's 59x!
As you can see in the chart below, CPU time dropped even further - by almost 66 times.
Duration (ms)
Here is a more detailed breakdown:
| Before | After | Improvement (%) | Improvement (x) | |
|---|---|---|---|---|
| Duration (ms)* | 8,656 | 171 | 5,062 | 51 |
| CPU (ms)* | 60,663 | 922 | 6,580 | 66 |
| Disk, number of reads* | 313,137 | 5,337 | 5,867 | 59 |
Overall, we observed a remarkable improvement, with the query execution becoming 51 times faster!
Final thoughts
The most common bottleneck for SQL Servers is 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.
