SQL Server Performance Tuning

How We Made T-SQL Queries 38 Times Faster

Updated October 29, 20253 min read

T-SQL queries 38x faster. Ploom rides a small motorbike through a finish-line tape.

Improvement after tuning

38xDURATION
80xCPU
2189xDISK

Duration(ms)

38,000
Before tuning
1,000
After tuning

Disk (number of reads)

687,475
Before tuning
314
After tuning

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 took 38,000 ms to execute. It times out on the application frontend. It was performing multiple scans and reading too much data.

Pre-tuning Metrics

38,000
Duration (ms)
75,000
CPU (ms)
687,475
Disk (number of reads)

Solution

The stored procedure was rewritten.

Also, we added a second SELECT with a UNION ALL, this was necessary to divide the WHERE clause selectivity.

Note: Both versions of the stored procedure deliver the same info, but the order's different.

Before launching this change, just make sure the app doesn't mind the data order. It probably doesn't, since the app sorts the data itself.

Before vs. after

The improvement was huge: Duration of the procedure dropped from 38,000 ms to just 1,000 ms!

Here is the detailed breakdown.

Statistics IO output
Table reads: before vs. after
Disk, number of reads
BeforeAfter
Run 128,924123
Run 2363,931133
Run 3294,62058
Overall amount687,475314

Disk reads took a massive dive, plummeting from 687,475 down to just 314.

That’s more than a 2,189-fold decrease!

BeforeAfterImprovement
Duration (ms)*38,0001,000~38 (or 3,800%)
CPU (ms)*75,000941~80 (or 7,970%)
Disk (number of reads)*687,475314~2,189 (or 218,941%)
*The numbers are an average of multiple T-SQL runs

Overall, we observed a staggering improvement, making the query execution 38 times faster!

Why does disk improvement matter for stored procedure speed?

It’s simple. The less you access the disk, the more disk capacity is left over.

It works just like a highway. Imagine a 3-lane highway with just 5 cars driving on it every minute.

What if you add 50 cars? The speed remains the same because 55 cars don’t overload that highway.

What if you add another 500 or 5000? Now you are starting a slowdown in traffic.

They all still get home. But not at the same speed anymore.

It's the same deal with SQL Servers. That's why it's crucial to speed-tune the most critical resources.

The fewer hits to the storage, the more capacity available.

In this tuning session, better performance was achieved by overhauling a stored procedure and adding a second SELECT with a UNION ALL to divide the WHERE clause selectivity.

As a result - 38 times faster query!

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 ➜