Repository navigation
PLAN_REGRESSION compares plans compiled for different inputs, and its advice to force the "best" plan makes the slow case slower #5630
Description
Claude posting for Erik Darling.
PLAN_REGRESSION reports a regression when a query's latest plan costs 2× its cheapest plan. It does not check that the two plans served the same kind of input. Two common query types make it fire falsely. The advice then says to force the cheaper plan. In a scratch test, that force made the slow case 10.6× and 4.4× slower.
How the detector compares plans
The comparison is PlanRegressionSuffix in Darling/PerformanceMonitor.Darling.Analysis/PgFactCollector.QueryPerf.cs. Lite has the same comparison in Lite/Analysis/DuckDbFactCollector.QueryPerf.cs.
- It groups each query's Query Store rows by
query_plan_hash. A group needs 25 executions. - The "latest" group is the one with the newest
last_exec. - The "best" group is the one with the lowest CPU per execution.
- It reports a regression when the latest CPU per execution is at least 2× the best. The latest group also needs 10 CPU-seconds in the window.
No step looks at the input that each plan served. So a plan that served only small inputs can be "best". A plan that served large inputs can be "latest".
Two false-positive patterns on one production store
We reviewed the top plan-regression targets on one production store. The databases run SQL Server 2022 at compatibility level 160. The two largest targets were not plan flips. The smaller targets included real plan flips.
Pattern 1: a statement with OPTION (RECOMPILE)
- The statement is a three-way
UNIONlookup: by serial number, by package, or by product. Each branch runs only when its parameter is not NULL. - With
OPTION (RECOMPILE), each call compiles a plan for its own parameter case. Each case gets a different shape and a different plan hash. - The "best" plan is the product-only case, at 19.8 ms of CPU per call.
- The "latest" plan is a serial-number case, at about 5 s of CPU per call. No index supports that branch.
- The detector reported a factor of 252. The real problem is a missing index on one branch. It is not a plan choice.
Pattern 2: a parameter-sensitive query
- A manifest list query has 12 Query Store plans with 12 different plan hashes. Each plan was compiled for a different location.
- CPU tracks the row count. Plans that return about 26,000 rows use 540 to 630 ms per call. Plans that return about 50 rows use 3 to 4 ms per call.
- The detector reported a factor of 187. The "best" plan served only small locations.
We exported the per-plan Query Store statistics of these two queries. The detector SQL reported both, at 252 and 187.
Reproduction
The scratch test ran in a local SQL Server 2025 container, at compatibility level 160. Parameter Sensitive Plan optimization, DOP feedback and CE feedback were off. The detector SQL came verbatim from dev at 4d730bb42. It ran in PostgreSQL 18, over the scratch database's Query Store rows.
| Case | Plans | Detector result |
|---|---|---|
lookup_items: a three-way UNION with OPTION (RECOMPILE), and no index on serial_number |
package seek 0.105 ms, product seek 0.476 ms, serial scan 125.7 ms | factor 1,191 |
list_manifests_b: one location with 1,000,000 rows and 200 locations with 100 rows. The index does not cover the query |
small location: seek and key lookup, 0.25 ms. Big location: parallel scan, 256.8 ms. Both return 50 rows | factor 1,027 |
list_manifests_a: the same data. The index covers the query |
small location: serial seek, 0.10 ms. Big location: parallel seek, 189.7 ms. Both return 50 rows | factor 1,844 |
list_manifests_c: the covered query with OPTION (MAXDOP 1) (the control) |
one plan and one hash for all 110 calls | no report |
- The control works as designed. When the shape is the same, Query Store keeps one plan, and the detector does not report.
- The false reports need a shape that differs by input. That is common for parameter-sensitive queries and for
OPTION (RECOMPILE). - Rows per call do not separate the cases. In
list_manifests_aandlist_manifests_b, both plans return 50 rows.
Scratch setup (abridged)
CREATE TABLE dbo.item (item_id int IDENTITY PRIMARY KEY, serial_number varchar(40) NOT NULL, package_id int NOT NULL, product_id int NOT NULL, filler char(150) NOT NULL DEFAULT 'x');
-- 1,500,000 rows: unique serials, 10 rows per package, 100 rows per product
CREATE INDEX ix_item_package ON dbo.item (package_id);
CREATE INDEX ix_item_product ON dbo.item (product_id);
CREATE PROCEDURE dbo.lookup_items @serial varchar(40) = NULL, @package_id int = NULL, @product_id int = NULL AS
SET NOCOUNT ON;
SELECT i.item_id, i.serial_number, i.package_id, i.product_id FROM dbo.item AS i WHERE @serial IS NOT NULL AND i.serial_number = @serial
UNION
SELECT i.item_id, i.serial_number, i.package_id, i.product_id FROM dbo.item AS i WHERE @package_id IS NOT NULL AND i.package_id = @package_id
UNION
SELECT i.item_id, i.serial_number, i.package_id, i.product_id FROM dbo.item AS i WHERE @product_id IS NOT NULL AND i.product_id = @product_id
OPTION (RECOMPILE);
-- workload: 40 product-only calls, 30 package-only calls, then 80 serial-only calls
CREATE TABLE dbo.manifest (manifest_id int IDENTITY PRIMARY KEY, location_id int NOT NULL, created_at datetime2(0) NOT NULL, status tinyint NOT NULL, amount money NOT NULL, ref_code char(40) NOT NULL DEFAULT 'R', filler char(100) NOT NULL DEFAULT 'x');
-- location 1: 1,000,000 rows. Locations 2 to 201: 100 rows each
CREATE INDEX ix_manifest_location ON dbo.manifest (location_id, created_at) INCLUDE (status, amount);
CREATE PROCEDURE dbo.list_manifests_b @location_id int AS
SET NOCOUNT ON;
SELECT TOP (50) m.manifest_id, m.created_at, m.status, m.amount, m.ref_code
FROM dbo.manifest AS m WHERE m.location_id = @location_id ORDER BY m.amount DESC, m.created_at DESC;
-- list_manifests_a: the same without ref_code. list_manifests_c: list_manifests_a plus OPTION (MAXDOP 1)
-- workload per procedure: clear the database's plan cache, 40 small-location calls,
-- clear it again, then 60 to 70 calls for location 1Forcing the "best" plan
Each "best" plan was forced with sp_query_store_force_plan. The expensive case then ran 5 times, and the plan was unforced.
| Case | Without the force | With the "best" plan forced |
|---|---|---|
lookup_items, serial lookups |
125.7 ms | 1,334.2 ms (10.6× slower): a full scan of ix_item_package and a key lookup for each row |
list_manifests_b, big location |
256.8 ms | 1,118.9 ms (4.4× slower): a seek and 1,000,000 key lookups |
Query Store reported both forces as a success, with force_failure_count 0. So the force-failure check in the advice cannot see this harm.
What the advice says now
ComposePlanRegressioninPerformanceMonitor.Analysis/FactAdvice.cssays that Query Store has the faster plan on record, so a plan choice got worse. It says that forcing the faster plan stops the cost at once.ComposeCpuSpikechecks PLAN_REGRESSION before PARAMETER_SENSITIVITY. When both facts fire, it advises a force. Its "do NOT force a plan" text then never shows. At least one production story with these targets had both facts.- The force bot already blocks this case.
FactRemediation.ForcePlanBlockersaddsParameterSensitivityCoFiredwhen the same query carries the parameter-sensitivity signature. The detector and the advice text do not use that signal.
Possible fixes (not tested)
- Mark statements that carry
OPTION (RECOMPILE). Do not report them as plan regressions, or report them with "do not force" advice. The hint is in the statement text that Query Store keeps. - Compare the compiled parameter values of the two plans. They are in the plan XML as
ParameterCompiledValue. If they differ, the comparison crosses inputs. Report it as parameter sensitivity, not as a regression. - Let "do not force" win in every advice block when PLAN_REGRESSION and PARAMETER_SENSITIVITY fire for the same query.
- Dividing CPU by rows returned is not enough. It would clear the production manifest case. It would not clear pattern 1 or the scratch
list_manifestscases.
This is not planned for 3.10.0.
Activity
Lite: PLAN_REGRESSION skips plans compiled for different inputs (#5630)
Merge the shared inputs-check branch (#5630)
Lite: plan-regression inputs check, parameter order (#5630)
Lite: PLAN_REGRESSION drops plans compiled for different inputs (#5630)
PLAN_REGRESSION drops queries whose plans were compiled for different…
The advice no longer says to force a plan when parameter sensitivity …
Keep the PLAN_REGRESSION pins and census in step with the input check (…
PLAN_REGRESSION inputs check: scrub the Lite plan fetch, judge a quer…
Reword the compare-first advice so the T-SQL convention guard reads i…
erikdarlingdata commentedon Oct 9, 2026
Claude posting for Erik Darling.
First real plan flip, and a correction to fix 2 in the issue body. Comparing compiled parameter values alone would hide this real regression.
Running tally on the same production store
OPTION (RECOMPILE)lookup (pattern 1)Another agent session checks the plan-regression alerts on that store as they come in. The tally will grow.
The real flip
The numbers come from Query Store runtime statistics on the store's readable secondary, weighted by executions. The other session read them. I did not measure them again.
The query is a PSP query variant. Its statement text carries
OPTION (PLAN PER VALUE(...)), and both plans haveQueryVariantID2. It has noOPTION (RECOMPILE).| Plan | Executions | CPU per execution | Logical reads per execution | Rows returned per execution |
|---|---|---|---|---|
| Best: seeks two filtered indexes | 584,193 | 0.050 ms | 4.2 | 0.0025 on average, 2 at most |
| Latest: seeks a non-filtered index in place of one filtered index | 72,024 | 16.8 ms | 1,306 | 0.0031 on average, 1 at most |
The output is the same. Both plans almost always return no rows. So this is not pattern 2, where cost follows the rows returned.
The latest plan does about 310 times the reads for the same output. That is a real plan regression.
I compared the two plan XML files directly:
ParameterCompiledValueOptimizerStatsUsageLastUpdatein the latest planWhat this changes
erikdarlingdata commentedon Oct 11, 2026
Claude posting for Erik Darling.
A second real plan flip. The tally on the same production store is now 2 false positives and 2 real flips.
Running tally
OPTION (RECOMPILE)lookup (pattern 1)The new case
The other agent session read these numbers on the store. I did not measure them again.
TOP (@batch_size)of the key, filtered by a parent id andkey > @last, ordered by the key. It has noOPTION (RECOMPILE).TOPstops early, with no sortTOPWhat this means for #5630
Draft PR #5631 needs a new design before it continues
Erik agreed to hold #5631: "i agree with all recs".
OPTION (RECOMPILE)exclusion from PLAN_REGRESSION does not compare plans compiled for different inputs (#5630) #5631. Both cases support it.PLAN_REGRESSION does not compare plans compiled for different inputs (#…
erikdarlingdata commentedon Oct 12, 2026
Claude posting for Erik Darling.
Fixed by #5631, merged to dev as 6efa76d. It ships in the next release.
PLAN_REGRESSION now drops a query when the statement has OPTION (RECOMPILE), when the newer plan returns 10 times the rows per call or more at a similar cost per row, or when the two plans were compiled for different parameter values and the statistics did not change. Dropped queries are counted in
cross_input_excluded_count. A plan pair compiled for different values after a statistics update is still reported, with advice to confirm on one slow call before forcing a plan.Known limit: a classic bad parameter sniff with unchanged statistics is now dropped too, because the collected data cannot tell it from parameter sensitivity. It still shows in the excluded count.
Reuse fresh PLAN_REGRESSION candidates across current passes; build n…