Using Query Store to Prove an Upgrade Did Not Hurt

A Query Store upgrade comparison helps you check whether important queries changed after an engine or compatibility-level move. Capture a useful baseline first, then compare similar work instead of treating a successful restore as a performance test.

Start Capturing Before the Upgrade

Query Store needs history from the workload you intend to compare. Turning it on after a regression leaves the earlier behavior missing. Configure it early enough to cover the relevant business cycle, following guidance for your SQL Server version.

SELECT
    actual_state_desc, desired_state_desc,
    current_storage_size_mb, max_storage_size_mb,
    query_capture_mode_desc, interval_length_minutes
FROM sys.database_query_store_options;

Check the actual state rather than assuming the requested state is active. Storage pressure can affect collection. Review capture policy and retention so important queries remain represented. A baseline that silently stopped collecting isn’t the baseline you thought you had.

Keep the database’s compatibility level, engine build, and relevant settings with the baseline. Annotate deployments and maintenance. Those details help distinguish an upgrade effect from another change that happened during the same week.

Move the Engine Before Changing Every Variable

Where the supported workflow allows it, upgrade the engine while keeping the existing supported compatibility level initially. Verify the application, then test the compatibility-level change as a separate step. This reduces the number of changes you must explain at once.

Compatibility level doesn’t preserve every old behavior or restore removed features. It is one control within a larger migration. Check the target release’s upgrade guidance and edition capabilities. Keep a tested recovery plan independent of Query Store.

SELECT
    SERVERPROPERTY('ProductVersion') AS engine_version,
    name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();

Compare Similar Windows

Use the Regressed Queries report in SSMS to select meaningful before and after periods. Compare a normal order-processing window with another similar window. A quiet evening and a busy morning don’t isolate an optimizer change. Check execution counts and parameter mix beside the performance metric.

Duration, CPU, and logical reads answer different questions. Review more than one when choosing a candidate. Query Store stores aggregated runtime statistics by intervals, not a complete record of every execution. Averages can hide outliers or shifts in the workload mix.

SELECT TOP (20)
    p.query_id, p.plan_id,
    SUM(rs.count_executions) AS executions,
    SUM(rs.avg_duration * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0) / 1000.0 AS weighted_duration_ms
FROM sys.query_store_plan AS p
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS i
    ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE i.start_time >= DATEADD(day, -1, SYSDATETIMEOFFSET())
  AND rs.execution_type = 0
GROUP BY p.query_id, p.plan_id
ORDER BY weighted_duration_ms DESC;

This summarizes regular executions in intervals starting during the recent day. Adjust the period for your comparison and understand interval boundaries. Use execution-weighted averages when combining intervals. Averaging interval averages directly can give a quiet interval too much influence.

Inspect the Plan Change

Select a regressed query and compare the old and new plans. Look for a plausible explanation such as changed row estimates, access methods, or joins. Check whether the query text and context still represent the same application operation. More than one plan alone doesn’t establish a regression.

Also inspect blocking and workload pressure during the slow period. A query can have the same plan and take longer because it waited. Query Store narrows the investigation, but it doesn’t replace every other diagnostic. Keep the business symptom connected to the chosen query.

Save the candidate query and plan identifiers in your review notes. Retain the comparison settings and time windows too. Another person should be able to reproduce the report rather than rely on a screenshot with hidden filters.

Use Plan Forcing as a Checked Mitigation

When an earlier plan is known to work for the relevant workload, Query Store can request that plan again. In SSMS, select the verified plan and use Force Plan. The equivalent administrative procedure is sp_query_store_force_plan with the matching query and plan identifiers.

Forcing can fail if the plan can no longer be produced. It can also succeed while remaining unsuitable for some parameter values. Monitor the result and the failure information. Don’t assume that clicking the button proves the application recovered.

SELECT
    query_id, plan_id, is_forced_plan,
    force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan
WHERE is_forced_plan = 1
   OR force_failure_count > 0;

Close the Comparison Honestly

Test the affected application path after mitigation and compare a representative workload. Record which queries and periods were covered. The title’s promise needs that scope: no captured regression isn’t proof that every possible future query will perform well.

Keep a review date for forced plans and remove temporary choices through a controlled test when appropriate. Data and engine behavior continue to change. I want Query Store to preserve evidence and support recovery, rather than accumulate unexplained permanent exceptions.

An upgrade comparison is not one fast query, it is evidence that the work you care about still behaves acceptably.

This post was rewritten from scratch in September 2026. The original, published on 2011-01-12, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.