SQL Performance Tuning Report – 899,253% improvement

SQL Performance Tuning Case Study… and a new big win!

Here is another recent SQL Server performance tuning report.

Improvements achieved:

  • CPU from 51,628 to 12
  • Reads from 13,730,174 to 6,682
  • Duration from 94,198 to 98

Before vs. After.

“Before” – shows numbers before we made any changes.

“After” – shows SQL performance numbers after we made some changes in the database to improve slow SQL calls.

See capture of BEFORE vs. AFTER performance tuning run:

SQL profiler trace

What changes made these queries run faster?

  1. SQL Cursor changed from Local to Fast Forward (cannot replace the SQL cursor, because this is a third-party application and we are limited on what we can do).
  2. Added a non-clustered index on table [<removed>Transaction.
  3. A subquery became a Common Table Expression (CTE).

Why did you use CTE ?

It’s simple.

You can influence SQL Server’s ordering of joins using CTEs (which were introduced in SQL Server 2005).

Common Table Expression allows a developer to break up a complex query into little parts, which can simplify the logic as well as assessment of exactly which joins dramatically affect performance.

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. Required fields are marked *