SQL Server Performance Tuning

How Rewriting a Busy Stored Procedure Made It 111x Faster

Updated October 29, 20253 min read

T-SQL queries 111x faster. Ploom plunges down the steep drop of a small roller coaster.

Improvement after tuning

111xDURATION
139xCPU
278xDISK

Reading from disk is SQL Server's slowest operation, making the reduction of disk reads a primary target for tuning.

Problem

A stored procedure that ran hundreds of times per hour was taking too long.

Pre-tuning Metrics

3,420
Duration (ms)
3,411
CPU (ms)
53,116
Disk (number of reads)

Solution

The fix involved multiple code changes in the stored procedure to reduce the number of disk reads.

Before vs. After

The transformation drastically cut execution time from 3,420 ms to about 31 ms.

Here’s the before-and-after snapshot that showcases the efficiency gains:

Query performance before vs after

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

RunDuration (ms)CPU (ms)Disk (8k page reads)
BeforeAfterBeforeAfterBeforeAfter
Run 13,833323,8443153,658231
Run 2193261883125,799162
Run 34,654244,6561565,655167
Run 43,287313,2503145,807225
Run 54,187434,156-61,039151
Run 64,367284,3751566,738214
AVG3,42030.73,41124.653,116191

We significantly lowered the total number of disk reads which also led to a decrease in CPU and Duration:

Disk, number of reads(average)

53,116
Before tuning
191
After tuning

BeforeAfterImprovement
Duration, ms*3,42030.7111x
CPU, ms*3,41124.6139x
Disk, number of reads*53,116191278x

*The numbers are an average of multiple T-SQL runs

After our tuning, the stored procedure ran 111 times faster.

CPU time dropped 139 times and disk reads dropped 278 times.

Final thoughts

The bottleneck for most 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 is one of the most common SQL Server bottlenecks, so reducing the disk reads is usually the primary objective.

For the end-user, nothing is more important 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 ➜