[Case Study] SQL Performance Tuning and how we took TSQL run from 52 minutes down to 92 milliseconds! How did we do it?

Real sample #13

Here what can be done when you have one of our DBA plans.

During monitoring session was observed this query running for long time and due to the number of
reads happening the CPU usage went to 70% during execution. There were 12 session running the query:

After review the query plan, it was clear that the problem was a NOT IN operation calling a view.

What have we done?

Something simple! It was changed a NOT IN operation for a NOT EXISTS operation as per query below.

Small changes can lead to big improvements?

Sure!  Take a look, the execution time gone from about an hour to few milliseconds.

Before tuning

The execution time was 53 minutes or 3,200,290 ms.

After tuning

The execution time was 0 seconds or 92 ms.

That’s it! That simple!

But take care, the logical operators NOT IN and NOT EXISTS behave different under certain circumstances, or performance-wise, meaning if using one operator has a performance benefit over the other.

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

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 *