SQL Server Performance Tuning

How We Made T-SQL Queries Run 17,000x Faster [Case Study]

Updated
3 min read
Written by
Mark Varnas

Improvement after tuning

17,643x

DURATION

4,266x

CPU

2,091x

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 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 to just 1,402 ms!

Below is a comparison of the number of table reads before and 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: 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.

Speak with a SQL Expert

In just 30 minutes, we will show you how we can eliminate your SQL Server headaches and provide 
operational peace of mind

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable.

Discover More

SQL Server Health Check SQL Server Migrations & Upgrades SQL Server Performance Tuning SQL Server Security SQL Server Tips

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials