Real sample #15
Here is another recent SQL Server performance tuning report.
Improvements achieved
- CPU from 125 to 18
- Reads from 21,737 to 18,411
- Duration from 180 to 1,271
Before vs After
The green block below shows SQL performance numbers after we made some changes in the database to improve slow SQL calls.
What changes made this query run faster?
- Changed cursor type to fast_foward;
- Replaced temporary #table to variable @table;
- Added index IX_groupID_IsDeleted on dbo.Violations;
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 theses, 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.
See more SQL Server Performance Optimization examples here.
