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 slow stored procedure significantly hampered SQL Server performance.
Pre-tuning Metrics
Solution
So here's what we did to improve the query performance:
- Changed cursor type to
FAST_FORWARD - Replaced temporary
#tablewith variable@table - Added index
IX_groupID_IsDeletedondbo.Violations
Before vs. After
The most interesting part…
The improvement was significant: the total number of disk reads decreased to just 1,271 from 21,737, resulting in a 5x reduction in CPU time!
The green block below shows SQL performance numbers after we made some changes in the database to improve slow SQL calls.
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 | 218 | 11 | 171 | 16 | 23,265 | 34 |
| Run 2 | 71 | 18 | 79 | 15 | 17,608 | 1,073 |
| Run 3 | 301 | 4 | 110 | 16 | 23,040 | 100 |
| Run 4 | 208 | 19 | 156 | - | 23,841 | 773 |
| Run 5 | 106 | 67 | 109 | 47 | 20,935 | 4,376 |
| AVG | 180 | 23 | 125 | 24 | 21,737 | 1,271 |
Disk (average number of reads per run)
| Before | After | Improvement | |
|---|---|---|---|
| Duration (ms)* | 180 | 23 | ~8x (or 783%) |
| CPU (ms)* | 125 | 24 | ~5x (or 520%) |
| Disk (number of reads)* | 21,737 | 1,271 | ~17x (or 1,710%) |
Duration (ms)
After our tuning, the stored procedure became significantly faster, showing an overall improvement of 783%!
That’s 8x faster!
Final thoughts
If possible, avoid cursors!
I still see a lot of people using cursors when they shouldn’t.
There are very few situations where a set-based query cannot replace a cursor logic.
For these, FAST FORWARD cursor is an excellent place to start.
Cursor options like FORWARD_ONLY, STATIC, and KEYSET can all produce parallel plans and reduce performance impacts too.
