How We Made T-SQL Query Run 17,000x Faster

Most SQL Servers bottleneck on Disk.

So, the goal is usually to get SQL Server to read less from the disk.

Problem

This query ran 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.

Here's a glance at the pre-tuning metrics:

• Duration (ms): 24,735,261
• CPU (ms): 24,662,578
• Disk (number of read operations): 1,001,538,390

Solution

• Finding: The problem was a NOT IN operation calling a VIEW without a WHERE clause, which caused a massive number of reads.
• Action Taken: Replaced NOT IN with NOT EXISTS, optimizing the query for performance.

Here is how we modified the original query to fix the problem:

Before vs. After

The transformation was nothing short of miraculous: the execution time plummeted from 24,735,261 ms tojust 1,402 ms!

Below is a comparison of the number of table reads before and after tuning:

The optimized query not only retained the same output (result set) but also achieved a great performance boost:

Overall, we observed a staggering improvement, making the query execution over 17,000 times faster.

Note: Performance of NOT IN vs. NOT EXISTS – performance-vise – could be massive. This is one of those examples.

Final thoughts

Such dramatic enhancements serve as a potent reminder of the power of SQL tuning. Even minor adjustments can unleash significant performance gains, ensuring our systems run smoother, faster, and more efficiently.

By replacing the NOT IN operation with NOT EXISTS, we can significantly cut down on disk reads and enhance the query performance.

Remember, in the realm of SQL performance tuning, the devil often lies in the details.

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.