Improvement after tuning
SQL Server tuning is crucial - it reduces CPU time, disk reads, and speeds up operations, making everything more efficient.
Problem
The slow stored procedure significantly hampered SQL Server performance.
Pre-tuning Metrics
Solution
Here is what we've done to make this query run faster:
- Removed SQL function from
WHEREclause (subquery) - Removed unused columns from temporary tables
- Replaced two temporary tables with Common Table Expressions (CTE)
Before vs. After
The improvement was huge: the total number of disk reads was 3,157 instead of 9,849!
Disc (number of reads)
| Duration (ms) | CPU (ms) | Disk (8k page reads) | ||||
|---|---|---|---|---|---|---|
| Before | After | Before | After | Before | After | |
| Run 1 | 805 | 476 | 500 | 453 | 5,563 | 4,471 |
| Run 2 | 807 | 3 | 46 | - | 337 | 13 |
| Run 3 | 1,171 | 4 | 125 | - | 1,034 | 14 |
| Run 4 | 3,009 | 563 | 1,766 | 516 | 10,958 | 4,612 |
| Run 5 | 3,613 | 432 | 2,688 | 390 | 31,354 | 6,678 |
| AVG | 1,881 | 295 | 1,025 | 453 | 9,849 | 3,157 |
Below is the detailed breakdown by metric:
| Before | After | Improvement | |
|---|---|---|---|
| Duration (ms)* | 1,881 | 295 | ~6 ( or 638%) |
| CPU (ms)* | 1,025 | 453 | ~2 ( or 226%) |
| Disk (number of reads)* | 9,849 | 3,157 | ~3 ( or 312%) |
After our tuning, the stored procedure became significantly faster, showing an overall improvement of 638%!
That’s over 6x faster!
Duration (ms)
SQL Server functions in the WHERE clause
For better performance, avoid using functions in the WHERE clause. Here is why:
When you use functions in the
WHEREclause, it makes SQL Server scan the whole table or index instead of quickly finding what it needs with an index seek, assuming an applicable index exists.
