SQL Server Performance Tuning

How We Made a T-SQL Query 45 Times Faster

Updated October 29, 20252 min read

A T-SQL query, 45x faster. Ploom skis down a steep slope with snow spraying behind him.

Improvement after tuning

45xDURATION
15xCPU
693xDISK

Reading from disk is the slowest operation SQL Server does.

Therefore, tuning for less disk “reads” is often the primary goal.

Problem

The stored procedure was too slow and performed too many disk reads.

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

  • Duration (ms): 30,682
  • CPU (ms): 18,922
  • Disk (number of read operations): 12,850,589

Solution

This is what we did to fix the issue: a SELECT with four JOINS became two SELECTs using a UNION.

That’s it. That simple!

Before vs. After

The improvement was huge: the total number of disk reads was 18,532 instead of 12,850,589!

Obviously, we can just paste the client code here. But here is what we can show you.

Below you will see a comparison of SQL procedure performance before and after tuning.

SQL procedure performance before and after tuning
Before After Improvement (%)Improvement (X)
Duration (ms)*30,6826854,47945
CPU (ms)*18,9221,2491,51515
Disk (number of read operations)*12,850,58918,53269,343693
*The numbers are an average of multiple T-SQL [FixAppliedPaymentsOnReversedTransactions] runs.

After our tuning, the stored procedure became significantly faster, showing an overall improvement of 4,479%!

That’s ~45x faster!

Final thoughts

Most SQL Server performance issues come from disk access, not the CPU or RAM as often thought.

The main problem is disk reads due to inefficient queries that fill up RAM and push the system to read from the disk - its slowest operation.

We boosted performance by turning a SELECT with four JOINS into two SELECTs with a UNION, simplifying queries to reduce system load and directly enhance speed.

This approach makes queries more efficient and decreases disk reads, thereby lightening the load on both CPU and RAM and addressing the core of 95% of bottlenecks.

Cutting down on disk reads is key because it directly improves SQL Server's main performance issue, leading to faster query times.

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 ➜