How We Made T-SQL Queries 678x Faster

Improvement after tuning

108xDURATION

109xCPU

462xDISK

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 an excessive amount of disk reads, as shown in the chart below:

Pre-tuning metrics (average of 5 separate runs)

31,025

Duration (ms)

30,659

CPU (ms)

7,929,279

Disk (number of reads)

Solution

Here's what we did to fix the issue:

Split a subquery into four parts using UNION
Replaced ISNULL function in WHERE clause

That’s it. That simple.

Before vs. After

The improvement was huge: average duration dropped from 31,025 ms to just 287 ms!

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

We took 5 separate runs to analyze query performance before and after the fix:

	Duration (ms)		CPU (ms)		Disk (8k page reads)
	Before	After	Before	After	Before	After
Run 1	74,237	480	73,328	469	8,189,920	28,724
Run 2	21,592	306	21,422	297	7,877,023	19,023
Run 3	18,815	160	18,625	156	7,851,209	9,542
Run 4	22,214	327	21,906	328	7,877,048	19,023
Run 5	18,268	165	18,016	156	7,851,196	9,539
AVG	31,025	287	30,659	281	7,929,279	17,170

We managed to significantly reduce the total number of disk reads:

Disk, number of reads(average)

7,929,279

Before tuning

17,170

After tuning

	Before	After	Improvement (%)	Improvement (x)
Duration, ms*	31,025	287	10,810	108
CPU, ms*	30,659	281	10,911	109
Disk, number of reads*	7,929,279	17,170	46,181	462
Overall improvement			67,901%	679x

After our tuning, the stored procedure became many times faster, showing an overall improvement of 67,901%!
That’s 678x faster!

Why does disk improvement matter for stored procedure speed?

It's straightforward: the less often you access the disk, the more disk capacity remains available.

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.

During this tuning, we achieved improved performance by dividing a subquery into four distinct parts through UNION and substituting the ISNULL function in the WHERE clause.

As a result - 678 times faster query!

How We Made T-SQL Queries Run 678 Times Faster