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.

See more SQL Server Performance Optimization examples here.

We tune slow SQL Servers every day. For over a DECADE now. Contact us!

Mark Varnas

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

Your email address will not be published.