Azure SQL Blog

5 MIN READ

Intelligent Query Processing Q&A

Microsoft

Apr 12, 2019

In this blog post we’ll share intelligent query processing (IQP) customer questions and answers. This is intended to supplement the more in-depth coverage in the Books Online topic “Intelligent query processing in SQL databases”.

If you have additional questions not covered here or general feedback or test results to share, please email us at IntelligentQP@microsoft.com.

What exactly is Intelligent Query Processing (IQP)?

We define the Intelligent Query Processing (IQP) feature family as including query processing and execution features with broad impact that improve the performance of existing workloads with minimal implementation effort to adopt.

We prioritize scenarios where a large population of customers are impacted (for example – there is significant adoption of features like table variables, multi-statement table valued functions, and T-SQL scalar UDF functions). And regarding implementation effort to adopt, most of the features in IQP only require a change to the latest database compatibility level.

What specific problems are solved by IQP?

The following table describes each feature, problem area, and description on how it can help:

Feature	Problem Area	How this feature helps
Adaptive Joins (Batch Mode)	Cardinality misestimates for join inputs can lead to incorrect physical join algorithm selection.	Adaptive joins dynamically select a join type during runtime based on actual input rows. This allows execution-time selection of either a nested loop or hash join algorithm.
Approximate Count Distinct	COUNT DISTINCT operations for big data scenarios involving many distinct values can result in very large memory grant sizes that can impact concurrency.	For scenarios where exact values are not required, APPROX_COUNT_DISTINCT improves performance and concurrency by using a very small memory footprint.
Batch Mode on Rowstore	CPU-bound relational data warehouse and analytic workloads may not be able to leverage columnstore indexes due to OLTP-overhead, lack of vendor support or other limitations. Without columnstore indexes, these workloads cannot benefit from batch execution mode.	This feature enables batch mode for analytic workloads without requiring columnstore indexes.
Interleaved Execution	Multi-statement table valued functions use a fixed guess for the cardinality estimate. If the actual number of rows is much higher than the guessed value, performance of downstream operations can suffer.	This feature uses the actual cardinality of the multi-statement table valued function encountered on first compilation, benefiting downstream operations.
Memory Grant Feedback (Batch and Row Mode)	Cardinality estimation under-estimation issues can result in operations spilling to disk when we have not requested enough memory. Conversely, cardinality estimation over-estimates can result in wasted memory and can impact workload concurrency.	If a query has operations that spill to disk, memory grant feedback adds more memory for consecutive executions. If a query operator wastes more than 50% of the memory allocated to it, memory grant feedback reduces the memory grant size for consecutive executions.
T-SQL Scalar UDF Inlining	Queries referencing T-SQL scalar UDFs use iterative invocation, lack costing and force serial execution.	T-SQL 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	Table variables use a fixed guess for the cardinality estimate. If the actual number of rows is much higher than the guessed value, performance of downstream operations can suffer.	This feature compiles a plan with the actual cardinality of the table variable encountered on first compilation instead of a fixed guess.

How should I safely prepare for upgrades?

Query optimizer changes are gated to the latest database compatibility level. This allows you to upgrade to the most recent version while running at a lower compatibility level until you are ready.

For pre-existing databases running at lower compatibility levels, the recommended workflow for upgrading the query processor to a higher compatibility level is detailed in the article Change the Database Compatibility Mode and Use the Query Store. We also recently introduced Query Tuning Assistant, described here: Upgrading Databases by using the Query Tuning Assistant.

Will my workload be faster with IQP?

We anticipate IQP features will improve performance, but as is the case with any query processing changes, there is always a possibility of regression. If a particular feature is causing a regression for your workload, you can disable the feature at the statement or database level.

What can I do if I encounter performance regressions?

Whenever a new QP feature is added, there is a risk of regression. Each of the IQP features provides options for disabling the individual (or all) features:

Lower the database compatibility level (this is the least granular approach and you may miss out on benefits from other IQP features)
Disable the feature via database scoped configuration
Disable the feature for a specific statement using a query hint
Force a prior plan from Query Store

Additionally, for T-SQL Scalar UDF Inlining, you can define the function as INLINE = OFF.

Do you persist memory grant feedback?

No. If the plan is evicted from cache, the feedback is lost. We are considering feedback persistence as a potential future enhancement.

Does IQP solve parameter sensitivity-related problems?

The “parameter sensitive plan” (PSP) problem refers to a scenario where the query optimizer generates a query execution plan that is optimal only for a specific parameter value (or set of values) and the cached plan ends up not being optimal for parameter values used in consecutive executions. Non-optimal plans can then result in query performance issues and overall workload throughput degradation.

The IQP feature family can help in some but not all PSP problem scenarios:

Memory grant feedback can help address spills or wasted memory due to the caching of a plan that uses an atypical parameter (very infrequent value used to compile the plan).
Adaptive joins can help in scenarios where the join-input row counts have significant variations.

Automatic Tuning (plan regression correction) can also assist with atypical parameter plan regression scenarios. There are other scenarios not covered by IQP and we’re actively looking at ways we can address them in future improvements.

Can I use the legacy cardinality estimator with IQP features?

Yes. Your query execution plan may be different from the default cardinality estimator plan, but IQP features are still available.

Can multiple IQP features work together?

Yes. Multiple IQP features can apply to a single plan. For example, batch made on rowstore can be enabled for a query, which then enables adaptive joins.

You have batch mode adaptive joins, but no row mode adaptive joins. Why?

Adaptive joins are more appropriate for scenarios where the join-input row count fluctuates significantly. Batch mode assumes a higher row flow vs. an OLTP low-row typical pattern. Row mode adaptive joins would likely be too prone to regressions. Batch mode on rowstore opens up adaptive joins for scenarios where we estimate higher row counts for join-inputs.

Have you solved * ALL * QP-related problems?

We’re just getting started... There are several problem areas that we are actively working on. Some of these efforts may result in incremental features over time.

Where can I learn more about IQP?

The full topic can be found here: https://aka.ms/IQP.

I'm testing IQP features and I have feedback. Where should I share?

We want to hear from you! Please email us at IntelligentQP@microsoft.com.

Updated Jul 23, 2019

Version 5.0

Joe Sack

Microsoft

Joined September 25, 2018

View Profile

Azure SQL Blog

Follow this blog board to get notified when there's new activity

\n Feature \n	\n Problem Area \n	\n How this feature helps \n
\n Adaptive Joins (Batch Mode) \n	\n Cardinality misestimates for join inputs can lead to incorrect physical join algorithm selection. \n	\n Adaptive joins dynamically select a join type during runtime based on actual input rows. This allows execution-time selection of either a nested loop or hash join algorithm. \n
\n Approximate Count Distinct \n	\n COUNT DISTINCT operations for big data scenarios involving many distinct values can result in very large memory grant sizes that can impact concurrency. \n	\n For scenarios where exact values are not required, APPROX_COUNT_DISTINCT improves performance and concurrency by using a very small memory footprint. \n
\n Batch Mode on Rowstore \n	\n CPU-bound relational data warehouse and analytic workloads may not be able to leverage columnstore indexes due to OLTP-overhead, lack of vendor support or other limitations. Without columnstore indexes, these workloads cannot benefit from batch execution mode. \n	\n This feature enables batch mode for analytic workloads without requiring columnstore indexes. \n
\n Interleaved Execution \n	\n Multi-statement table valued functions use a fixed guess for the cardinality estimate. If the actual number of rows is much higher than the guessed value, performance of downstream operations can suffer. \n	\n This feature uses the actual cardinality of the multi-statement table valued function encountered on first compilation, benefiting downstream operations. \n
\n Memory Grant Feedback (Batch and Row Mode) \n	\n Cardinality estimation under-estimation issues can result in operations spilling to disk when we have not requested enough memory. Conversely, cardinality estimation over-estimates can result in wasted memory and can impact workload concurrency. \n	\n If a query has operations that spill to disk, memory grant feedback adds more memory for consecutive executions. If a query operator wastes more than 50% of the memory allocated to it, memory grant feedback reduces the memory grant size for consecutive executions. \n
\n T-SQL Scalar UDF Inlining \n	\n Queries referencing T-SQL scalar UDFs use iterative invocation, lack costing and force serial execution. \n	\n T-SQL scalar UDFs are transformed into equivalent relational expressions that are “inlined” into the calling query, often resulting in significant performance gains. \n
\n Table Variable Deferred Compilation \n	\n Table variables use a fixed guess for the cardinality estimate. If the actual number of rows is much higher than the guessed value, performance of downstream operations can suffer. \n	\n This feature compiles a plan with the actual cardinality of the table variable encountered on first compilation instead of a fixed guess. \n

\n Feature \n	\n Problem Area \n	\n How this feature helps \n
\n Adaptive Joins (Batch Mode) \n	\n Cardinality misestimates for join inputs can lead to incorrect physical join algorithm selection. \n	\n Adaptive joins dynamically select a join type during runtime based on actual input rows. This allows execution-time selection of either a nested loop or hash join algorithm. \n
\n Approximate Count Distinct \n	\n COUNT DISTINCT operations for big data scenarios involving many distinct values can result in very large memory grant sizes that can impact concurrency. \n	\n For scenarios where exact values are not required, APPROX_COUNT_DISTINCT improves performance and concurrency by using a very small memory footprint. \n
\n Batch Mode on Rowstore \n	\n CPU-bound relational data warehouse and analytic workloads may not be able to leverage columnstore indexes due to OLTP-overhead, lack of vendor support or other limitations. Without columnstore indexes, these workloads cannot benefit from batch execution mode. \n	\n This feature enables batch mode for analytic workloads without requiring columnstore indexes. \n
\n Interleaved Execution \n	\n Multi-statement table valued functions use a fixed guess for the cardinality estimate. If the actual number of rows is much higher than the guessed value, performance of downstream operations can suffer. \n	\n This feature uses the actual cardinality of the multi-statement table valued function encountered on first compilation, benefiting downstream operations. \n
\n Memory Grant Feedback (Batch and Row Mode) \n	\n Cardinality estimation under-estimation issues can result in operations spilling to disk when we have not requested enough memory. Conversely, cardinality estimation over-estimates can result in wasted memory and can impact workload concurrency. \n	\n If a query has operations that spill to disk, memory grant feedback adds more memory for consecutive executions. If a query operator wastes more than 50% of the memory allocated to it, memory grant feedback reduces the memory grant size for consecutive executions. \n
\n T-SQL Scalar UDF Inlining \n	\n Queries referencing T-SQL scalar UDFs use iterative invocation, lack costing and force serial execution. \n	\n T-SQL scalar UDFs are transformed into equivalent relational expressions that are “inlined” into the calling query, often resulting in significant performance gains. \n
\n Table Variable Deferred Compilation \n	\n Table variables use a fixed guess for the cardinality estimate. If the actual number of rows is much higher than the guessed value, performance of downstream operations can suffer. \n	\n This feature compiles a plan with the actual cardinality of the table variable encountered on first compilation instead of a fixed guess. \n

Blog Post

Intelligent Query Processing Q&A

2 Comments