[Case Study] Tuning SQL Performance and improving speed by 899,253% (not a typo)! How did we do it?

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:

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.