Skip to content

Latest commit

 

History

History
140 lines (116 loc) · 22.4 KB

intelligent-query-processing.md

File metadata and controls

140 lines (116 loc) · 22.4 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic ms.custom monikerRange
Intelligent query processing
Intelligent query processing features to improve query performance in SQL Server, Azure SQL Managed Instance, and Azure SQL Database.
MikeRayMSFT
mikeray
derekw
01/19/2024
sql
performance
conceptual
ignite-2024
=azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

Intelligent query processing in SQL databases

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance FabricSQLDB]

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 [!INCLUDE ssazuremi-md] and [!INCLUDE ssazure-sqldb]. Some features depend on the database's compatibility level.

:::image type="content" source="media/iqp-feature-family.svg" alt-text="Diagram of the Intelligent Query Processing family of features and when they were first introduced to SQL Server.":::

Watch this video for an overview of intelligent query processing:

[!VIDEO https://learn-video.azurefd.net/vod/player?show=data-exposed&ep=how-sql-is-getting-smarter-intelligent-query-processing-2017-to-today-data-exposed]

For demos and sample code of intelligent query processing (IQP) features on GitHub, visit https://aka.ms/IQPDemos.

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 [!INCLUDE tsql]. For example:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 160;

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 (IQP) feature details.

IQP features for [!INCLUDE ssazure-sqldb] and [!INCLUDE sssql22-md]

IQP Feature Supported in [!INCLUDE ssazure-sqldb] Supported in [!INCLUDE sssql22-md] Description
Adaptive Joins (Batch Mode) Yes, starting with database compatibility level 140 Yes, starting in [!INCLUDE ssSQL17] with database compatibility level 140 Adaptive joins dynamically select a join type during runtime based on actual input rows.
Approximate Count Distinct Yes Yes, starting in [!INCLUDE sql-server-2019] 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 [!INCLUDE sql-server-2022] 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 [!INCLUDE sql-server-2019] 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 [!INCLUDE sql-server-2022] 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.
Degrees of Parallelism (DOP) feedback Yes, in Preview, starting with database compatibility level 160 Yes, starting in [!INCLUDE sql-server-2022] with 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 [!INCLUDE ssSQL17] 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 [!INCLUDE ssSQL17] 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 [!INCLUDE sql-server-2019] 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 [!INCLUDE sql-server-2022]) 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 [!INCLUDE sql-server-2022]) 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 [!INCLUDE sql-server-2022]) with database compatibility level 160 Requires Query Store to be enabled for the database and in READ_WRITE mode.
DOP feedback persistence Yes, in Preview, starting with database compatibility level 160 Yes, starting with [!INCLUDE sql-server-2022]) with database compatibility level 160 Requires Query Store to be enabled for the database and in READ_WRITE mode.
Optimized plan forcing Yes Yes, starting with [!INCLUDE sql-server-2022]). 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 [!INCLUDE sql-server-2019] 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 Sensitivity Plan optimization Yes, starting with database compatibility level 160 Yes, starting in [!INCLUDE sql-server-2022] with database compatibility level 160 Parameter 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 Compilation Yes, starting with database compatibility level 150 Yes, starting in [!INCLUDE sql-server-2019] with database compatibility level 150 Uses the actual cardinality of the table variable encountered on first compilation instead of a fixed guess.

IQP features for [!INCLUDE ssazuremi]

| IQP Feature | Supported in [!INCLUDE ssazuremi] | Description | | ---------------- | ------- | ------- | ---------------- | | 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 | Yes| 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. | | Degrees of Parallelism (DOP) feedback | No | 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. | | Optimized plan forcing | No | 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 | Scalar UDFs are transformed into equivalent relational expressions that are "inlined" into the calling query, often resulting in significant performance gains.| | Parameter Sensitivity 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 is not optimal for all possible incoming parameter values, for example non-uniform data distributions. | | 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.|

IQP features for [!INCLUDE sssql19-md]

IQP Feature Supported in [!INCLUDE sssql19-md] Description
Adaptive Joins (Batch Mode) Yes, starting in [!INCLUDE ssSQL17] with database compatibility level 140 Adaptive joins dynamically select a join type during runtime based on actual input rows.
Approximate Count Distinct Yes 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.

IQP features for [!INCLUDE sssql17-md]

IQP Feature Supported in [!INCLUDE sssql17-md] Description
Adaptive Joins (Batch Mode) Yes, starting in [!INCLUDE ssSQL17] with database compatibility level 140 Adaptive joins dynamically select a join type during runtime based on actual input rows.
Approximate Count Distinct Yes 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
Adaptive Joins (Batch Mode) No
Approximate Count Distinct No
Approximate Percentile No
Batch Mode on Rowstore No
Cardinality estimation (CE) feedback Yes
Degrees of Parallelism (DOP) feedback Yes
Interleaved Execution No
Memory grant feedback (Batch Mode) No
Memory grant feedback (Row Mode) No
Memory grant feedback (Percentile and Persistence mode) Yes
Optimized plan forcing Yes
Scalar UDF Inlining No
Parameter Sensitivity Plan Optimization No, but recommended
Table Variable Deferred Compilation No

Related content

For complete details on all IQP features, including release notes and more in-depth descriptions, see Intelligent query processing (IQP) feature details.