Query Store as a Regression Safety Net for Azure SQL Database

A query that ran in 200 milliseconds yesterday suddenly takes four seconds after a deployment. The execution plan changed, statistics updated, or a parameter-sensitive code path surfaced. In Azure SQL Database, you do not have to guess which plan regressed or wait for the next maintenance window. Query Store keeps a history of query text, execution plans, and runtime statistics inside the database itself. The useful takeaway: enable it before the incident, then use plan forcing as a reversible emergency lever while you fix the underlying cause.

Query Store is not just a monitoring tool; it is a regression safety net. The right workflow is to enable it deliberately, capture enough history to compare intervals, and treat a forced plan as a temporary mitigation with an owner and an expiry date—not as the final fix.

Enable Query Store before you need it

Query Store persists query text, compiled plans, and runtime statistics so you can compare intervals instead of guessing. By default it may not be enabled everywhere, and its behavior varies by engine version and service tier, so verify current settings rather than assuming SQL Server parity.

Pick capture mode deliberately. AUTO captures all relevant queries; CUSTOM lets you narrow the scope. Because the store is size-bounded, set max_storage_size_mb and stale-query retention before an incident. If the store fills, it can stop capturing new history or enter a read-only state, which defeats the purpose during an emergency.

Run this in the target database to inspect the current state:

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

Expected check: actual_state_desc should be READ_WRITE. If it is READ_ONLY, note the readonly_reason_desc and address storage before relying on new captures.

Worked example: force a known-good plan

Suppose a stored procedure regressed after a deployment. You suspect the optimizer chose a different plan, but you need evidence. First, identify the query_id for the affected statement and the candidate plan_id values in sys.query_store_plan. Do not force the first plan you see. Compare candidates using runtime statistics.

SELECT plan_id,
       avg_duration,
       avg_cpu_time,
       avg_logical_io_reads,
       last_execution_time
FROM sys.query_store_runtime_stats
WHERE plan_id IN (SELECT plan_id
                  FROM sys.query_store_plan
                  WHERE query_id = <query_id>)
ORDER BY last_execution_time DESC;

Look for a plan with materially lower avg_duration, avg_cpu_time, and avg_logical_io_reads in earlier intervals. Confirm the old plan is still valid for the current schema and statistics. If it is, force it:

EXEC sp_query_store_force_plan @query_id = <query_id>, @plan_id = <plan_id>;

Run this in the same database with administrative privileges. The expected check is that the optimizer uses the forced plan on the next execution. Verify the flag directly:

SELECT is_forced_plan
FROM sys.query_store_plan
WHERE plan_id = <plan_id>;

Then compare sys.query_store_runtime_stats for the same query_id and plan_id across intervals to confirm the regression is contained. To roll back, unforce the plan:

EXEC sp_query_store_unforce_plan @query_id = <query_id>, @plan_id = <plan_id>;

Forcing is a lever, not a fix

A forced plan can mask parameter sniffing or stale statistics. It may also be ignored after schema, index, or statistics changes, so revalidate before trusting it. Treat every forced plan as a temporary mitigation with a named owner and an expiry date. Query Store itself adds overhead and can enter read-only mode when full; that is a reason to size it correctly, not a reason to leave it off.

Automatic tuning and plan-correction features are separate and version-sensitive. If you rely on them, check sys.database_automatic_tuning_options for desired_state_desc and actual_state_desc, and confirm Query Store is read-write first.

Make it routine

• Enable Query Store on every production Azure SQL Database.
• Set retention and storage limits before an incident, not during one.
• Add a regression check after deployments that compares runtime stats across intervals.
• Document how to unforce a plan so the mitigation does not become permanent.
• Verify the selected plan after forcing rather than assuming it took effect.

Plan forcing buys you time. The real fix is updating statistics, adding an index, or rewriting parameter-sensitive code. Query Store simply gives you the evidence and the reversible switch to keep production stable while you do that work.