[case study] SQL performance tuning report and how we made TSQL run 13 times faster! How did we do it?

Today I am sharing another MS SQL Performance tuning report from recent tuning.

Improvements after Red9 SQL Server tuning 

After making a couple of small tweaks to the SQL Server stored procedure we were able to get few improvements

  1. Run time duration improved by: 638%
  2. CPU improved by: 378%
  3. Disk improved by: 312%

Overall stored procedure improvement: 13x faster!

The green block above shows the SQL Server Stored Procedure performance AFTER tuning.

What changes made this query run faster?

  • Removed SQL function from where clause (subquery);
  • Removed unused columns from a temporary tables;
  • Replaced two temporary tables with CTE;

Why should you avoid SQL Server functions in the WHERE clause for Performance?

When  functions are used in the WHERE clause this forces SQL Server to do a table scan or index scan  instead of doing an index seek if there is an index that can be used.

Questions? Comments? Write below.

See more SQL Server Performance Optimization examples here.

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

Picture of Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

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