SQL Server 2022 Intelligent Query Processing

SQL Server 2022 added a family of features that address exactly the problems in this module — plans that are wrong because a single cached plan cannot suit every parameter, and plans that are wrong because the estimates behind them were wrong. They work automatically once the database is at compatibility level 160.

Confirm you are eligible before reading on:

SELECT  SERVERPROPERTY('ProductMajorVersion') AS major_version,   -- need 16
        (SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME()) AS compat_level,
        (SELECT is_query_store_on   FROM sys.databases WHERE name = DB_NAME()) AS query_store_on;

Major version 16 and compatibility level 160. Several of these also require Query Store to be on, because that is where the feedback is persisted.

Parameter Sensitive Plan (PSP) optimisation

This is the direct answer to the sniffing problem in Lesson 2.

Previously: one cached plan per query, compiled for whichever values ran first. With PSP: SQL Server identifies a query whose performance is sensitive to a parameter with a skewed distribution, and caches up to three plan variants — for low, medium and high cardinality ranges of that parameter. At execution it picks the variant matching the value passed.

Your one-week versus five-year report can now get an appropriate plan for both, without a hint and without a recompile per execution.

The limits are worth knowing, because they explain why it will not always kick in:

It applies to equality predicates on columns whose statistics show significant skew. A pure range predicate on a date is not the primary target.
At most three variants per query, on a limited number of parameters.
The query must be eligible: the optimizer decides, and you cannot request it.

Check whether a query is using it:

SELECT  qsq.query_id,
        qsq.query_parameterization_type_desc,
        qsp.plan_id,
        qsp.query_plan_hash,
        SUM(rs.count_executions)     AS executions,
        AVG(rs.avg_duration)/1000.0  AS avg_ms
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan  AS qsp ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = qsp.plan_id
WHERE qsq.query_id = 1234                     -- placeholder
GROUP BY qsq.query_id, qsq.query_parameterization_type_desc, qsp.plan_id, qsp.query_plan_hash
ORDER BY avg_ms DESC;

In the plan XML, a PSP-enabled query shows a Dispatcher node with the predicate ranges it uses to route executions. Multiple plans for one query with clearly different average durations, each performing well for its own parameter range, is PSP working as intended — not the problem from Lesson 2.

To disable it for testing, or if it regresses something:

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;

Memory grant feedback, now persisted

Memory grant feedback arrived in 2017 (batch mode) and 2019 (row mode): if a query spilled to tempdb, or reserved far more memory than it used, the engine adjusts the grant on the next execution and keeps adjusting until it converges.

The limitation was that the feedback lived in the plan cache. A restart, a failover, or plan eviction threw it away, and the query started over-granting or spilling again from scratch. On a nightly report that runs once a day, the feedback might never survive to the next run.

SQL Server 2022 persists it in Query Store, so it survives restarts. It also adds percentile feedback, which sizes the grant from a percentile of recent executions rather than the last one — much better for a query whose row counts genuinely vary.

-- Requires Query Store on
SELECT feature_name, feature_id, is_enabled
FROM sys.database_query_store_internal_state;   -- feedback state, 2022+

For a report that spilled to tempdb (Module 2 Lesson 5), this often resolves the spill without any work from you — after a few executions.

Cardinality Estimation feedback

New in 2022. The optimizer makes model assumptions when it estimates: that predicates are independent of each other, that data is uniformly distributed within a histogram step, that a join has a particular containment property. When those assumptions are wrong for your data, estimates are wrong in a consistent direction.

CE feedback notices repeated misestimation for a query, tries a different model assumption, verifies that the alternative actually performed better, and persists the adjustment as a Query Store hint if it did. If it did not help, it reverts.

This is the feature that most directly attacks the "estimated 1, actual 940,000" pattern from Module 2 — automatically, over repeated executions. You will see it as Query Store hints appearing that you did not create:

SELECT query_id, query_hint_text, comment,
       last_query_hint_failure_reason_desc
FROM sys.query_store_query_hints;

Hints with a comment indicating they were applied by feedback are the engine's own work. Leave them alone unless you are diagnosing a regression.

Degree of Parallelism (DOP) feedback

Reports go parallel, and more parallelism is not always faster. Past a point, the cost of splitting work and merging results exceeds the benefit, and excess parallelism also increases CXPACKET waits and contends with the rest of the workload.

DOP feedback monitors parallel query efficiency and lowers the degree of parallelism for queries that are not benefiting, persisting the adjustment in Query Store. It only ever lowers DOP, never raises it, and it verifies before keeping the change.

ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON;   -- 2022; off by default in some builds

Optimized plan forcing

Also 2022: when a plan is forced, the engine stores a compressed "replay script" of the optimization steps, so re-compiling the forced plan is much cheaper. It reduces the CPU cost of plan forcing substantially, which makes forcing a more sustainable stabilization tool than it used to be.

Carried forward from 2017 and 2019

Still relevant and still doing work:

Adaptive joins (2017, batch mode; 2019 broader) — defer the nested-loops-versus-hash decision until the build input's real row count is known at runtime.
Interleaved execution (2017) — pause optimization to get a real row count from a multi-statement TVF instead of using the fixed 100-row guess.
Table variable deferred compilation (2019) — compile statements using a table variable after it is populated, so the estimate is real instead of 1.
Scalar UDF inlining (2019) — Module 4 Lesson 5.
Batch mode on rowstore (2019) — batch-mode execution for analytic queries on regular rowstore tables, without needing a columnstore index. Very relevant to reporting.
Approximate count distinct — APPROX_COUNT_DISTINCT(), ~2% error, far less memory than COUNT(DISTINCT ...). Good for dashboard tiles where exactness is not required:

SELECT APPROX_COUNT_DISTINCT(CustomerId) AS approx_customers
FROM   dbo.Orders                        -- placeholder table
WHERE  OrderDate >= DATEADD(YEAR, -1, CAST(SYSDATETIME() AS date));

What to do with all this

If you are on 2022 at compatibility level 160 with Query Store on, most of it is already working and your job is to verify rather than configure. Two concrete actions:

Confirm compatibility level 160. A 2022 instance at level 110 gets none of this. That single ALTER DATABASE may be the largest available improvement.
Confirm Query Store is on and healthy. Memory grant persistence, CE feedback and DOP feedback all store their state there. Query Store off means the feedback loop resets constantly.

And one caution: these features change plans automatically, which is a good thing that also means a plan can change without anyone deploying anything. That is precisely why the baseline discipline from Module 1 matters — Query Store shows you what changed and when.

The short version

2022's Parameter Sensitive Plan optimisation caches up to three plan variants for a skewed parameter and routes each execution to the right one, which addresses the classic reporting sniffing problem without a hint. Memory grant feedback now persists in Query Store instead of dying with the plan cache, and cardinality estimation feedback tries different model assumptions and keeps the change only if it verifies faster. All of it needs compatibility level 160 though, so check that first: a 2022 instance hosting a database at 110 gets none of it.