Using SQL Server Query Store to Catch and Fix Query Regressions After an Upgrade

Problem: Sudden slowdown after an application upgrade

You roll out a new version of your application, and within minutes users report slower response times. The deployment didn’t change any database schema, but some queries are now taking far longer than before. Identifying the offending statements manually is time‑consuming, especially when the workload is heterogeneous.

Thesis

SQL Server Query Store automatically records query text, execution plans, runtime statistics, and wait data for every batch that runs in a database. By comparing the captured metrics before and after a deployment you can quickly pinpoint regressed queries, see what plan changed, and force a prior good plan as a temporary mitigation while you develop a permanent fix.

Enabling and configuring Query Store

First make sure the feature is turned on and sized appropriately for your workload. Run the following as a member of the db_owner or sysadmin role in the target database:

ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON;
ALTER DATABASE [YourDatabase] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, 
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), 
    DATA_FLUSH_INTERVAL_SECONDS = 900, 
    MAX_SIZE_MB = 500);

Adjust MAX_SIZE_MB based on your expected query volume; a busy OLTP system may need several hundred megabytes to avoid automatic cleanup of recent data. Verify that Query Store is active:

SELECT is_query_store_on FROM sys.database_scoped_configurations WHERE name = 'QUERY_STORE';

The result should be 1. You can also inspect the current settings via sys.database_query_store_options.

Capturing a baseline and detecting regressions

After enabling Query Store, let it run for a representative period before the upgrade (e.g., one hour of peak traffic). This becomes your baseline. After the deployment, Query Store continues to collect data.

To find statements that have regressed, query the built‑in view sys.query_store_regressed_queries (available starting with SQL Server 2016 SP1). The following example shows the top 10 regressed queries by average duration:

SELECT TOP 10
    q.query_sql_text,
    rs.avg_duration AS avg_duration_after,
    rs_prev.avg_duration AS avg_duration_before,
    (rs.avg_duration - rs_prev.avg_duration) AS duration_increase_ms
FROM sys.query_store_regressed_queries AS rq
JOIN sys.query_store_query AS q ON rq.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rq.runtime_stats_id = rs.runtime_stats_id
JOIN sys.query_store_runtime_stats AS rs_prev ON rq.previous_runtime_stats_id = rs_prev.runtime_stats_id
ORDER BY duration_increase_ms DESC;

If the view returns rows, you have concrete evidence of regressions. You can also open the “Regressed Queries” report in SQL Server Management Studio (Object Explorer → Database → Query Store → Regressed Queries) for a graphical view.

Forcing a known‑good plan as a temporary fix

Once you have identified a problematic query, examine the plan changes that caused the regression. The view sys.query_store_plan shows each plan captured for a query, and the column is_forced_plan indicates whether a plan is currently forced.

Suppose the query ID is 42 and plan ID 7 is the good plan you want to enforce. Execute:

EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 7;

After forcing, verify the change:

SELECT is_forced_plan FROM sys.query_store_plan WHERE query_id = 42 AND plan_id = 7;

The result should be 1. Subsequent executions of the query will now use the forced plan unless you later unforce it (sp_query_store_force_plan @query_id = 42, @plan_id = 7, @force_or_unforce = 0).

Trade‑offs and limitations

While Query Store is powerful, it is not a free lunch:

• Storage overhead: Every captured query consumes space in the database. If MAX_SIZE_MB is too low, older data is purged automatically, potentially removing the baseline you need. Monitor sys.database_query_store_options and the query_store_size_in_megabytes column in sys.database_files.
• Plan forcing is a band‑aid: Forcing a plan hides the underlying cause (e.g., missing indexes, outdated statistics). Treat it as a temporary mitigation while you investigate and apply a proper fix.
• Minimal performance impact: The asynchronous write‑ahead logging adds a small CPU and I/O cost. In most OLTP workloads this is negligible, but validate on a staging replica if you run at extreme scale.

Actionable closing

1. Enable Query Store on all production databases that undergo frequent releases. 2. Establish a routine baseline capture window (e.g., nightly) and retain it for at least the duration of your release cycle. 3. After each deployment, run the regressed‑query query or open the built‑in report to spot degradations. 4. For any high‑impact regression, force the last known‑good plan to restore performance immediately. 5. Follow up with root‑cause analysis (indexes, statistics, query rewrite) and remove the force once the fix is verified.

By integrating Query Store into your release validation process you turn opaque slowdowns into diagnosable, actionable events—keeping both your users and your DBAs happy.