Query Store plan regressions

In one line: a query that ran fine for months is suddenly slow and nothing was deployed. Its plan was recompiled at an unlucky moment. Query Store keeps every plan a query has used and what each cost, so you can prove it, force the good plan back, and then watch the forcing in case it breaks.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/query-store-regression on GitHub.

What you’ll see

  • “It was fine yesterday.” In the demo, the same call went from 51 reads to 21,915 after a recompile.
  • Two plans for one query in Query Store: one a seek, one a scan, with very different average reads. The SSMS Regressed Queries report shows exactly this comparison.

Why it happens

A plan is evicted by a restart, memory pressure, a statistics update or a recompile. The next call compiles a new plan for its parameter values. If that call is the unusual one (the customer with 300,000 orders), every ordinary call after it inherits a plan built for the unusual one.

What to do

  • Find it in Query Store: same query_id, two plan_ids, very different avg_logical_io_reads.
  • Force the good plan with sys.sp_query_store_force_plan. In the demo it survived the very recompile that caused the regression, and small customers went back to 51 reads.
  • On SQL Server 2022, try a Query Store hint first, for example OPTION (RECOMPILE) through sys.sp_query_store_set_hints. It fixes the query without changing its code, which matters when a vendor owns the code, and without forcing anything. In the demo every call got the right plan, at the cost of a compile per call.

What doesn’t help

  • Forcing and forgetting. It fails silently: after the index the forced plan used was dropped, every call still succeeded, as a scan. force_failure_count went to 1 with reason NO_INDEX, and nothing else said so.
  • Assuming forcing is free. It trades away the cases the forced plan is bad at. The big customer went from 21,939 reads to 968,510.
  • Looking for the forced plan’s plan_id in the runtime stats. Forced executions were recorded under a new plan_id with the same shape.

Check your server

-- Forced plans in this database, and whether forcing is still working.
SELECT q.query_id,
       p.plan_id,
       OBJECT_NAME(q.object_id) AS module_name,
       p.force_failure_count,
       p.last_force_failure_reason_desc,
       p.last_execution_time
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
WHERE p.is_forced_plan = 1
ORDER BY p.force_failure_count DESC;

Run it after any index change on a database that forces plans.

See it happen

The query-store-regression demo causes a regression on purpose, finds it in Query Store, forces the fix, drops the index the forced plan depends on, and on 2022 fixes the query with a hint instead. It never clears the Query Store history that was already there.

Related: parameter sniffing · slow in the app, fast in SSMS · SQL Server 2022 query processing

Measured on SQL Server 2022 CU25 (16.0.4255.1). Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights