SQL Server Performance Tuning

How We Made a Stored Procedure Almost 5 Times Faster

Updated August 14, 20263 min read

A stored procedure, almost 5x faster. Ploom pushes a tiny toy wheelbarrow toward a huge pile of bricks.

Improvement after tuning

4.7xDURATION
4.8xCPU
3.1xDISK

Reading from disk is the slowest operation SQL Server does.

Therefore, tuning for less disk “reads” is often the primary goal.

Problem

18.8 seconds per run, and almost 14 seconds of CPU each time. We found this stored procedure during a database assessment, and it was causing CPU pressure on the client's server.

Query Store pointed straight at it. It lists the queries that use the most resources, and this one was at the top by CPU time.

Here’s a glance at the pre-tuning metrics:

  • Duration (ms): 18,755
  • CPU (ms): 13,715
  • Disk (number of read operations): 998,924

Solution

This is what we did to fix the issue:

  • Replaced the table variables with temporary tables.
  • Created an indexed view and used it in place of a subquery.

Table variables have no statistics, and before SQL Server 2019 the database assumed they hold one row. Temporary tables give it real numbers to plan with.

Before vs. After

The average run went from 18.8 seconds to 4 seconds.

We ran the procedure five times before and five times after the change:

Duration before (ms)Duration after (ms)CPU before (ms)CPU after (ms)Reads beforeReads after
Run 121,7352,35715,7032,265994,668317,581
Run 217,9233,61115,3283,141998,911316,741
Run 314,5363,18512,1562,2501,004,580322,583
Run 421,4148,71514,4854,343996,106313,007
Run 518,1682,21110,9062,1411,000,358317,555
AVG18,7554,01613,7152,828998,924317,493

Here is the summary:

BeforeAfterImprovement
Duration (ms)*18,7554,0164.7x
CPU (ms)*13,7152,8284.8x
Disk (number of read operations)*998,924317,4933.1x
*The numbers are an average of multiple T-SQL runs, captured with SQL Server Profiler, Microsoft's tracing tool.

After tuning, the procedure ran 4.7 times faster and used 4.8 times less CPU.

Final thoughts

Tuning results vary a lot. Some changes make T-SQL run 200 times faster. Others take 20% off the cost. It depends on how complex the query is and how much tuning it has already had.

Here two small changes made the procedure almost five times faster and freed up a lot of CPU.

Query Store shows you where the resources go. Start with the top of that list.

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 ➜