SQL Server Performance Tuning

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

Updated October 29, 20253 min read

A 7-hour query in 1.4 seconds: one red arrow cutting straight through a maze

Improvement after tuning

17,643xDURATION
4,266xCPU
2,091xDISK

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:

Comparing the original and fixed queries

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:

Comparison of reads per table before/after tuning

Duration (ms)

24,735,261
Before tuning
1,402
After tuning

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

BeforeAfterImprovement
Duration (ms)*24,735,2611,402~17,643 (or 1,764,284%)
CPU (ms)*24,662,5785,781~4,266 (or 426,614%)
Disk (number of reads)*1,001,538,390478,903~2,091 (or 209,132%)
*The numbers are an average of multiple T-SQL runs

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

Disk (number of read operations)

3,291,001,538,390
Before tuning
479,903
After tuning

Note: The performance difference between NOT IN and NOT EXISTS 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 possible solutions were tested to improve performance and fix the query issue.

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜