Improvement after tuning
DURATION
CPU
DISK
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 aVIEW
without aWHERE
clause, which caused a massive number of reads. - Action Taken: Replaced
NOT IN
withNOT 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 to just 1,402 ms!
Below is a comparison of the number of table reads before and after tuning:

Duration (ms)
The optimized query not only retained the same output (result set) but also achieved a great performance boost:
Before | After | Improvement | |
---|---|---|---|
Duration (ms)* | 24,735,261 | 1,402 | ~17,643 (or 1,764,284%) |
CPU (ms)* | 24,662,578 | 5,781 | ~4,266 (or 426,614%) |
Disk (number of reads)* | 1,001,538,390 | 478,903 | ~2,091 (or 209,132%) |
Overall, we observed a staggering improvement, making the query execution over 17,000 times faster.
Disk (number of read operations)
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 withNOT 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.