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 frequently executed stored procedure select_scInvoicesCredits is performing slower than expected.
Pre-tuning Metrics
Solution
To fix the issue we added additional index IX to tblRcptitem.
Before vs. After
The improvement was huge: the total number of disk reads was 10,785 instead of 118,566!
Pre-tuning Metrics
Below is the detailed breakdown by metric:
| Before | After | Improvement | |
|---|---|---|---|
| Duration (ms)* | 2,462 | 918 | ~3 ( or 268%) |
| CPU (ms)* | 2,469 | 922 | ~3 ( or 268%) |
| Disk (number of reads)* | 118,566 | 10,785 | ~11 ( or 1099%) |
After our tuning, the stored procedure became significantly faster, showing an overall improvement of 268%!
That’s almost 3x faster!
Duration (ms)
Final thoughts
Adding indexes can really speed up your SQL Server queries, but there's a catch - they might also slow things down if not used wisely.
In today's case, an additional index helped us slash the overall duration of the stored procedure by nearly 3x - an outstanding outcome!
But it’s very important to understand your overall workload and find a good balance between making your queries efficient and not paying too much for that.
