The intelligent query processing (IQP) feature family includes features with broad impact that improve the performance of existing workloads with minimal implementation effort to adopt. The following graphic details the family of IQP features and when they were first introduced for SQL Server. All IQP features are available in Azure SQL Managed Instance and Azure SQL Database. Some features depend on the database's compatibility level.

For demos and sample code of intelligent query processing (IQP) features, see the GitHub sample repository .

You can make workloads automatically eligible for intelligent query processing by enabling the applicable database compatibility level for the database. You can set this using Transact-SQL. For example:

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 170;
The following table details all intelligent query processing features, along with any requirement they have for database compatibility level. For complete details on all IQP features, including release notes and more in-depth descriptions, see Intelligent query processing features in detail.
IQP features for Azure SQL Database and SQL Server 2025
IQP Feature
Supported in Azure SQL Database
Supported in SQL Server 2025 (17.x)
Description
Optional parameter plan optimization (OPPO)
Yes, starting with database compatibility level 170
Yes, starting with SQL Server 2025 (17.x) with database compatibility level 170
Leverages the adaptive plan optimization (Multiplan) infrastructure that was introduced with the Parameter Sensitive Plan Optimization (PSPO) improvement, which generates multiple plans from a single statement. The feature can choose a more optimal plan at runtime based on whether a parameter is NULL OR NOT NULL, which improves performance for queries that could otherwise default to suboptimal performance for such query patterns.
Cardinality estimation (CE) feedback for expressions
Yes, starting with database compatibility level 170
Yes, starting with SQL Server 2025 (17.x) with database compatibility level 160
Extends CE feedback to improve cardinality estimates for repeating expressions across queries by learning from previous executions and automatically applying appropriate CE model choices to future executions of those expressions.
OPTIMIZED_SP_EXECUTESQL
Yes, starting with SQL Server 2025 (17.x)
Effectively reduces the impact of compilation storms. A compilation storm is a situation where a large number of queries is being compiled simultaneously, leading to performance issues and resource contention. Enable this feature to allow invocations of sp_executesql to behave like objects such as stored procedures and triggers from a compilation perspective.
IQP Feature
Supported in Azure SQL Database
Supported in SQL Server 2022 (16.x) and later versions
Description
Adaptive Joins (Batch Mode)
Yes, starting with database compatibility level 140
Yes, starting in SQL Server 2017 (14.x) with database compatibility level 140
Adaptive joins dynamically select a join type during runtime based on actual input rows.
Approximate Count Distinct
Yes, starting in SQL Server 2019 (15.x)
Provide approximate COUNT DISTINCT for big data scenarios with the benefit of high performance and a low memory footprint.
Approximate Percentile
Yes, starting with database compatibility level 110
Yes, starting in SQL Server 2022 (16.x) with compatibility level 110
Quickly compute percentiles for a large dataset with acceptable rank-based error bounds to help make rapid decisions by using approximate percentile aggregate functions.
Batch Mode on Rowstore
Yes, starting with database compatibility level 150
Yes, starting in SQL Server 2019 (15.x) with compatibility level 150
Provide batch mode for CPU-bound relational DW workloads without requiring columnstore indexes.
Cardinality estimation (CE) feedback
Yes, starting with database compatibility level 160
Yes, starting in SQL Server 2022 (16.x) with compatibility level 160
Automatically adjusts cardinality estimates for repeating queries to optimize workloads where inefficient CE assumptions cause poor query performance. CE feedback will identify and use a model assumption that better fits a given query and data distribution to improve query execution plan quality.
Degree of parallelism (DOP) feedback
Yes, starting with database compatibility level 160
Yes, starting with database compatibility level 160
Automatically adjusts degree of parallelism for repeating queries to optimize for workloads where inefficient parallelism can cause performance issues. Requires Query Store to be enabled.
Interleaved Execution
Yes, starting with database compatibility level 140
Yes, starting in SQL Server 2017 (14.x) with database compatibility level 140
Uses the actual cardinality of the multi-statement table valued function encountered on first compilation instead of a fixed guess.
Memory grant feedback (Batch Mode)
Yes, starting with database compatibility level 140
Yes, starting in SQL Server 2017 (14.x) with database compatibility level 140
If a batch mode query has operations that spill to disk, add more memory for consecutive executions. If a query wastes > 50% of the memory allocated to it, reduce the memory grant size for consecutive executions.
Memory grant feedback (Row Mode)
Yes, starting with database compatibility level 150
Yes, starting in SQL Server 2019 (15.x) with database compatibility level 150
If a row mode query has operations that spill to disk, add more memory for consecutive executions. If a query wastes > 50% of the memory allocated to it, reduce the memory grant size for consecutive executions.
Memory grant feedback (Percentile)
Yes, enabled on all databases
Yes, starting with SQL Server 2022 (16.x)) with database compatibility level 140
Addresses existing limitations of memory grant feedback in a non-intrusive way by incorporating past query execution to refine feedback.
Memory Grant feedback persistence
Yes, enabled on all databases
Yes, starting with SQL Server 2022 (16.x)) with database compatibility level 140
Provides new functionality to persist memory grant feedback. Requires Query Store to be enabled for the database and in READ_WRITE mode.
CE feedback persistence
Yes, starting with database compatibility level 160
Yes, starting with SQL Server 2022 (16.x)) with database compatibility level 160
Requires Query Store to be enabled for the database and in READ_WRITE mode.
Optimized plan forcing with Query Store
Yes, starting with SQL Server 2022 (16.x)).
Reduces compilation overhead for repeating forced queries. For more information, see Optimized plan forcing with Query Store.
Scalar UDF Inlining
Yes, starting with database compatibility level 150
Yes, starting in SQL Server 2019 (15.x) with database compatibility level 150
Scalar UDFs are transformed into equivalent relational expressions that are "inlined" into the calling query, often resulting in significant performance gains.
Parameter Sensitive Plan optimization
Yes, starting with database compatibility level 160
Yes, starting in SQL Server 2022 (16.x) with database compatibility level 160
Parameter Sensitive Plan optimization addresses the scenario where a single cached plan for a parameterized query isn't optimal for all possible incoming parameter values, for example non-uniform data distributions.
Table Variable Deferred Compilation
Yes, starting with database compatibility level 150
Yes, starting in SQL Server 2019 (15.x) with database compatibility level 150
Uses the actual cardinality of the table variable encountered on first compilation instead of a fixed guess.
Adaptive Joins (Batch Mode)
Yes, starting with database compatibility level 140
Adaptive joins dynamically select a join type during runtime based on actual input rows.
Approximate Count Distinct
Provide approximate COUNT DISTINCT for big data scenarios with the benefit of high performance and a low memory footprint.
Approximate Percentile
Yes, starting with database compatibility level 110
Quickly compute percentiles for a large dataset with acceptable rank-based error bounds to help make rapid decisions by using approximate percentile aggregate functions.
Batch Mode on Rowstore
Yes, starting with database compatibility level 150
Provide batch mode for CPU-bound relational DW workloads without requiring columnstore indexes.
Cardinality estimation (CE) feedback
Yes, starting with database compatibility level 160
Automatically adjusts cardinality estimates for repeating queries to optimize workloads where inefficient CE assumptions cause poor query performance. CE feedback will identify and use a model assumption that better fits a given query and data distribution to improve query execution plan quality.
Cardinality estimation (CE) feedback for expressions
Yes, starting with database compatibility level 160 in Azure SQL Managed Instance, with the SQL Server 2025 or Always-up-to-date update policy. No, for the SQL Server 2022 update policy.
Extends CE feedback to improve cardinality estimates for repeating expressions across queries by learning from previous executions and automatically applying appropriate CE model choices to future executions of those expressions.
Degree of parallelism (DOP) feedback
Yes, starting with database compatibility level 160 in Azure SQL Managed Instance with the SQL Server 2025 or Always-up-to-date update policy. No, for the SQL Server 2022 update policy.
Automatically adjusts degree of parallelism for repeating queries to optimize for workloads where inefficient parallelism can cause performance issues. Requires Query Store to be enabled.
Interleaved Execution
Yes, starting with database compatibility level 140
Uses the actual cardinality of the multi-statement table valued function encountered on first compilation instead of a fixed guess.
Memory grant feedback (Batch Mode)
Yes, starting with database compatibility level 140
If a batch mode query has operations that spill to disk, add more memory for consecutive executions. If a query wastes > 50% of the memory allocated to it, reduce the memory grant size for consecutive executions.
Memory grant feedback (Row Mode)
Yes, starting with database compatibility level 150
If a row mode query has operations that spill to disk, add more memory for consecutive executions. If a query wastes > 50% of the memory allocated to it, reduce the memory grant size for consecutive executions.
Memory grant feedback (Percentile)
Yes, starting with database compatibility level 160
Addresses existing limitations of memory grant feedback in a non-intrusive way by incorporating past query execution to refine feedback.
Memory Grant, CE, and DOP feedback persistence
Yes, starting with database compatibility level 160
Provides new functionality to persist memory grant feedback. CE and DOP feedback is always persisted. Requires Query Store to be enabled for the database and in READ_WRITE mode.
Optional parameter plan optimization (OPPO)
Yes, starting with database compatibility level 170 in Azure SQL Managed Instance with the SQL Server 2025 or Always-up-to-date update policy. No, for the SQL Server 2022 update policy.
Leverages the adaptive plan optimization (Multiplan) infrastructure that was introduced with the Parameter Sensitive Plan Optimization (PSPO) improvement, which generates multiple plans from a single statement. The feature can choose a more optimal plan at runtime based on whether a parameter is NULL OR NOT NULL, which improves performance for queries that could otherwise default to suboptimal performance for such query patterns.
Optimized plan forcing with Query Store
Yes, with the SQL Server 2025 or Always-up-to-date update policy. No, for the SQL Server 2022 update policy.
Reduces compilation overhead for repeating forced queries. For more information, see Optimized plan forcing with Query Store.
Parameter Sensitive Plan optimization
Yes, starting with database compatibility level 160
Parameter Sensitivity Plan Optimization addresses the scenario where a single cached plan for a parameterized query isn't optimal for all possible incoming parameter values, for example non-uniform data distributions.
Scalar UDF Inlining
Yes, starting with database compatibility level 150
Scalar UDFs are transformed into equivalent relational expressions that are "inlined" into the calling query, often resulting in significant performance gains.
Table Variable Deferred Compilation
Yes, starting with database compatibility level 150
Uses the actual cardinality of the table variable encountered on first compilation instead of a fixed guess.
Adaptive Joins (Batch Mode)
Yes, starting in SQL Server 2017 (14.x) with database compatibility level 140
Adaptive joins dynamically select a join type during runtime based on actual input rows.
Approximate Count Distinct
Provide approximate COUNT DISTINCT for big data scenarios with the benefit of high performance and a low memory footprint.
Batch Mode on Rowstore
Yes, starting with database compatibility level 150
Provide batch mode for CPU-bound relational DW workloads without requiring columnstore indexes.
Interleaved Execution
Yes, starting with database compatibility level 140
Use the actual cardinality of the multi-statement table valued function encountered on first compilation instead of a fixed guess.
Memory grant feedback (Batch Mode)
Yes, starting with database compatibility level 140
If a batch mode query has operations that spill to disk, add more memory for consecutive executions. If a query wastes > 50% of the memory allocated to it, reduce the memory grant size for consecutive executions.
Memory grant feedback (Row Mode)
Yes, starting with database compatibility level 150
If a row mode query has operations that spill to disk, add more memory for consecutive executions. If a query wastes > 50% of the memory allocated to it, reduce the memory grant size for consecutive executions.
Scalar UDF Inlining
Yes, starting with database compatibility level 150
Scalar UDFs are transformed into equivalent relational expressions that are "inlined" into the calling query, often resulting in significant performance gains.
Table Variable Deferred Compilation
Yes, starting with database compatibility level 150
Use the actual cardinality of the table variable encountered on first compilation instead of a fixed guess.
Adaptive Joins (Batch Mode)
Yes, starting in SQL Server 2017 (14.x) with database compatibility level 140
Adaptive joins dynamically select a join type during runtime based on actual input rows.
Approximate Count Distinct
Provide approximate COUNT DISTINCT for big data scenarios with the benefit of high performance and a low memory footprint.
Interleaved Execution
Yes, starting with database compatibility level 140
Use the actual cardinality of the multi-statement table valued function encountered on first compilation instead of a fixed guess.
Memory grant feedback (Batch Mode)
Yes, starting with database compatibility level 140
If a batch mode query has operations that spill to disk, add more memory for consecutive executions. If a query wastes > 50% of the memory allocated to it, reduce the memory grant size for consecutive executions.
Query Store requirement
Several of the suite of intelligent query processing features require the Query Store to be enabled in order to benefit the user database. To enable the Query Store, see Enable the Query Store.
IQP feature
Requires Query Store to be enabled and READ_WRITE
Execution modes
Query processing architecture guide
Logical and physical showplan operator reference
What's new in SQL Server 2017
What's new in SQL Server 2019
What's new in SQL Server 2022
Memory grant feedback
Demonstrating Intelligent Query Processing
Constant Folding and Expression Evaluation
Intelligent query processing demos on GitHub
Performance Center for SQL Server Database Engine and Azure SQL Database
Monitor performance by using the Query Store
Best practices for monitoring workloads with Query Store