How We Made T-SQL Query 45 Times Faster

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.

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.