Using Query Store in SQL Server to Detect Plan Regressions

Problem: Unexplained Query Slowdowns

When a new deployment or a code change appears to slow a critical query, the usual “look at the plan” approach is hard because you lose the historical plan context. Without a persistent baseline, you cannot prove whether a plan has regressed or if the change is due to data growth.

Thesis: Query Store Gives a Persistent, Low‑Impact Baseline

Query Store captures every query’s execution plan and runtime statistics in a durable store. By enabling it once, you can later compare current plans against past ones to spot regressions, and you can audit query performance over time without intrusive tracing.

How to Enable and Tune Query Store

1. Enable the feature on the target database:

   ALTER DATABASE YourDatabase SET QUERY_STORE = ON;
   

   Run in a query window connected to the database. Requires ALTER DATABASE permission. After execution, confirm the status:

   SELECT query_store_status
   FROM sys.databases
   WHERE name = 'YourDatabase';
   

   Expected result: ON.

2. Configure retention and storage limits to avoid disk pressure:

   ALTER DATABASE YourDatabase
   SET QUERY_STORE (
       CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
       DATA_FLUSH_INTERVAL = 60,
       MAX_STORAGE_SIZE_MB = 1024
   );
   

   Replace 30 or 1024 with values that match your environment. The STALE_QUERY_THRESHOLD_DAYS controls how long a query stays in the store; MAX_STORAGE_SIZE_MB caps the total disk usage. Check the applied settings with:

   SELECT * FROM sys.query_store_config;
   

3. Capture a test query to verify data flow:

   SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID = 1;
   

   After running, confirm the query appears in the store:

   SELECT query_id, query_text_id
   FROM sys.query_store_query
   WHERE query_text LIKE '%SalesOrderHeader%';
   

Detecting Plan Regressions

Once Query Store is populated, you can compare the current plan with the best previous plan. Use the plan_compare function to highlight differences:

-- Find the best plan for a query
WITH BestPlan AS (
    SELECT TOP 1 plan_id
    FROM sys.query_store_plan
    WHERE query_id = 1
    ORDER BY avg_cpu_time_ms ASC
)
SELECT plan_compare(
    (SELECT plan_id FROM BestPlan),
    (SELECT plan_id FROM sys.query_store_plan WHERE query_id = 1 ORDER BY plan_id DESC LIMIT 1)
) AS plan_diff;

The output is a SQL graph showing differences. If the graph shows new operators or larger cardinality estimates, you have a regression.

Trade‑offs and Limitations

• Storage consumption: By default, Query Store records all queries, which can grow to hundreds of MB on busy systems. Tighten MAX_STORAGE_SIZE_MB or use STALE_QUERY_THRESHOLD_DAYS to control growth.
• Cold queries: Only queries that hit the cache are stored. Ad‑hoc or infrequently used queries may not appear, giving an incomplete picture.
• Performance overhead: There is a modest CPU and I/O cost. Benchmark on a staging system before enabling in production.

Actionable Checklist

1. Enable Query Store on each production database.
2. Set a conservative cleanup policy and storage limit.
3. Run a known query to confirm data capture.
4. Schedule regular reviews of sys.query_store_plan for critical queries.
5. Use plan_compare after major deployments to guard against regressions.

By following these steps, you add a low‑overhead, long‑term performance monitoring layer that turns query slowdowns into actionable insights rather than blind guesses.