SQL Server 2022 query processing

In one line: at compatibility level 160 with Query Store on, SQL Server 2022 changes plans by itself. It can keep more than one plan for a statement, attach hints, and remember memory grant corrections. Before you tune a query, check whether the engine is already acting on it.

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

What you’ll see

  • One statement with several plans. In the plan cache its text ends in option (PLAN PER VALUE(...)).
  • Hints on a query that nobody added, in sys.query_store_query_hints.
  • Memory grants that come back right after a restart or a cleared plan cache, where they used to start over.
  • A plan that isn’t the one you’d expect from the code alone.

Why it happens

  • Parameter Sensitive Plan (PSP) optimization gives a skewed parameter a dispatcher plan with up to three variants, each compiled for its own range of row counts. In the demo, the big customer read 1,500,880 pages through the small customer’s plan at level 150, and 17,124 through its own variant at 160. PSP needs enough skew: at 500,000 rows against 50 it didn’t engage (SkewnessThresholdNotMet), and at 500,000 against 1 it did.
  • Cardinality estimation (CE) feedback watches a repeating query’s estimates. It tries a hint that changes an estimator assumption, keeps it if the query improves, and removes it if not. In the demo it tried, found no gain (VERIFICATION_REGRESSED) and took the hint back off.
  • Memory grant feedback persistence saves grant corrections in Query Store. After the plan cache was cleared, the grant came back at 3,016 KB with persistence on, and 110,152 KB again with it off.

What to do

  • Run at compatibility level 160 (a database restored from an older server keeps its old level) with Query Store on.
  • Before tuning, check the three places the engine leaves evidence:
    • sys.query_store_query_variant
    • sys.query_store_query_hints
    • sys.query_store_plan_feedback
  • Don’t assume PSP covers your skewed procedure. Check for variants. If there are none, the parameter sniffing fixes still apply.

What doesn’t help

  • Clearing the plan cache to “reset” a query. On 2022 the persisted feedback comes straight back.
  • Expecting DOP feedback by default. It’s off (DOP_FEEDBACK = 0), and in this demo it didn’t fire even when turned on.

Check your server

-- What the engine is doing by itself in this database (needs Query Store, SQL Server 2022+).
SELECT kind = 'plan feedback', query_id = CAST(p.query_id AS varchar(20)),
       detail = f.feature_desc COLLATE DATABASE_DEFAULT, state = f.state_desc COLLATE DATABASE_DEFAULT
FROM sys.query_store_plan_feedback AS f
JOIN sys.query_store_plan AS p ON p.plan_id = f.plan_id
UNION ALL
SELECT 'query hint', CAST(h.query_id AS varchar(20)),
       h.query_hint_text COLLATE DATABASE_DEFAULT, h.source_desc COLLATE DATABASE_DEFAULT
FROM sys.query_store_query_hints AS h
UNION ALL
SELECT 'PSP variant', CAST(v.query_variant_query_id AS varchar(20)),
       'parent query ' + CAST(v.parent_query_id AS varchar(20)), NULL
FROM sys.query_store_query_variant AS v;

See it happen

The sql2022-iqp demo triggers PSP, CE feedback and memory grant feedback persistence in a database of its own, and shows where each leaves its evidence. On 2019 it reports SKIPPED.

Related: parameter sniffing · Query Store plan regressions · memory grants sized by declared width · optional parameter plans (SQL Server 2025)

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