SQL Server Intelligent Query Processing: The Complete Guide to Self-Learning Features in 2022 and 2025

---

SQL Server has been quietly building a self-learning query optimization engine since 2017. Most DBAs know one or two features from this family. Few have a complete inventory of what is running in their environment right now, what is turned off that should be on, what requires Query Store to function, and what is coming in SQL Server 2025. This article builds that complete picture.

Microsoft calls this family Intelligent Query Processing (IQP). The features divide into two categories: features that learn from previous executions and automatically adjust future behavior, and features that adapt during a single execution without learning across runs. Both categories matter, but the learning features are the ones that change how a DBA thinks about long-term workload management. Every fact in this article is verified from official Microsoft Learn documentation as of August 2026.

1 The Two Categories: Learning Features vs Adaptive Features Beginner

Not every IQP feature learns. Understanding the distinction matters because learning features require different monitoring, have different persistence behavior, and create different operational expectations than features that adapt only within a single query execution.

Learning features: observe, adjust, and persist

Learning features observe how a query performs across multiple executions, identify where the optimizer’s assumptions were wrong, adjust future executions based on what was observed, and persist verified adjustments so they survive SQL Server restarts. All learning features in SQL Server 2022 and 2025 use Query Store as their persistence layer. If Query Store is not enabled in READ_WRITE mode, learning features cannot persist their findings and fall back to basic behavior.

Learning Feature	Introduced	Learns What?	Persists?
Memory Grant Feedback (Batch Mode)	SQL Server 2017	Correct memory grant for query operations	Yes, requires Query Store (SQL 2022+)
Memory Grant Feedback (Row Mode)	SQL Server 2019	Correct memory grant for row mode operations	Yes, requires Query Store (SQL 2022+)
Memory Grant Feedback Persistence	SQL Server 2022	Retains memory grant adjustments across restarts	Yes, requires Query Store READ_WRITE
Memory Grant Percentile Feedback	SQL Server 2022	Learns grant from historical execution percentile	Yes, requires Query Store READ_WRITE
DOP Feedback	SQL Server 2022	Optimal degree of parallelism per repeating query	Yes, requires Query Store READ_WRITE
Cardinality Estimation (CE) Feedback	SQL Server 2022	Correct CE model assumptions for repeating queries	Yes, requires Query Store READ_WRITE
Parameter Sensitive Plan (PSP) Optimization	SQL Server 2022	Multiple plans for different parameter value ranges	Yes, stored in Query Store plan cache
CE Feedback for Expressions	SQL Server 2025	Learns expression-level selectivity across queries	Yes, requires Query Store READ_WRITE
Optional Parameter Plan Optimization (OPPO)	SQL Server 2025	Optimal plan for optional parameter patterns	Yes, stored in Query Store plan cache

Adaptive features: adjust within a single execution

Adaptive features improve execution of the current query without observing patterns across multiple runs. They do not require Query Store and do not persist anything. Each execution is independent. These features are valuable but require no DBA action beyond setting the correct compatibility level.

Adaptive Feature	Introduced	What It Does
Adaptive Joins (Batch Mode)	SQL Server 2017	Defers join type selection (Hash Join vs Nested Loops) until actual row counts are known during execution
Interleaved Execution	SQL Server 2017	Corrects cardinality estimates for multi-statement TVFs by executing them first to get actual row counts
Table Variable Deferred Compilation	SQL Server 2019	Uses actual row counts for table variables instead of always assuming one row
Scalar UDF Inlining	SQL Server 2019	Automatically rewrites eligible scalar UDFs into relational operators the optimizer can reason about
Batch Mode on Rowstore	SQL Server 2019	Uses batch processing mode for analytical queries without requiring columnstore indexes
Approximate Count Distinct	SQL Server 2019	Approximate COUNT DISTINCT using HyperLogLog algorithm for large datasets where exact precision is not required

2 The Query Store Dependency: Which Features Need It and Why Beginner

According to Microsoft Learn: “Several of the suite of intelligent query processing features require the Query Store to be enabled in order to benefit the user database.” The reason is persistence. Without Query Store, feedback features can observe a query’s behavior and adjust the current execution, but they cannot store the verified adjustment anywhere that survives a SQL Server restart or cache eviction. Every restart means starting from scratch.

With Query Store in READ_WRITE mode, verified feedback is written to sys.query_store_plan_feedback on disk. The next time the query executes after a restart, the verified adjustment is already available and applied immediately.

-- Check Query Store status across all user databases
SELECT
    name                    AS DatabaseName,
    is_query_store_on,
    actual_state_desc       AS QueryStoreState,
    desired_state_desc,
    readonly_reason
FROM sys.databases
WHERE database_id > 4
ORDER BY name;
-- actual_state_desc should be READ_WRITE for full IQP learning feature support
-- If READ_ONLY, check readonly_reason - may be storage limit exceeded

3 The Complete SQL Server 2022 IQP Feature Inventory Beginner

The following table is the authoritative reference for SQL Server 2022 IQP features. All values are verified from Microsoft Learn documentation. The database-scoped configuration name is the exact string to use in ALTER DATABASE SCOPED CONFIGURATION and sys.database_scoped_configurations queries.

Feature	Min Compat Level	Default in SQL 2022	Query Store Required?	Database Scoped Config Name
Batch Mode Memory Grant Feedback	140	ON	No (basic). Yes for persistence.	BATCH_MODE_MEMORY_GRANT_FEEDBACK
Row Mode Memory Grant Feedback	150	ON	No (basic). Yes for persistence.	ROW_MODE_MEMORY_GRANT_FEEDBACK
Memory Grant Feedback Persistence	140	ON (requires Query Store READ_WRITE)	Yes, READ_WRITE required	MEMORY_GRANT_FEEDBACK_PERSISTENCE
Memory Grant Percentile Feedback	140	ON (requires Query Store READ_WRITE)	Yes, READ_WRITE required	MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT
DOP Feedback	160	OFF (must enable manually)	Yes, READ_WRITE required	DOP_FEEDBACK
Cardinality Estimation (CE) Feedback	160	ON	Yes, READ_WRITE required	CE_FEEDBACK
Parameter Sensitive Plan Optimization	160	ON	No	PARAMETER_SENSITIVE_PLAN_OPTIMIZATION
Optimized Plan Forcing	Any (Query Store feature)	ON	Yes (Query Store feature)	OPTIMIZED_PLAN_FORCING
Adaptive Joins (Batch Mode)	140	ON	No	BATCH_MODE_ADAPTIVE_JOINS
Interleaved Execution (MSTVF)	140	ON	No	INTERLEAVED_EXECUTION_TVF
Table Variable Deferred Compilation	150	ON	No	DEFERRED_COMPILATION_TV
Scalar UDF Inlining	150	ON	No	TSQL_SCALAR_UDF_INLINING
Batch Mode on Rowstore	150	ON	No	BATCH_MODE_ON_ROWSTORE

4 Memory Grant Feedback: Three Layers Intermediate

Memory Grant Feedback is the oldest and most mature of the IQP learning features, introduced in SQL Server 2017. In SQL Server 2022 it has three distinct layers that build on each other. All three must be understood together.

Layer 1: Basic Memory Grant Feedback (SQL Server 2017 and 2019)

The query optimizer estimates how much memory a query needs before execution. When the estimate is wrong, either too high causing unused memory to be reserved, or too low causing data to spill to TempDB, performance suffers. Basic Memory Grant Feedback observes the actual memory used versus granted and adjusts the grant for the next execution. Without Query Store in SQL Server 2022, these adjustments exist only in the plan cache and are lost on restart or cache eviction.

Layer 2: Memory Grant Feedback Persistence (SQL Server 2022)

According to Microsoft Learn, Memory Grant Feedback Persistence writes verified memory grant adjustments to Query Store. This means the adjusted grant survives SQL Server restarts. The database-scoped configuration is MEMORY_GRANT_FEEDBACK_PERSISTENCE. It is ON by default at compatibility level 140 and higher when Query Store is in READ_WRITE mode.

Layer 3: Memory Grant Percentile Feedback (SQL Server 2022)

For queries with highly variable memory requirements across executions, a single adjusted grant value is not optimal. A query that sometimes needs 100 MB and sometimes 500 MB cannot be well-served by a fixed adjusted grant. Percentile Feedback uses a statistical approach, examining the history of memory grants in Query Store and selecting a grant based on a percentile of historical usage. The database-scoped configuration is MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT. It is ON by default when Query Store is in READ_WRITE mode.

-- Check Memory Grant Feedback activity in Query Store
-- feature_id = 1 in sys.query_store_plan_feedback
USE YourDatabase;

SELECT
    qspf.plan_id,
    qspf.feature_desc,
    qspf.state_desc,
    qspf.feedback_data,
    qspf.create_time,
    qspf.last_updated_time,
    LEFT(qt.query_sql_text, 100)    AS QueryText
FROM sys.query_store_plan_feedback  qspf
JOIN sys.query_store_plan           qsp ON qspf.plan_id      = qsp.plan_id
JOIN sys.query_store_query          qsq ON qsp.query_id      = qsq.query_id
JOIN sys.query_store_query_text     qt  ON qsq.query_text_id = qt.query_text_id
WHERE qspf.feature_desc LIKE '%Memory%'
ORDER BY qspf.last_updated_time DESC;

5 DOP Feedback: The Feature That Is Off by Default Intermediate

DOP Feedback is introduced in SQL Server 2022 and is the only SQL Server 2022 IQP learning feature that is OFF by default. According to Microsoft Learn: “DOP feedback is not enabled by default in SQL Server 2022 (16.x).” It requires compatibility level 160 and Query Store in READ_WRITE mode. It must be enabled per-database.

DOP Feedback identifies parallelism inefficiencies for repeating queries based on elapsed time and wait statistics. It lowers the degree of parallelism for those queries to reduce unnecessary CPU consumption and scheduling contention. Verified adjustments are stored in sys.query_store_plan_feedback and survive restarts. DOP Feedback never raises DOP above the effective MAXDOP setting and never pushes a query to serial execution (minimum adjusted DOP is 2).

6 Cardinality Estimation Feedback Intermediate

According to Microsoft Learn: “Starting with SQL Server 2022 (16.x), Cardinality Estimation (CE) feedback is part of the intelligent query processing family of features and addresses suboptimal query execution plans for repeating queries when these issues result from incorrect CE model assumptions.”

The query optimizer makes assumptions about data distribution and correlation when estimating cardinality. When those assumptions are consistently wrong for a specific query, CE Feedback identifies the incorrect assumption and tests a different CE model choice. If the alternative produces better results, the feedback is persisted in Query Store as a verified adjustment.

CE Feedback is ON by default at compatibility level 160 when Query Store is in READ_WRITE mode. The feature ID in sys.query_store_plan_feedback is 2. The database-scoped configuration is CE_FEEDBACK.

7 Parameter Sensitive Plan Optimization Intermediate

Parameter Sensitive Plan (PSP) Optimization addresses parameter sniffing at the engine level. When a parameterized query is executed for the first time, SQL Server compiles and caches one execution plan based on the parameter values used in that first execution. If the data distribution is nonuniform, a plan that is optimal for one parameter value may be very poor for another.

According to Microsoft Learn, PSP Optimization is part of the Intelligent Query Processing family introduced in SQL Server 2022. It enables the query optimizer to cache multiple execution plans for the same query, each optimized for different parameter value ranges. SQL Server selects the most appropriate plan at runtime based on the incoming parameter values. This eliminates the class of parameter sniffing problems where a single cached plan is consistently wrong for a subset of parameter values.

PSP Optimization is ON by default at compatibility level 160. It requires no Query Store enablement to function, though Query Store captures the plans it generates. The database-scoped configuration is PARAMETER_SENSITIVE_PLAN_OPTIMIZATION.

In SQL Server 2025, PSP Optimization is extended to support DML statements (DELETE, INSERT, MERGE, and UPDATE) in addition to SELECT queries. It also gains expanded tempdb support and additional handling for multiple eligible predicates on the same table. These extensions require compatibility level 170.

8 Optimized Plan Forcing Beginner

Optimized Plan Forcing is a Query Store feature that reduces the compilation cost of forced plans. When a plan is forced in Query Store, SQL Server normally goes through the full optimization process and then validates that the result matches the forced plan. This is expensive for complex queries.

With Optimized Plan Forcing, SQL Server stores an optimization replay script as part of the compressed plan XML in Query Store during the first successful forced plan compilation. On subsequent executions, SQL Server replays the stored optimization steps rather than rerunning the full optimization, reducing compilation overhead significantly.

Optimized Plan Forcing is ON by default and requires no compatibility level change beyond Query Store being active. The database-scoped configuration is OPTIMIZED_PLAN_FORCING.

9 What SQL Server 2025 Adds to IQP Intermediate

SQL Server 2025 introduces compatibility level 170 as the default for new databases. Two new IQP learning features debut in SQL Server 2025, and several existing features receive significant enhancements at compatibility level 170.

New: Optional Parameter Plan Optimization (OPPO)

According to Microsoft Learn, Optional Parameter Plan Optimization is introduced in SQL Server 2025 and is ON by default at compatibility level 170. It targets a specific query pattern that PSP Optimization did not address: optional parameters where the predicate is WHERE Column = @P OR @P IS NULL. This pattern means “if the parameter has a value, filter by it; if null, return all rows.” A single plan cannot be optimal for both cases.

OPPO generates multiple execution plans for this pattern and selects the appropriate plan at runtime based on whether the parameter is NULL. The database-scoped configuration is OPTIONAL_PARAMETER_OPTIMIZATION. It is ON by default at compatibility level 170. To disable for a specific query, use the DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION query hint.

New: CE Feedback for Expressions

According to Microsoft Learn, CE Feedback for Expressions extends the CE Feedback framework introduced in SQL Server 2022. The original CE Feedback feature learns from repeated executions of the same query. CE Feedback for Expressions learns from expression-level patterns across different queries that share common subexpressions, even when the overall query structures are different.

The feature uses expression fingerprints, signatures generated from logical expressions such as filters and joins within a query plan, to identify and apply CE corrections that benefit multiple queries sharing similar expression patterns. The database-scoped configuration is CE_FEEDBACK_FOR_EXPRESSIONS.

Enhanced: PSP Optimization at compatibility level 170

According to Microsoft Learn, PSP Optimization in SQL Server 2025 at compatibility level 170 adds support for DML statements (DELETE, INSERT, MERGE, and UPDATE) in addition to SELECT queries. It also gains expanded tempdb support and additional consideration for multiple eligible predicates on the same table.

Enhanced: DOP Feedback in SQL Server 2025

According to Microsoft Learn, DOP Feedback is available for queries operating at compatibility level 160 or higher. In Azure SQL Managed Instance with the SQL Server 2025 or Always-up-to-date update policy, DOP Feedback receives additional enhancements. The database-scoped configuration remains DOP_FEEDBACK.

SQL Server 2025 IQP Addition	Default	Compat Level	Config Name
Optional Parameter Plan Optimization (OPPO)	ON at compat 170	170	OPTIONAL_PARAMETER_OPTIMIZATION
CE Feedback for Expressions	ON (Query Store required)	170	CE_FEEDBACK_FOR_EXPRESSIONS
PSP Optimization (DML support)	ON at compat 170	170	PARAMETER_SENSITIVE_PLAN_OPTIMIZATION

10 Audit Script: Check All IQP Settings Across Every Database Beginner

Run this script on the SQL Server instance to get a complete picture of IQP configuration across all user databases. It reports compatibility level, Query Store state, and every relevant database-scoped configuration value in a single result set.

-- IQP Complete Audit: All user databases, all settings
-- Run on the SQL Server instance (master context)
-- Returns one row per setting per database

DROP TABLE IF EXISTS #IQPAudit;
CREATE TABLE #IQPAudit
(
    DatabaseName        SYSNAME,
    CompatLevel         INT,
    QueryStoreState     NVARCHAR(60),
    ConfigName          SYSNAME,
    ConfigValue         SQL_VARIANT,
    ConfigState         NVARCHAR(10)
);

DECLARE @db     SYSNAME;
DECLARE @sql    NVARCHAR(MAX);

DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases
    WHERE database_id > 4
      AND state_desc = 'ONLINE'
      AND source_database_id IS NULL;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'
    USE ' + QUOTENAME(@db) + N';
    INSERT INTO #IQPAudit
    SELECT
        DB_NAME(),
        d.compatibility_level,
        COALESCE(qso.actual_state_desc, N''NOT ENABLED''),
        dsc.name,
        dsc.value,
        CASE TRY_CONVERT(INT, dsc.value)
            WHEN 1 THEN ''ON''
            WHEN 0 THEN ''OFF''
            ELSE CONVERT(NVARCHAR(10), dsc.value)
        END
    FROM sys.databases d
    CROSS JOIN sys.database_query_store_options qso
    JOIN sys.database_scoped_configurations dsc ON 1 = 1
    WHERE d.database_id = DB_ID()
      AND dsc.name IN (
          N''BATCH_MODE_MEMORY_GRANT_FEEDBACK'',
          N''ROW_MODE_MEMORY_GRANT_FEEDBACK'',
          N''MEMORY_GRANT_FEEDBACK_PERSISTENCE'',
          N''MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT'',
          N''DOP_FEEDBACK'',
          N''CE_FEEDBACK'',
          N''PARAMETER_SENSITIVE_PLAN_OPTIMIZATION'',
          N''OPTIMIZED_PLAN_FORCING''
      );
    ';

    BEGIN TRY
        EXEC sys.sp_executesql @sql;
    END TRY
    BEGIN CATCH
        PRINT 'Could not audit ' + @db + ': ' + ERROR_MESSAGE();
    END CATCH;

    FETCH NEXT FROM db_cursor INTO @db;
END;

CLOSE db_cursor; DEALLOCATE db_cursor;

-- Results with assessment
SELECT
    DatabaseName,
    CompatLevel,
    QueryStoreState,
    ConfigName,
    ConfigState,
    CASE
        WHEN ConfigName = 'DOP_FEEDBACK'
         AND ConfigState = 'OFF'
            THEN 'ACTION NEEDED: Enable with ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON'
        WHEN ConfigName IN ('MEMORY_GRANT_FEEDBACK_PERSISTENCE',
                            'MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT',
                            'CE_FEEDBACK')
         AND QueryStoreState != 'READ_WRITE'
            THEN 'WARNING: Feature enabled but Query Store is not READ_WRITE'
        WHEN ConfigState = 'ON'
            THEN 'OK'
        WHEN ConfigState = 'OFF'
            THEN 'OFF: Verify this is intentional'
        ELSE 'Review'
    END AS Assessment
FROM #IQPAudit
ORDER BY DatabaseName, ConfigName;

11 Enable Script: Configure All IQP Features Per Database Beginner

Run the following on a specific database after verifying compatibility level and reviewing the audit results. Enabling compatibility level 160 on an existing database can change optimizer behavior beyond IQP settings. Run application regression testing before applying to production.

-- SQL Server 2022 IQP Full Enable Script
-- Run per database after testing in non-production first
-- Replace YourDatabase with the target database name

USE master;
GO

-- Step 1: Set compatibility level to 160 for full SQL Server 2022 IQP feature set
-- WARNING: Test for query plan regressions before applying to production
ALTER DATABASE YourDatabase SET COMPATIBILITY_LEVEL = 160;
GO

-- Step 2: Enable and configure Query Store (required for all learning features)
ALTER DATABASE YourDatabase
SET QUERY_STORE = ON (
    OPERATION_MODE          = READ_WRITE,
    QUERY_CAPTURE_MODE      = AUTO,
    MAX_STORAGE_SIZE_MB     = 1024,
    INTERVAL_LENGTH_MINUTES = 60
);
GO

USE YourDatabase;
GO

-- Step 3: Memory Grant Feedback (all three layers)
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK           = ON;
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK             = ON;
ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE          = ON;
ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT     = ON;
GO

-- Step 4: Cardinality Estimation Feedback (ON by default at compat 160, verify here)
ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK                                = ON;
GO

-- Step 5: Parameter Sensitive Plan Optimization (ON by default at compat 160)
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION      = ON;
GO

-- Step 6: Optimized Plan Forcing (ON by default)
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING                    = ON;
GO

-- Step 7: DOP Feedback (OFF by default in SQL 2022 - must be explicitly enabled)
-- Pilot this feature on one database before broad rollout
ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK                              = ON;
GO

-- Verification: Confirm all settings are as expected
SELECT
    name,
    CASE value WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE CONVERT(VARCHAR, value) END AS State
FROM sys.database_scoped_configurations
WHERE name IN (
    'BATCH_MODE_MEMORY_GRANT_FEEDBACK',
    'ROW_MODE_MEMORY_GRANT_FEEDBACK',
    'MEMORY_GRANT_FEEDBACK_PERSISTENCE',
    'MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT',
    'DOP_FEEDBACK',
    'CE_FEEDBACK',
    'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION',
    'OPTIMIZED_PLAN_FORCING'
)
ORDER BY name;
GO

12 Verify Feedback Is Actually Working Intermediate

A feature being enabled does not mean SQL Server has found an eligible query or applied feedback. The features only activate for repeating queries where the optimizer’s original assumptions were demonstrably wrong. On a new database or one without a recurring workload, feedback entries may be absent initially.

-- Check all feedback stored in Query Store
-- Run in the target database
-- feature_id values: 1 = Memory Grant Feedback, 2 = CE Feedback, 3 = DOP Feedback
USE YourDatabase;

SELECT
    qspf.plan_id,
    qspf.feature_desc,
    qspf.feature_id,
    qspf.state_desc,           -- Tracking, Verifying, Verified, Regressed
    qspf.feedback_data,        -- JSON with the adjustment detail
    qspf.create_time,
    qspf.last_updated_time,
    LEFT(qt.query_sql_text, 120) AS QueryText
FROM sys.query_store_plan_feedback  qspf
JOIN sys.query_store_plan           qsp ON qspf.plan_id      = qsp.plan_id
JOIN sys.query_store_query          qsq ON qsp.query_id      = qsq.query_id
JOIN sys.query_store_query_text     qt  ON qsq.query_text_id = qt.query_text_id
ORDER BY qspf.last_updated_time DESC;

-- Summary by feature and state
SELECT
    feature_desc,
    state_desc,
    COUNT(*)                    AS FeedbackCount,
    MAX(last_updated_time)      AS MostRecent
FROM sys.query_store_plan_feedback
GROUP BY feature_desc, state_desc
ORDER BY feature_desc, state_desc;

The state_desc values in sys.query_store_plan_feedback map to the following stages:

• Tracking: The feature has identified the query as a candidate and is observing executions.
• Verifying: An adjustment has been applied to a test execution and SQL Server is evaluating whether it helped.
• Verified: The adjustment was confirmed as beneficial and is persisted. This adjustment survives restarts.
• Regressed: The adjustment caused a regression. SQL Server reverted to the previous state. This is expected and normal behavior, not an error.

13 Recommended Rollout Order Beginner

The following phased approach minimizes risk when enabling IQP features on an existing production environment. Each phase validates before proceeding.

14 Side-by-Side: SQL Server 2022 vs SQL Server 2025 Beginner

IQP Feature	SQL Server 2022 (Compat 160)	SQL Server 2025 (Compat 170)
Memory Grant Feedback (Batch + Row Mode)	ON by default	ON by default
Memory Grant Feedback Persistence	ON, requires Query Store	ON, requires Query Store
Memory Grant Percentile Feedback	ON, requires Query Store	ON, requires Query Store
DOP Feedback	OFF by default, must enable manually	Available, requires Query Store
Cardinality Estimation (CE) Feedback	ON by default, requires Query Store	ON by default, requires Query Store
CE Feedback for Expressions	Not available	NEW in SQL Server 2025. Requires Query Store.
Parameter Sensitive Plan (PSP) Optimization	ON by default. SELECT only.	ON by default. SELECT + DML (DELETE, INSERT, MERGE, UPDATE).
Optional Parameter Plan Optimization (OPPO)	Not available	NEW in SQL Server 2025. ON by default at compat 170.
Optimized Plan Forcing	ON by default	ON by default
Adaptive Joins, Interleaved Execution, Batch Mode on Rowstore	ON by default	ON by default
Default compatibility level for new databases	160	170

References

• Microsoft Learn: Intelligent Query Processing in SQL Databases (master IQP reference)
• Microsoft Learn: Intelligent Query Processing Feature Details
• Microsoft Learn: DOP Feedback
• Microsoft Learn: Cardinality Estimation Feedback
• Microsoft Learn: CE Feedback for Expressions (SQL Server 2025)
• Microsoft Learn: Parameter Sensitive Plan Optimization
• Microsoft Learn: Optional Parameter Plan Optimization (SQL Server 2025)
• Microsoft Learn: ALTER DATABASE SCOPED CONFIGURATION (all IQP config names)
• Microsoft Learn: sys.query_store_plan_feedback
• SQLYARD: MAXDOP and DOP Feedback in SQL Server 2022: The Complete Guide
• SQLYARD: SQL Server Query Store: The Complete Guide
• SQLYARD: SQL Server Parameter Sniffing: Causes, Detection, and Fixes