[Case Study] We Tuned SQL Server Performance and now TSQL runs 24,000 TIMES faster(not a typo)! How did we do it?

Real sample

Here what can be done during a session of SQL performance tuning.

This query was running for a long time, causing a blocking chain and High CPU usage.

It was also preventing other sessions and the index maintenance job from completing.

Test environment

All the tests were executed on the DEV server against a database restored from the PROD server.

The query was executed a few times and tested possible solutions to improve the performance and fix the query issue.


After many tests, it was clear that the problem was a NOT IN operation calling a view without a where clause, which causes a massive number of reads.

Original query

Figure 1- Original query that was causing some issues.

Query modified

The NOT IN operation for a NOT EXISTS operation was changed as per the query below (See the highlights).

Figure 2- Query after modifications.

Comparing the results

As expected, the same output (result set) was presented by the original and modified query.

Small changes can lead to big improvements!

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!

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 *