Establishing a Baseline You Can Prove Against

A tuning change without a baseline is indistinguishable from a coincidence. Servers are noisy: cache warmth, concurrent load, statistics updates and time of day all move query duration by large factors. If your only evidence is "I ran it before and after," you cannot tell your change from the weather.

What to capture before you touch anything

For the specific query you are tuning, record six numbers:

1. Average duration over a meaningful window (Query Store, not one run).
2. Average CPU time — separates work from waiting.
3. Average logical reads — the most stable metric across load conditions and the one least affected by other activity on the server. This is your primary evidence.
4. Rows returned — so you can prove the result did not change.
5. Memory grant — large grants have side effects on everything else.
6. The plan — save the .sqlplan file. When someone asks in three months what you changed, the before-and-after plans are the answer.

Logical reads deserve emphasis. Duration on a busy server can vary 5x between two identical runs. Logical reads for the same query and parameters barely move. Halving logical reads is a real, defensible improvement even if the wall clock is noisy that afternoon.

Capturing it

-- Baseline snapshot for one query_id, saved into a table you keep.
-- Create the table once:
-- CREATE TABLE dbo.TuningBaseline (
--     captured_at  datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
--     label        nvarchar(100) NOT NULL,
--     query_id     bigint NOT NULL,
--     plan_id      bigint NULL,
--     executions   bigint NULL,
--     avg_ms       decimal(18,2) NULL,
--     avg_cpu_ms   decimal(18,2) NULL,
--     avg_reads    decimal(18,2) NULL,
--     avg_rows     decimal(18,2) NULL,
--     avg_grant_kb decimal(18,2) NULL
-- );

INSERT INTO dbo.TuningBaseline (label, query_id, plan_id, executions, avg_ms, avg_cpu_ms, avg_reads, avg_rows, avg_grant_kb)
SELECT  'before-covering-index',                 -- label this change
        qsp.query_id,
        qsp.plan_id,
        SUM(rs.count_executions),
        AVG(rs.avg_duration)/1000.0,
        AVG(rs.avg_cpu_time)/1000.0,
        AVG(rs.avg_logical_io_reads),
        AVG(rs.avg_rowcount),
        AVG(rs.avg_query_max_used_memory) * 8.0  -- pages to KB
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS rsi
     ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN sys.query_store_plan AS qsp ON qsp.plan_id = rs.plan_id
WHERE qsp.query_id = 1234                        -- placeholder: your query_id
  AND rsi.start_time >= DATEADD(DAY, -7, SYSDATETIMEOFFSET())
GROUP BY qsp.query_id, qsp.plan_id;

Run the same insert with label 'after-covering-index' a few days after the change and the table tells the story on its own.

Isolating a single test run fairly

When you are iterating in a dev or test environment, the cache state dominates. Two runs of the same query — one cold, one warm — differ by an order of magnitude for reasons unrelated to your change.

-- DEVELOPMENT / TEST SERVERS ONLY. Never run these on production:
-- they discard the plan cache and buffer pool for the whole instance.
DBCC FREEPROCCACHE;      -- forces a fresh compile
CHECKPOINT;
DBCC DROPCLEANBUFFERS;   -- cold buffer pool

SET STATISTICS IO, TIME ON;
-- run the query
SET STATISTICS IO, TIME OFF;

A more targeted version that does not flush the whole instance — clear only this query's plan so you measure a fresh compile while leaving the buffer pool alone:

-- Safer: evict a single plan by handle
DECLARE @ph varbinary(64);
SELECT TOP (1) @ph = plan_handle
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE t.text LIKE '%FactSalesReport%';   -- placeholder

IF @ph IS NOT NULL DBCC FREEPROCCACHE(@ph);

Then run the query three times and take the median, not the best or the first.

Test with the real parameters

Reports behave completely differently by date range. A baseline built on "last 7 days" tells you nothing about the five-year run that users complain about. Capture both:

• A narrow range (the fast case).
• The widest range users actually select (the slow case).
• Whatever the scheduled/overnight run uses, if there is one.

If those three want different plans, you have found the parameter sniffing problem in Module 5 before writing a single index.

The discipline

One change at a time, measured. If you add an index and rewrite the WHERE clause in the same deployment and it gets faster, you have learned nothing transferable and you cannot roll back the half that was wrong. Change, measure, record, repeat.

The short version

Baseline average duration, CPU, logical reads and rows returned from Query Store over a week before you change anything, and save the plan. Logical reads is the primary metric because it is stable under load, so a 60% reduction is real evidence and not a quiet afternoon. Then make one change at a time and compare the same numbers over the same window.