Improvement after tuning
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 before | Reads after | |
|---|---|---|---|---|---|---|
| Run 1 | 21,735 | 2,357 | 15,703 | 2,265 | 994,668 | 317,581 |
| Run 2 | 17,923 | 3,611 | 15,328 | 3,141 | 998,911 | 316,741 |
| Run 3 | 14,536 | 3,185 | 12,156 | 2,250 | 1,004,580 | 322,583 |
| Run 4 | 21,414 | 8,715 | 14,485 | 4,343 | 996,106 | 313,007 |
| Run 5 | 18,168 | 2,211 | 10,906 | 2,141 | 1,000,358 | 317,555 |
| AVG | 18,755 | 4,016 | 13,715 | 2,828 | 998,924 | 317,493 |
Here is the summary:
| Before | After | Improvement | |
|---|---|---|---|
| Duration (ms)* | 18,755 | 4,016 | 4.7x |
| CPU (ms)* | 13,715 | 2,828 | 4.8x |
| Disk (number of read operations)* | 998,924 | 317,493 | 3.1x |
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.



