SQL Server Performance Tuning

How We Made T-SQL Queries Run 11 Times Faster

Updated October 29, 20253 min read

T-SQL queries 11x faster. Ploom pedals a small bicycle at full speed.

Improvement after tuning

38xDURATION
9xCPU
83xDISK

Reading from disk is the slowest operation SQL Server does.

Therefore, tuning for less disk “reads” is often the main target.

Problem

A slow stored procedure heavily affected the SQL Server performance.

Pre-tuning Metrics

122,332
Duration (ms)
110,460
CPU (ms)
72,872,635
Disk (number of reads)

Solution

Here is how we fixed it:

  • To improve the stored procedure performance, we created four VIEWs.
  • Also, we made changes to the stored procedure code, removing a few tables from the EXISTS operator that were part of the LEFT JOIN.

Before vs. After

The improvement was huge: the total number of disk reads dropped from 72,872,635 to just 881,626!

That’s 83x!

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

Snapshot of SP performance before and after tuning
BeforeAfterImprovement (%)Improvement (x)
Duration (ms)*122,33210,7051,14311
CPU (ms)*110,46712,9068569
Disk (number of reads)*72,872,635881,6268,26683
*The numbers are an average of multiple T-SQL runs

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

Final thoughts

The most common bottleneck for SQL Servers is disk access (or disk "reads").

It's neither the CPU nor the RAM, which are usually the first suspects for most customers.

And that makes much sense. Here is why:

  • Inefficient queries scan (or read) much data.
  • Data read in is stored in RAM. As more data is read in, “older” data is pushed out from RAM.
  • If there isn’t enough RAM to keep ALL data in memory (which is often not possible), SQL Server has to read from disk – and that is the slowest operation SQL Server can do.
  • When the query can be tuned to read 10 rows vs. 10 million – less CPU and RAM automatically are necessary.

Disk resources are the cause of 95% of SQL Server bottlenecks, so reducing the disk reads is usually the primary objective.

For the end-user, nothing is more crucial than the speed (or duration) of the query, in every case.

Tuning to reduce CPU/RAM resources is helpful too.

When queries are tuned to need less CPU & RAM, it means that the same server now has more capacity.

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 ➜