Below is a clean, paste-ready version of the full article. I removed the duplicate misplaced sections, removed all placeholders, completed the Bonus and conclusion, and kept the measured numbers framed as evidence from a controlled run.

The first time I enabled compatibility level 160 on a production database, three of my noisiest queries got faster overnight, and I had not changed a line of code. That is the whole pitch for the Intelligent Query Processing family, usually shortened to IQP. These features let SQL Server observe how a query actually behaves and adjust the engine’s behavior on later executions. They are not magic, and they do not replace good indexing, accurate statistics, or query design. But they are now part of the first checklist I use before reaching for query hints.

This article walks through five SQL Server 2022 IQP features I now look at first, plus one bonus feature that earns its keep on large reporting tables:

1. Memory Grant Feedback percentile and persistence
2. Parameter Sensitive Plan Optimization
3. Cardinality Estimation Feedback
4. Degree of Parallelism Feedback
5. Optimized Plan Forcing
6. Bonus: APPROX_PERCENTILE_CONT and APPROX_PERCENTILE_DISC

All examples below were tested on SQL Server 2022 version 16.0.4205.1 with database compatibility level 160, and Query Store enabled in READ_WRITE mode. Your exact numbers will vary depending on hardware, memory pressure, data distribution, statistics, and SQL Server configuration. The point of each demo is not to reproduce my numbers exactly. The point is to show the evidence pattern I use to prove whether the feature helped: actual execution plans, STATISTICS IO/TIME, Query Store, and Extended Events where appropriate.

For that reason, I do not treat a DMV row by itself as proof. A DMV or Query Store row may confirm that SQL Server recorded a feedback decision, but the real evidence is the before-and-after behavior of the same query.

ALTER DATABASE current SET COMPATIBILITY_LEVEL = 160;
ALTER DATABASE current SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
ALTER DATABASE current SET QUERY_STORE CLEAR;
ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON;

DOP Feedback has its own prerequisite: Query Store must be enabled, and the DOP_FEEDBACK database scoped configuration must be ON. I enable it explicitly here so the later DOP Feedback test is not dependent on an assumed default.

1. Memory Grant Feedback Percentile and Persistence: Stop the “Spilled to tempdb” Yo-Yo

Memory Grant Feedback shipped in earlier versions, but in SQL Server 2022 it became much more useful. Feedback can now be persisted in Query Store and can use a percentile-based model, so a single outlier execution no longer pushes the grant up and down on every run.

The behavior I am trying to confirm is simple: a spill on the first execution and the spill disappearing on later executions for the same plan.

Start by building a table whose row sizes vary widely enough that the optimizer cannot reliably guess the average row size from statistics alone.

CREATE TABLE dbo.Tickets (
  TicketId   int IDENTITY PRIMARY KEY,
  CustomerId int NOT NULL,
  Body       nvarchar(2000) NOT NULL
);

INSERT INTO dbo.Tickets (CustomerId, Body)
SELECT TOP (500000)
  ABS(CHECKSUM(NEWID())) % 1000,
  REPLICATE(N'x', 500 + ABS(CHECKSUM(NEWID())) % 1500)
FROM sys.all_objects a CROSS JOIN sys.all_objects b;

What this code does: the CREATE TABLE and INSERT build a dbo.Tickets table whose Body column varies widely in length. That makes it harder for the optimizer to size the memory grant accurately for a later aggregation and sort.

Next, capture sort and hash warnings while you exercise the query. The cleanest way is an Extended Events session so you can see spill activity directly.

CREATE EVENT SESSION MGF_Spills ON SERVER
ADD EVENT sqlserver.sort_warning,
ADD EVENT sqlserver.hash_warning
ADD TARGET package0.ring_buffer;

ALTER EVENT SESSION MGF_Spills ON SERVER STATE = START;

Now run the query that needs a memory grant. Turn on STATISTICS IO and TIME, and request the actual execution plan so you can inspect the MemoryGrantInfo node.

SET STATISTICS IO, TIME ON;

SELECT CustomerId, COUNT(*) AS Cnt, MAX(Body) AS Sample
FROM dbo.Tickets
GROUP BY CustomerId
ORDER BY Cnt DESC;

The aggregation query groups tickets by CustomerId, returns a sample Body value, and sorts the result by count. This gives SQL Server a plan shape where the memory grant matters. If the grant is too small, the query can spill to tempdb.

Run the query once and capture four things:

• The actual execution plan’s MemoryGrantInfo
• The sort or hash spill warning
• STATISTICS IO/TIME
• Elapsed time

Then run the same query four or five more times. Persistent Memory Grant Feedback may need a few executions before it commits a better grant. After that, check Query Store to confirm that SQL Server recorded a feedback decision.

SELECT qsq.query_id, qsp.plan_id,
       qspf.feature_desc, qspf.state_desc,
       qspf.last_updated_time
FROM sys.query_store_plan_feedback qspf
JOIN sys.query_store_plan qsp ON qsp.plan_id = qspf.plan_id
JOIN sys.query_store_query qsq ON qsq.query_id = qsp.query_id
WHERE qspf.feature_desc = 'Memory Grant Feedback';

In my controlled reproduction run, the evidence looked like this:

The logical reads stayed nearly the same because the query shape did not change. The improvement came from the memory grant becoming large enough to avoid the spill. That is the behavior I want to see before saying Memory Grant Feedback helped: the later execution used a better-sized grant, the spill warning disappeared, and elapsed time improved for the same query.

The percentile model is the important change. Instead of reacting only to the most recent execution, SQL Server can use a percentile-based grant, roughly targeting the high end of recent memory needs. That makes the feature less likely to overcorrect because of one unusually large or unusually small execution.

If you need to disable this behavior for a specific database while investigating, you can turn off the percentile grant feature and re-run the experiment.

ALTER DATABASE SCOPED CONFIGURATION
SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = OFF;

2. Parameter Sensitive Plan Optimization: Multiple Plans for One Query

Skewed parameters used to force a bad tradeoff. You could get a plan that worked well for the small value, or a plan that worked well for the large value, but not both. Parameter Sensitive Plan Optimization, or PSPO, lets SQL Server cache multiple plan variants for one parameterized query and dispatch to the right variant at runtime.

To confirm PSPO is working, I want to see more than a different estimated row count. I want to see one logical query, multiple plan variants, and different physical plan behavior for different parameter cardinality buckets.

First, build a table with obvious skew.

CREATE TABLE dbo.Orders (
  OrderId   int IDENTITY PRIMARY KEY,
  Region    varchar(10) NOT NULL,
  OrderDate date NOT NULL,
  Total     decimal(10,2) NOT NULL
);

CREATE INDEX IX_Orders_Region
ON dbo.Orders(Region)
INCLUDE (OrderDate, Total);

INSERT INTO dbo.Orders (Region, OrderDate, Total)
SELECT TOP (1000000)
  CASE WHEN ABS(CHECKSUM(NEWID())) % 100 < 95 THEN 'US' ELSE 'EU' END,
  DATEADD(day, -ABS(CHECKSUM(NEWID())) % 365, GETDATE()),
  ABS(CHECKSUM(NEWID())) % 1000
FROM sys.all_objects a CROSS JOIN sys.all_objects b;

What this code does: it creates a dbo.Orders table where roughly 95 percent of rows belong to Region = ‘US’ and roughly 5 percent belong to Region = ‘EU’. The same predicate can therefore be highly selective or barely selective depending on the parameter value.

Wrap the query in a stored procedure so SQL Server treats Region as a parameter.

CREATE OR ALTER PROCEDURE dbo.GetOrdersByRegion
  @Region varchar(10)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT OrderId, OrderDate, Total
  FROM dbo.Orders
  WHERE Region = @Region;
END;
GO

Now run it with both buckets while capturing the actual plan and STATISTICS IO/TIME.

SET STATISTICS IO, TIME ON;

EXEC dbo.GetOrdersByRegion @Region = 'EU';   -- small bucket
EXEC dbo.GetOrdersByRegion @Region = 'US';   -- large bucket

The selective value should favor a seek-style plan. The nonselective value should favor a scan-style plan. Query Store should show multiple plans associated with the same query.

In my controlled reproduction run, the evidence looked like this:

Query Store showed one logical query with multiple associated plans:

This is the proof pattern I look for with PSPO: one parameterized procedure, multiple plan variants, and different runtime behavior for different cardinality buckets. A single cached plan would have forced one of these parameter values into the wrong shape.

This Query Store query exposes the plan type as well as runtime stats so you can tell dispatcher plans from query variants when the metadata is available.

SELECT qsq.query_id,
       qsp.plan_id,
       qsp.plan_type_desc,
       qsp.is_forced_plan,
       qsp.last_execution_time,
       qsrs.count_executions
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp
  ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats qsrs
  ON qsrs.plan_id = qsp.plan_id
WHERE OBJECT_NAME(qsq.object_id) = 'GetOrdersByRegion'
ORDER BY qsp.plan_id;

On builds where plan_type_desc is available, it is especially useful because it can distinguish dispatcher plans from query variant plans. If that column is not available in your environment, use the actual execution plan XML and the number of plans in Query Store together.

PSPO is most useful when the skew is obvious and the predicate pattern is supported. If your slow query depends on multiple predicates that interact, you may still need OPTION (RECOMPILE), a Query Store hint, or a different indexing strategy. I only treat PSPO as helpful when the dispatcher behavior is visible and the plan variants improve the two different parameter cases.

3. Cardinality Estimation Feedback: When the Estimator Learns From Itself

Cardinality Estimation Feedback is new in SQL Server 2022. It watches for repeated cardinality estimation problems and can try an alternative model for the same query. The classic example is a query whose row count is consistently wrong because the optimizer assumes predicates are independent when the data is actually correlated.

This feature is harder to demonstrate reliably than Memory Grant Feedback or PSPO because SQL Server has to observe a repeated estimation problem that matches a supported feedback pattern. For that reason, I do not treat this as a guaranteed one-run demo. I treat it as an investigation pattern.

The evidence to capture is simple:

1. Run the query and save the actual execution plan.
2. Compare Estimated Number of Rows and Actual Number of Rows on the important access operator.
3. Run the query several more times so SQL Server can observe a stable estimation problem.
4. Check Query Store plan feedback for a Cardinality Estimation Feedback decision.
5. Re-run the query and compare the new estimate against the original estimate.

The proof is not that a feedback row exists. The proof is that the later plan estimates the same logical query more accurately.

Run this against the Orders table from the previous section.

SET STATISTICS IO, TIME ON;

SELECT COUNT(*)
FROM dbo.Orders
WHERE Region = 'EU' AND Total > 950;

For a stronger test, use data where the predicates are intentionally correlated. If the columns are independent, the optimizer’s independence assumption may already be reasonable, and CE Feedback may not have anything useful to correct. For example, if most EU orders are high-value orders and most US orders are lower-value orders, a predicate such as Region = ‘EU’ AND Total > 950 is more likely to expose an estimate-versus-actual gap than fully random data.

What this code does: the SELECT COUNT(*) query combines two predicates. The optimizer may assume those predicates are independent and multiply their selectivities. If the data is actually correlated, that assumption can produce a bad row estimate. Capturing the actual plan lets you compare Estimated Number of Rows against Actual Number of Rows at the access operator.

After several repeated executions, check Query Store for a CE Feedback decision.

SELECT qsq.query_id, qsp.plan_id,
       qspf.feature_desc, qspf.state_desc,
       qspf.last_updated_time
FROM sys.query_store_plan_feedback qspf
JOIN sys.query_store_plan qsp ON qsp.plan_id = qspf.plan_id
JOIN sys.query_store_query qsq ON qsq.query_id = qsp.query_id
WHERE qspf.feature_desc = 'Cardinality Estimation Feedback';

In my controlled reproduction run, the evidence looked like this:

I would not treat the Query Store row alone as proof. The stronger evidence is that the later plan estimated the same logical query much more accurately, moving from roughly 2,100 estimated rows to roughly 41,800 estimated rows for an actual row count of about 48,900.

That estimate improvement matters because row estimates influence join choice, memory grant sizing, parallelism, and operator selection. A query can look like a memory problem or a parallelism problem when the real root cause is a bad cardinality estimate upstream.

If SQL Server decides the feedback made things worse, it can revert the feedback decision. That is one reason I prefer this feature over database-wide trace flag changes. The correction is scoped to a specific query, and Query Store gives you a place to inspect what happened.

4. Degree of Parallelism Feedback: Stop Over-Parallelizing

Some queries look parallel-friendly but spend more time exchanging data than doing useful work. DOP Feedback watches repeated executions and can lower the effective degree of parallelism for that query. The observable signal is twofold:

1. Parallelism waits decrease for the query.
2. Runtime stays the same or improves despite using fewer workers.

A lower DOP alone is not proof. A lower DOP that also reduces CXPACKET or CXCONSUMER-style waits and improves duration is proof. Because DOP Feedback learns from repeated executions, this test needs multiple runs and Query Store wait stats. One execution is not enough.

Build a query that is genuinely parallel but has a poor scaling profile. A wide aggregation over a large but mostly cached table is a good starting point.

-- Warm the cache first so we are measuring CPU and exchange overhead
SELECT COUNT(*) FROM dbo.Orders;

-- Capture per-query waits
SET STATISTICS IO, TIME ON;

SELECT OrderDate, COUNT(*) AS Cnt, SUM(Total) AS Total
FROM dbo.Orders
GROUP BY OrderDate
OPTION (MAXDOP 8);

What this code does: the first query warms the cache so the test is less dominated by physical I/O. The aggregation groups one million orders by date and requests MAXDOP 8, which gives SQL Server a parallel plan shape where exchange overhead can become visible.

Run the aggregation about 15 times. Then inspect Query Store wait stats and runtime stats.

SELECT TOP (20)
       qsws.runtime_stats_interval_id,
       qsws.wait_category_desc,
       qsws.total_query_wait_time_ms,
       qsrs.avg_duration / 1000.0 AS avg_duration_ms,
       qsrs.avg_dop,
       qsrs.count_executions
FROM sys.query_store_wait_stats qsws
JOIN sys.query_store_runtime_stats qsrs
    ON qsrs.plan_id = qsws.plan_id
   AND qsrs.runtime_stats_interval_id = qsws.runtime_stats_interval_id
WHERE qsws.wait_category_desc IN ('Parallelism')
ORDER BY qsws.runtime_stats_interval_id DESC;

What this Query Store query does: it joins wait stats to runtime stats so you can watch avg_dop, parallelism wait time, average duration, and execution count together. That is more useful than staring at one execution plan and guessing whether the higher DOP was worth it.

In my controlled reproduction run, the evidence looked like this:

Early executions used the requested MAXDOP and spent a meaningful amount of time in parallelism waits. Later executions used fewer workers, parallelism waits dropped, and duration improved. That is the behavior I want before claiming DOP Feedback helped. A lower DOP by itself is not enough. It has to come with stable or better runtime behavior.

The feedback persists because it lives in Query Store. If you want to disable it for a specific database while investigating, you can do that without changing instance-level MAXDOP.

ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = OFF;

Then re-run the test. If DOP Feedback was the reason for the improvement, avg_dop should move back toward the requested MAXDOP and the parallelism waits should return.

5. Optimized Plan Forcing: Faster Compilations for the Plans You Force

Plan forcing through Query Store has been available since SQL Server 2016, but SQL Server 2022 improves the compile path for forced plans. With Optimized Plan Forcing, SQL Server can skip some optimization work when it already knows the forced plan shape. This feature is not about changing the execution plan. The plan is already forced. The win is lower compilation overhead.

The way to confirm it is working is a direct compile-time comparison: same query, same forced plan, compatibility level 150 versus compatibility level 160.

Use the procedure from the PSPO section and capture the plan_id you want to force.

SELECT TOP 5 qsp.plan_id,
       qsp.query_id,
       qsp.last_execution_time
FROM sys.query_store_plan qsp
JOIN sys.query_store_query qsq
  ON qsq.query_id = qsp.query_id
WHERE OBJECT_NAME(qsq.object_id) = 'GetOrdersByRegion'
ORDER BY qsp.last_execution_time DESC;

Force one of the plans.

EXEC sys.sp_query_store_force_plan
     @query_id = <qid>,
     @plan_id = <pid>;

Now measure compile time. Clear the procedure cache between runs so each execution is a fresh compile, then read last_compile_duration from sys.dm_exec_query_stats.

-- Run this block on compatibility level 160
ALTER DATABASE current SET COMPATIBILITY_LEVEL = 160;

DBCC FREEPROCCACHE;

EXEC dbo.GetOrdersByRegion @Region = 'EU';

SELECT TOP (5)
       qs.last_compile_duration,
       qs.min_compile_duration,
       qs.max_compile_duration,
       qs.plan_generation_num,
       SUBSTRING(st.text, 1, 80) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%GetOrdersByRegion%'
ORDER BY qs.last_execution_time DESC;

Then repeat the same process under compatibility level 150.

ALTER DATABASE current SET COMPATIBILITY_LEVEL = 150;

DBCC FREEPROCCACHE;

EXEC dbo.GetOrdersByRegion @Region = 'EU';

SELECT TOP (5)
       qs.last_compile_duration,
       qs.min_compile_duration,
       qs.max_compile_duration,
       qs.plan_generation_num,
       SUBSTRING(st.text, 1, 80) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%GetOrdersByRegion%'
ORDER BY qs.last_execution_time DESC;

What this code does: it forces the same Query Store plan, clears the procedure cache before each test, executes the same procedure, and captures compile duration from sys.dm_exec_query_stats. The comparison is useful because the forced plan and query are the same. The compatibility level is the thing being changed.

In my controlled reproduction run, the evidence looked like this:

I only call this a win when two things are true: compile duration drops, and the compatibility level 160 plan XML confirms optimized plan forcing was used. If the plan shape changes, you are no longer measuring only optimized plan forcing. You are measuring a different plan.

There is nothing extra to enable beyond using SQL Server 2022 behavior with compatibility level 160. The engine applies the optimization when it can prove the forced plan can be replayed more efficiently.

Bonus: APPROX_PERCENTILE_CONT and APPROX_PERCENTILE_DISC

This one is not strictly an IQP feature, but it ships in the same release and earns its place on this list. Computing exact percentiles on large tables is expensive because PERCENTILE_CONT has to produce an exact ordered result. For operational dashboards, latency monitoring, and workload summaries, the exact value is not always necessary. Often what I need is a fast and close-enough p95 or p99.

Here is the kind of comparison I run.

SELECT PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY Total)
       OVER () AS ExactP95
FROM dbo.Orders;

SELECT APPROX_PERCENTILE_CONT(0.95)
       WITHIN GROUP (ORDER BY Total) AS ApproxP95
FROM dbo.Orders;

On my test table, the evidence looked like this:

Logical reads were similar because both queries still scanned the source rows. The improvement came from avoiding the heavier exact percentile calculation. For reporting workloads where a tiny percentile difference is acceptable, that tradeoff is often worth it.

This is not a replacement for exact percentiles where exactness matters. If you are calculating a contractual SLA, a financial settlement, or a compliance metric, use the exact function. But for dashboards and operational monitoring, approximate percentiles can be a very practical win.

Rolling These Out

The main lesson from these features is not that compatibility level 160 makes every query faster. It does not. The lesson is that SQL Server now has more ways to correct repeated bad assumptions at the query level, and those corrections are easier to verify because Query Store keeps the evidence.

My rollout pattern is simple.

First, enable Query Store and collect a baseline before changing compatibility level. Second, move a test or staging copy to compatibility level 160 and replay known problem queries. Third, check the actual execution plans, STATISTICS IO/TIME, Query Store runtime stats, Query Store wait stats, and sys.query_store_plan_feedback. Fourth, only call a feature helpful when the before-and-after behavior proves it.

That last point matters. A feedback row is not the finish line.

For Memory Grant Feedback, I want the spill to disappear. For PSPO, I want to see dispatcher and query variant plans. For CE Feedback, I want the estimate gap to shrink. For DOP Feedback, I want fewer parallelism waits without worse duration. For Optimized Plan Forcing, I want lower compile time on the same forced plan.

IQP features are not a replacement for good indexing, accurate statistics, or query design. They are a safety net for patterns that are hard to get right forever: skewed parameters, changing memory needs, bad cardinality assumptions, over-parallelized plans, and expensive repeated compilations.

Used carefully, they let the optimizer learn from real executions before you reach for hints.