Introduction

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.

What is compatibility?

What each QIP feature means?

IQP FeatureSupported in Azure SQL DatabaseSupported in SQL Server 2022 (16.x)Description
Adaptive Joins (Batch Mode)Yes, starting with database compatibility level 140Yes, starting in SQL Server 2017 (14.x) with database compatibility level 140Adaptive joins dynamically select a join type during runtime based on actual input rows.
Approximate Count DistinctYesYes, 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 PercentileYes, starting with database compatibility level 110Yes, starting in SQL Server 2022 (16.x) with compatibility level 110Quickly 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 RowstoreYes, starting with database compatibility level 150Yes, starting in SQL Server 2019 (15.x) with compatibility level 150Provide batch mode for CPU-bound relational DW workloads without requiring columnstore indexes.
Cardinality estimation (CE) feedbackYes, starting with database compatibility level 160Yes, starting in SQL Server 2022 (16.x) with compatibility level 160Automatically 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.
Degrees of Parallelism (DOP) feedbackYes, in Preview, starting with database compatibility level 160Yes, starting in SQL Server 2022 (16.x) with compatibility level 160Automatically 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 ExecutionYes, starting with database compatibility level 140Yes, starting in SQL Server 2017 (14.x) with database compatibility level 140Uses 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 140Yes, starting in SQL Server 2017 (14.x) with database compatibility level 140If 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 150Yes, starting in SQL Server 2019 (15.x) with database compatibility level 150If 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 databasesYes, starting with SQL Server 2022 (16.x)) with database compatibility level 140Addresses existing limitations of memory grant feedback in a non-intrusive way by incorporating past query execution to refine feedback.
Memory Grant feedback persistenceYes, enabled on all databasesYes, starting with SQL Server 2022 (16.x)) with database compatibility level 140Provides new functionality to persist memory grant feedback. Requires Query Store to be enabled for the database and in READ_WRITE mode.
CE feedback persistenceYes, starting with database compatibility level 160Yes, starting with SQL Server 2022 (16.x)) with database compatibility level 160Requires Query Store to be enabled for the database and in READ_WRITE mode.
DOP feedback persistenceYes, in Preview, starting with database compatibility level 160Yes, starting with SQL Server 2022 (16.x)) with database compatibility level 160Requires Query Store to be enabled for the database and in READ_WRITE mode.
Optimized plan forcingYesYes, 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 InliningYes, starting with database compatibility level 150Yes, starting in SQL Server 2019 (15.x) with database compatibility level 150Scalar UDFs are transformed into equivalent relational expressions that are “inlined” into the calling query, often resulting in significant performance gains.
Parameter Sensitivity Plan optimizationYes, starting with database compatibility level 160Yes, starting in SQL Server 2022 (16.x) with database compatibility level 160Parameter Sensitive Plan optimization addresses the scenario where a single cached plan for a parameterized query is not optimal for all possible incoming parameter values, for example non-uniform data distributions.
Table Variable Deferred CompilationYes, starting with database compatibility level 150Yes, starting in SQL Server 2019 (15.x) with database compatibility level 150Uses the actual cardinality of the table variable encountered on first compilation instead of a fixed guess.

Problem

Enable IQP features based on SQL Server version and edition

Solution

https://github.com/steverezhener/DBA/blob/master/Procedures/bestpractices.usp_enable_database_intelligent_query_processing.sql