Finding and Fixing a Plan Regression with Query Store
A stored procedure that averaged about 4 to 5 ms per call now averages 32 ms, and its CPU per call has gone from 4 ms to over 100 ms, with no deployment. The plan cache holds only the current plan, so yesterday’s plan isn’t there to compare. Query Store keeps both, with runtime numbers for each, and it can put the old plan back.

I reproduced a regression, found it in Query Store, forced the old plan, and then let the engine do the same on its own. I also checked whether Parameter Sensitive Plan optimization on SQL Server 2025 would have prevented it. The cause here is parameter sniffing. Reading compiled and runtime parameter values from a plan is covered in Diagnosing Parameter Sniffing with AI.
The Test
I used two instances on one workstation: SQL Server 2019 (15.0.4500.2, Developer) and SQL Server 2025 (17.0.5015.4, Enterprise Developer). Each got a scratch database with a 1,000,000-row orders table. Customer 1 owns 200,000 rows. Customers 2 to 80,001 own 10 rows each. There’s a nonclustered index on customer_id, and statistics were updated with a full scan. The procedure under test:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE PROCEDURE [dbo].[get_customer_orders] ( @customer_id int ) AS BEGIN SET NOCOUNT ON; SELECT COUNT_BIG(1) AS [order_count] , SUM([o].[amount]) AS [total_amount] , MAX([o].[order_dt]) AS [last_order_dt] FROM [dbo].[orders] AS [o] WHERE [o].[customer_id] = @customer_id; END; |
Query Store options are per database, so I changed them only in the scratch database. I used a one-minute statistics interval so each phase of the test lands in its own interval. The default is 60 minutes.[1] The workload calls the procedure 1,000 times. Every 100th call is for customer 1 and the rest are for a random small customer.
Phase 1 compiles the plan for customer 2 and runs the workload. Phase 2 runs sp_recompile on the procedure, calls it once for customer 1, and runs the same workload. That stands in for a plan being evicted or invalidated in production and the next caller happening to be the large customer.
Finding It
This query compares every plan Query Store holds for the statement:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
USE [sat_qs_regression]; SELECT [q].[query_id] , [p].[plan_id] , [p].[is_forced_plan] , [p].[force_failure_count] , [p].[last_force_failure_reason_desc] , SUM([rs].[count_executions]) AS [executions] , SUM([rs].[avg_duration] * [rs].[count_executions]) / SUM([rs].[count_executions]) / 1000.0 AS [avg_duration_ms] , SUM([rs].[avg_cpu_time] * [rs].[count_executions]) / SUM([rs].[count_executions]) / 1000.0 AS [avg_cpu_ms] , SUM([rs].[avg_logical_io_reads] * [rs].[count_executions]) / SUM([rs].[count_executions]) AS [avg_logical_reads] , MAX([rs].[max_logical_io_reads]) AS [max_logical_reads] FROM [sys].[query_store_query] AS [q] INNER JOIN [sys].[query_store_plan] AS [p] ON [p].[query_id] = [q].[query_id] INNER JOIN [sys].[query_store_runtime_stats] AS [rs] ON [rs].[plan_id] = [p].[plan_id] WHERE [q].[object_id] = OBJECT_ID(N'dbo.get_customer_orders') GROUP BY [q].[query_id] , [p].[plan_id] , [p].[is_forced_plan] , [p].[force_failure_count] , [p].[last_force_failure_reason_desc]; |
The averages are weighted by count_executions. A plain AVG() of avg_duration treats a row with 2 executions the same as a row with 1,000. Durations and CPU times are in microseconds, hence the division.[2] The results, at compatibility level 150 on both instances:
| Instance | Plan | Shape | Avg duration (ms) | Avg CPU (ms) | Avg reads | Max reads (pages) |
|---|---|---|---|---|---|---|
| SQL Server 2019 | 1 | Seek and key lookup | 5.05 | 4.27 | 6,030 | 600,352 |
| SQL Server 2019 | 2 | Parallel scan | 31.26 | 132.42 | 29,957 | 29,957 |
| SQL Server 2025 | 1 | Seek and key lookup | 3.73 | 3.73 | 6,030 | 600,350 |
| SQL Server 2025 | 2 | Parallel scan | 32.64 | 114.08 | 29,957 | 29,957 |
Each plan ran 1,001 times. Plan 1 was compiled for customer 2 and plan 2 for customer 1. The plan XML in sys.query_store_plan records the compiled parameter value but no runtime values, so the runtime numbers are what you compare.
Plan 1’s average of 6,030 reads covers two groups of calls. Its small-customer calls read about 33 pages each, and its 10 calls for customer 1 read about 600,000 pages each. The max_logical_reads column shows that. The seek plan reads less in total for this mix of callers, but much more for customer 1. CPU also separated the plans more clearly than duration. The scan plan is parallel, so it used 114 to 132 ms of CPU for 31 to 33 ms of elapsed time. When I repeated phase 2 later, its elapsed time ranged from 14 to 33 seconds, because other work was running on the workstation. Average CPU stayed between 100 and 135 ms in the runs where I captured it.
A second query joins sys.query_store_runtime_stats_interval to list the per-interval rows. On SQL Server 2019, plan 2 had two rows for the same one-minute interval, with 711 and 290 executions. For the current interval, one row can hold flushed data and another in-memory data, so aggregate by plan, execution type and interval.[2]
Forcing the Good Plan
Forcing takes the query id and the plan id from the query above. It needs ALTER permission on the database.[3]
|
1 2 3 4 5 6 |
USE [sat_qs_regression]; EXEC [sys].[sp_query_store_force_plan] @query_id = 1, @plan_id = 1; /* later, to undo: */ EXEC [sys].[sp_query_store_unforce_plan] @query_id = 1, @plan_id = 1; |
I forced plan 1, then repeated the trigger from phase 2: sp_recompile, a call for customer 1, and the 1,000-call workload. The workload took 3,531 ms on SQL Server 2019 and 3,084 ms on SQL Server 2025, against 31,211 ms and 32,775 ms for the regressed phase.
The executions weren’t recorded against plan 1. Query Store recorded a new plan, plan 11 on 2019 and plan 6 on 2025, with is_forced_plan = 0. It has plan 1’s shape, a seek with a key lookup, but it was compiled for customer 1. It estimates 200,000 rows instead of 10, has a different query_plan_hash, and carries UsePlan="1" in its XML. Microsoft says forcing recompiles the query and usually produces the same plan or a similar one.[4] So after you force, check the statistics of the plan that runs, not only the plan you forced. On both instances the new plan averaged 7,674 reads per call, against 6,030 for plan 1. Customer 1 calls on the new plan read about 638,000 pages each, a bit more than under plan 1.
When Forcing Fails
To see a failure, I dropped ix_orders_customer_id while plan 1 was forced and ran the procedure twice. On both instances, plan 1 showed force_failure_count = 1 and last_force_failure_reason_desc = NO_INDEX. The two calls ran on a new scan plan, and the caller got no error. Microsoft documents this: when forcing fails, an Extended Event fires and the optimizer compiles normally.[3]
When I recreated the index with the same name, the next call used the forced shape again. force_failure_count stayed at 1 and the reason stayed at NO_INDEX, so they show the last failure, even after forcing works again. After sp_query_store_unforce_plan, both columns went back to 0 and NONE. The documentation says the count resets when a plan changes from not forced to forced.[4] On these builds it also reset on unforce.
Automatic Plan Correction
With FORCE_LAST_GOOD_PLAN on, the engine forces the last good plan when it detects a regression. It acts when the estimated CPU gain is over 10 seconds or the new plan has more errors, and then it verifies the result.[5] The gain is an estimate. Mine forced after under half a second of extra CPU on 2025 (about 2 s on 2019), and the JSON didn’t say how it got there. The feature is Enterprise only. On SQL Server 2025 that includes Enterprise Developer but not Standard Developer.[6] The SQL Server 2019 feature table also lists it under Enterprise only.[7]
I cleared Query Store, turned the option on in the scratch database, and ran both phases again:
|
1 |
ALTER DATABASE [sat_qs_regression] SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON); |
| Instance | regressedPlanExecutionCount | Reason text | Phase 2 elapsed (ms) |
|---|---|---|---|
| SQL Server 2019 | 18 | Average query CPU time changed from 3.7ms to 111.95ms | 3,846 |
| SQL Server 2025 | 5 | Average query CPU time changed from 3.47ms to 80.36ms | 3,792 |
On both instances sys.dm_db_tuning_recommendations recommended plan 1 for query 1, with the sp_query_store_force_plan call in its details JSON. sys.query_store_plan showed plan 1 with plan_forcing_type_desc = AUTO. The scan plan ran 20 times in total on 2019 and 8 times on 2025, instead of 1,001.
The state stayed at Verifying with reason LastGoodPlanForced. On SQL Server 2019 I ran another 1,000 calls and it was still Verifying afterward. The DMV isn’t persisted, and Microsoft says a plan forced automatically and not yet verified is unforced if the instance restarts.[8][5] If you want a record of what it did, copy the rows somewhere.
With the option off, the two versions behaved differently. On SQL Server 2025, the DMV showed an Active recommendation with reason AutomaticTuningOptionNotEnabled after 6 regressed executions, which is a usable alert even if you don’t want automatic forcing. On SQL Server 2019, in two separate runs, the DMV stayed empty. I don’t know why, and I tested only this one workload.
Parameter Sensitive Plan Optimization
At compatibility level 160 and higher, PSP optimization can keep several plans for one parameterized statement. A dispatcher plan sends each call to a query variant based on the estimated cardinality of the parameter value.[9] That fits this regression, so I ran the test on SQL Server 2025 at levels 160 and 170.
PSP didn’t kick in: phase 2 still took 29,208 ms at 160 and 24,850 ms at 170. An Extended Events session on parameter_sensitive_plan_optimization_skipped_reason gave the reason at level 170: SkewnessThresholdNotMet. A 200,000-row customer against 10-row customers wasn’t enough skew. Microsoft describes skewness thresholds but doesn’t give the values.[9]
So I built a second table with the same 200,000 rows for customer 1, but one row for each of 800,000 other customers. The query_with_parameter_sensitivity event reported a skewness of 200000.00 for the column, and PSP optimization was used:
| Compat level | Plans in Query Store | Phase 1 elapsed (ms) | Phase 2 elapsed (ms) |
|---|---|---|---|
| 150 | Two compiled plans, seek and scan | 3,467 | 25,427 |
| 160 | Dispatcher plus two variants | 346 | 302 |
| 170 | Dispatcher plus two variants | 304 | 310 |
At 170, the low-cardinality variant ran 1,981 times at 0.02 ms and 6 reads per call. The high-cardinality variant was the parallel scan and ran 21 times at 25.42 ms. The sp_recompile and the first call for customer 1 no longer mattered, because the dispatcher sent each call to the variant for its range. The dispatcher’s boundaries were 100 and 100,000 rows. A second run at 170, with other work running on the machine, took 315 and 1,121 ms, and the scan variant averaged 60.66 ms.
The variant queries had object_id = 0 in sys.query_store_query. Only the parent query carried the procedure’s object_id, and its dispatcher plan had no runtime statistics. So the query near the top of this post, which filters on OBJECT_ID(N'dbo.get_customer_orders'), finds no runtime numbers once PSP is in use. Join through sys.query_store_query_variant on parent_query_id instead.[9]
What I’d Take From This
Query Store kept both plans, the runtime numbers for each, and the compiled parameter value. That was enough to find the regression and force the old plan. Forcing the seek plan fixed the small-customer calls, but customer 1 still read about 640,000 pages per call. If you’re raising a compatibility level, Upgrading Database Compatibility Levels Across a Whole Instance covers capturing a Query Store baseline first, so the old plans are there to force. To browse Query Store plans in a GUI, Performance Studio: A Cross-Platform SQL Server Execution Plan Analyzer can load them directly.
Limits of this test: one workstation, other workloads running on the same machine, one synthetic table, and one query shape. I didn’t test Standard Edition, availability group secondaries, or a restart while a forced plan was being verified.
Related: Finding the Queries That Use the Most CPU, and What Each Source Misses (available 2026-11-22).
If you’ve seen automatic plan correction move a recommendation to Success, or Reverted, on a real workload, I’d like to hear how long it took. Find me on Bluesky or LinkedIn.
References
- ALTER DATABASE SET options (Transact-SQL) - Microsoft Learn. Describes INTERVAL_LENGTH_MINUTES, the aggregation window for Query Store runtime statistics, with a default of 60. ↩
- sys.query_store_runtime_stats (Transact-SQL) - Microsoft Learn. States that durations and CPU times are in microseconds, and that the current interval can have several rows per plan that must be aggregated. ↩
- sp_query_store_force_plan (Transact-SQL) - Microsoft Learn. Requires ALTER permission on the database, and says a failed forcing fires an Extended Event and the optimizer compiles normally. ↩
- sys.query_store_plan (Transact-SQL) - Microsoft Learn. Describes is_forced_plan, force_failure_count (reset when a plan becomes forced) and the NO_INDEX failure reason. ↩
- Automatic tuning - Microsoft Learn. The 10-second estimated CPU gain rule for FORCE_LAST_GOOD_PLAN, the verification step, and the unforcing of unverified plans on restart. ↩
- Editions and supported features of SQL Server 2025 - Microsoft Learn. Lists automatic tuning for Enterprise only, and maps Enterprise Developer to Enterprise features and Standard Developer to Standard features. ↩
- Editions and supported features of SQL Server 2019 - Microsoft Learn. Lists automatic tuning for Enterprise only. ↩
- sys.dm_db_tuning_recommendations (Transact-SQL) - Microsoft Learn. The recommendation states and reasons, and the note that recommendations are kept only until the engine restarts. ↩
- Parameter Sensitive Plan optimization - Microsoft Learn. Dispatcher plans, query variants, the compatibility level 160 requirement, the skipped-reason Extended Event, skewness thresholds (no values given), and sys.query_store_query_variant. ↩