SQL Performance Tuning Report #12 – 878,750% improvement

Case Study #12… and a new big gain!

 

Pretty close to the last record, huh?

Here is another recent SQL Server performance tuning report.

Improvements achieved:

  • CPU from 258,142 to 63
  • Reads from 68,650,873 to 18,411
  • Duration from 470,993 to 490

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.

Performance Chart

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 ?

Its 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.

Stay tuned for more posts to come.
Questions? Comments? Write 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.