SQL Performance Tuning Report – 3,187% gain!

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.

Agree? Disagree? Comment below.

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.