The new index feels faster, but that is not a result the team can review. Before and after numbers turn a tuning change into a test: same query, comparable windows, and measured CPU, reads, duration, and call count. The workload has to be similar enough for the comparison to mean anything.
Define the Query and the Claim
Start with the query ID and the user-visible symptom. Are you trying to reduce per-call latency, total CPU, logical reads, or a nightly job's finish time? Those goals can move differently. An index can lower reads but add write cost elsewhere. I write the claim before changing anything: this query, with these parameters, should use fewer reads and finish sooner during the normal afternoon window.
Query Store gives a useful history for statements that it captured. Verify it is on and writable, then find the exact query ID. Similar SQL text can have different IDs because of context or options. Save query text, plan IDs, and the proposed change. What result would convince you that the change helped rather than a quiet server hour?
Choose Matching Windows
Pick a before window and an after window with similar day-of-week, time-of-day, workload mix, and data volume. A Monday peak compared with a Sunday test is not a fair trial. Give Query Store enough time to collect complete runtime intervals after the change. Record the change time separately and exclude intervals that straddle it.
I also note major confounders: deployments, statistics updates, cache clearing, failover, maintenance, and unusual blocking. Those can move duration without changing the query's own work. Exact equality between windows is rare, but obvious differences should be named rather than hidden inside a percentage improvement.
Pull Before and After Numbers From Query Store
The following query aggregates runtime stats for one query ID across two complete windows. It weights averages by execution count, rather than averaging each interval equally. Query Store durations and CPU are in microseconds; logical reads are pages. Replace the dates and query ID. Use a time zone consistent with the interval timestamps on your server.
DECLARE @QueryID bigint = 0;
DECLARE @BeforeStart datetimeoffset = '2026-01-06T13:00:00+00:00';
DECLARE @BeforeEnd datetimeoffset = '2026-01-06T14:00:00+00:00';
DECLARE @AfterStart datetimeoffset = '2026-01-13T13:00:00+00:00';
DECLARE @AfterEnd datetimeoffset = '2026-01-13T14:00:00+00:00';
WITH windows AS
(
SELECT N'Before' AS period_name,
@BeforeStart AS start_at, @BeforeEnd AS end_at
UNION ALL
SELECT N'After', @AfterStart, @AfterEnd
)
SELECT w.period_name,
SUM(rs.count_executions) AS executions,
SUM(rs.avg_duration * rs.count_executions)
/ NULLIF(SUM(rs.count_executions), 0) / 1000000.0
AS average_duration_seconds,
SUM(rs.avg_cpu_time * rs.count_executions)
/ NULLIF(SUM(rs.count_executions), 0) / 1000000.0
AS average_cpu_seconds,
SUM(rs.avg_logical_io_reads * rs.count_executions)
/ NULLIF(SUM(rs.count_executions), 0)
AS average_logical_reads
FROM windows AS w
JOIN sys.query_store_runtime_stats_interval AS i
ON i.start_time >= w.start_at AND i.end_time <= w.end_at
JOIN sys.query_store_runtime_stats AS rs
ON rs.runtime_stats_interval_id = i.runtime_stats_interval_id
JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
WHERE p.query_id = @QueryID
GROUP BY w.period_name;The result has one row per window when Query Store has data. If a row is missing, do not fill it with zero. Check capture state and interval boundaries. Runtime stats can have multiple rows for one active interval; aggregating them as shown is deliberate. Save the raw intervals as well when a spike needs explanation.
Compare Per-Call and Total Work
Average duration tells you about a typical call, but it can hide a long tail. Check minimum, maximum, and standard deviation from runtime stats, or use percentiles from other monitoring if needed. Total CPU is average CPU multiplied by executions. A query that is twice as fast per call but runs at ten times the frequency can consume more total CPU after the change.
Logical reads are buffer pages read, not disk reads. Lower logical reads usually point to less data access, but they do not guarantee lower wall time when blocking or CPU dominates. I compare plan shape and row counts to explain why a metric moved. A report of 60 percent improvement without the underlying before and after values is difficult to trust.
The weighted average formula matters when Query Store intervals contain different numbers of calls. Averaging an interval with two executions and one with two thousand executions equally would distort the result. Keep the execution count in the output so the arithmetic is explainable. If outliers matter, inspect individual intervals and maximum duration instead of hiding them behind the combined average.
Check That Volume Was Similar
Execution count belongs in the same result set because it exposes workload changes. Compare executions per hour and, if possible, rows processed per execution, parameter distributions, and the number of active users. If the after window has one tenth the calls, the lower total CPU is unsurprising. If the per-call duration drops while reads stay the same, inspect waits and cache conditions.
Also check the data set. An orders table can grow or a partition can roll over between windows. If the predicate selects fewer rows after the change, some improvement is due to less requested work. Repeat a controlled parameter test when production windows cannot be closely matched. Label the controlled test separately from production evidence.
Keep Plan IDs Beside the Before and After Numbers
A tuning change can cause a new plan, but sometimes the same plan simply runs with a warmer cache. Query Store plan IDs and compile times help distinguish those situations. Record the plan before and after, then inspect the first important operator difference. If the new index is unused, do not credit it for a lower duration. Another change or workload shift is responsible.
DECLARE @QueryID bigint = 0;
SELECT plan_id, is_forced_plan, last_compile_start_time,
count_compiles, query_plan
FROM sys.query_store_plan
WHERE query_id = @QueryID
ORDER BY last_compile_start_time DESC;This is read-only. Open the plans in SSMS and verify the expected access path. Check for a forced plan before attributing a choice to the new index. A deployment can leave an old force in place and make a perfectly reasonable index look ineffective.
Report Before and After Numbers With Their Limits
A good closeout note says the query ID, change, two time windows, execution counts, per-call CPU, reads, duration, and plan IDs. It also says which workloads were not measured, such as insert cost or a month-end report. I keep the note short enough that someone will read it and detailed enough to repeat the test.
If the numbers are mixed, say so. The new index can improve one query and increase write latency. The decision belongs to the full workload. One measured improvement is evidence; a feeling is a starting hypothesis. The team should be able to see the difference in one table without guessing what was compared.
Related reading on this blog: Reading Statistics IO Data and Baselines: Knowing What Normal Looks Like.
One fast run is not proof of tuning, it is a sample to compare with a fair baseline.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
