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.

Jose

Jose

I am an Enterprise SQL Server Database Administrator with 10+ years of experience working with complex transactional environments.

Leave a Reply

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

Call Us Now

Or, Let Us Know How We Can Help

Tight On Time?

Schedule A Call: