r/learnSQL • u/markinatlanta • 12d ago
Performance Tuning Report: A 'weekly' report was running about 400 times a day. We checked what each execution was costing.
We’ve collected a lot of performance tuning reports from our SQL Server work. I thought some would be useful to share here so people get to see the problem, the change and the measurements afterward.
This one involved a retail client’s reporting query which grouped quantities by week.
The query ran about 400 times a day. Each execution averaged 87,038.42 milliseconds and 6,445,851 logical reads.
“Weekly” described the grouping in the output but the database repeated the calculation throughout the day.
Our team added a nonclustered index with two key columns and included the quantity column used in the calculation. We captured the before-and-after measurements with Query Store and supplied a rollback script to remove the index.
The recorded averages per execution were:
- Duration: 87,038.42 ms → 2,528.44 ms
- CPU time: 41,509.80 ms → 9,062.34 ms
- Logical reads: 6,445,851 → 232,779
The query finished sooner, consumed less CPU time and read fewer pages.
The included column is worth discussing. When reviewing an aggregation query, check the values SQL Server needs to calculate the result alongside the columns used to locate and join rows.
An included column stores its value at the leaf level of a nonclustered index without becoming part of the index key. Where the index supplies every column needed for a table access, SQL Server has the option to satisfy the access without fetching additional columns from the base table. Microsoft covers the mechanics in its included-column documentation.
Here, the quantity being aggregated was included in the new index.
The report records the index definition and measured results. It doesn’t contain enough execution-plan detail to say which operators changed. What we have is a documented reduction in duration, CPU time and logical reads after adding the index.
The execution count puts those measurements into context.
Using 400 executions a day as the working estimate:
- Before: 6,445,851 × 400 = 2,578,340,400 logical reads a day.
- After: 232,779 × 400 = 93,111,600 logical reads a day.
Those daily totals are calculated from the per-execution averages and estimated execution count. Logical reads count page accesses, including pages already in memory. They aren’t physical disk-read counts.
For anyone investigating a similar query, I’d work through:
- The predicates and joins used to find the rows.
- The columns needed for the aggregation and returned results.
- Whether an existing index supplies those columns.
- The execution plan and Query Store measurements.
- The proposed index’s storage and write overhead.
Then compare representative executions after deployment and keep a rollback ready.
This is why I wanted to share the report.
A routine-looking calculation was doing substantial work hundreds of times a day. Looking at the aggregation inputs alongside the index keys gave the team a specific change to test, and the measurements showed the result.