Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
SQL database in Microsoft Fabric
This article contains in-depth descriptions of various intelligent query processing (IQP) features, release notes, and more detail. 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.
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, to set a database's compatibility level to SQL Server 2022 (16.x):
ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 160;
For more information on changes introduced with new versions, see:
Applies to: SQL Server (Starting with SQL Server 2017 (14.x)), Azure SQL Database
The batch mode Adaptive Joins feature enables the choice of a Hash Join or Nested Loops Join method to be deferred until after the first input has been scanned, by using a single cached plan. The Adaptive Join operator defines a threshold that is used to decide when to switch to a Nested Loops plan. Your plan can therefore dynamically switch to a better join strategy during execution.
For more information, including how to disable Adaptive joins without changing the compatibility level, see Understanding Adaptive joins.
Applies to: SQL Server (Starting with SQL Server 2017 (14.x)), Azure SQL Database
A multi-statement table-valued function (MSTVF) is a type of user-defined function that can accept parameters, execute multiple T-SQL statements, and RETURN
a table.
Interleaved execution helps workload performance issues that are due to fixed cardinality estimates associated with MSTVFs. With interleaved execution, the actual row counts from the function are used to make better-informed downstream query plan decisions.
MSTVFs have a fixed cardinality guess of 100 starting with SQL Server 2014 (12.x), and 1 for earlier SQL Server versions.
Interleaved execution changes the unidirectional boundary between the optimization and execution phases for a single-query execution and enables plans to adapt based on the revised cardinality estimates. During optimization if the database engine encounters a candidate for interleaved execution that uses multi-statement table-valued functions (MSTVFs), optimization will pause, execute the applicable subtree, capture accurate cardinality estimates, and then resume optimization for downstream operations.
The following image depicts a Live Query Statistics output, a subset of an overall execution plan that shows the impact of fixed cardinality estimates from MSTVFs
You can see the actual row flow vs. estimated rows. There are three noteworthy areas of the plan (flow is from right to left):
Contrast the prior plan with the actual plan generated with interleaved execution enabled:
MSTVF referencing statements in interleaved execution must currently be read-only and not part of a data modification operation. Also, MSTVFs are not eligible for interleaved execution if they do not use runtime constants.
In general, the higher the skew between the estimated vs. actual number of rows, coupled with the number of downstream plan operations, the greater the performance impact.
In general, interleaved execution benefits queries where:
SELECT *
from an MSTVF will not benefit from interleaved execution.The overhead should be minimal-to-none. MSTVFs were already being materialized prior to the introduction of interleaved execution, however the difference is that now we're now allowing deferred optimization and are then using the cardinality estimate of the materialized row set. As with any plan affecting changes, some plans could change such that with better cardinality for the subtree we get a worse plan for the query overall. Mitigation can include reverting the compatibility level or using Query Store to force the nonregressed version of the plan.
Once an interleaved execution plan is cached, the plan with the revised estimates on the first execution is used for consecutive executions without reinstantiating interleaved execution.
You can see usage attributes in the actual query execution plan:
Execution Plan attribute | Description |
---|---|
ContainsInterleavedExecutionCandidates | Applies to the QueryPlan node. When true, means the plan contains interleaved execution candidates. |
IsInterleavedExecuted | Attribute of the RuntimeInformation element under the RelOp for the TVF node. When true, means the operation was materialized as part of an interleaved execution operation. |
You can also track interleaved execution occurrences via the following extended events:
XEvent | Description |
---|---|
interleaved_exec_status |
This event fires when interleaved execution is occurring. |
interleaved_exec_stats_update |
This event describes the cardinality estimates updated by interleaved execution. |
Interleaved_exec_disabled_reason |
This event fires when a query with a possible candidate for interleaved execution does not actually get interleaved execution. |
A query must be executed in order to allow interleaved execution to revise MSTVF cardinality estimates. However, the estimated execution plan still shows when there are interleaved execution candidates via the ContainsInterleavedExecutionCandidates
showplan attribute.
If a plan is cleared or evicted from cache, upon query execution there is a fresh compilation that uses interleaved execution.
A statement using OPTION (RECOMPILE)
creates a new plan using interleaved execution and not cache it.
Plans using interleaved execution can be forced. The plan is the version that has corrected cardinality estimates based on initial execution.
Interleaved execution can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher. To disable interleaved execution for all query executions originating from the database, execute the following within the context of the applicable database:
-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = ON;
-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET INTERLEAVED_EXECUTION_TVF = OFF;
When enabled, this setting appears as enabled in sys.database_scoped_configurations. To re-enable interleaved execution for all query executions originating from the database, execute the following within the context of the applicable database:
-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = OFF;
-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET INTERLEAVED_EXECUTION_TVF = ON;
You can also disable interleaved execution for a specific query by designating DISABLE_INTERLEAVED_EXECUTION_TVF
as a USE HINT query hint. For example:
SELECT [fo].[Order Key], [fo].[Quantity], [foo].[OutlierEventQuantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Fact].[WhatIfOutlierEventQuantity]('Mild Recession',
'1-01-2013',
'10-15-2014') AS [foo] ON [fo].[Order Key] = [foo].[Order Key]
AND [fo].[City Key] = [foo].[City Key]
AND [fo].[Customer Key] = [foo].[Customer Key]
AND [fo].[Stock Item Key] = [foo].[Stock Item Key]
AND [fo].[Order Date Key] = [foo].[Order Date Key]
AND [fo].[Picked Date Key] = [foo].[Picked Date Key]
AND [fo].[Salesperson Key] = [foo].[Salesperson Key]
AND [fo].[Picker Key] = [foo].[Picker Key]
OPTION (USE HINT('DISABLE_INTERLEAVED_EXECUTION_TVF'));
A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.
Applies to: SQL Server (Starting with SQL Server 2019 (15.x)), Azure SQL Database
Scalar UDF inlining automatically transforms scalar UDFs into relational expressions. It embeds them in the calling SQL query. This transformation improves the performance of workloads that take advantage of scalar UDFs. Scalar UDF inlining facilitates cost-based optimization of operations inside UDFs. The results are efficient, set-oriented, and parallel instead of inefficient, iterative, serial execution plans. This feature is enabled by default under database compatibility level 150 or higher.
For more information, see Scalar UDF inlining.
Applies to: SQL Server (Starting with SQL Server 2019 (15.x)), Azure SQL Database
Table variable deferred compilation improves plan quality and overall performance for queries referencing table variables. During optimization and initial plan compilation, this feature propagates cardinality estimates that are based on actual table variable row counts. This exact row count information will then be used for optimizing downstream plan operations.
With table variable deferred compilation, compilation of a statement that references a table variable is deferred until the first actual execution of the statement. This deferred compilation behavior is identical to the behavior of temporary tables. This change results in the use of actual cardinality instead of the original one-row guess.
To enable table variable deferred compilation, enable database compatibility level 150 or higher for the database you're connected to when the query runs.
Table variable deferred compilation doesn't change any other characteristics of table variables. For example, this feature doesn't add column statistics to table variables.
Table variable deferred compilation doesn't increase recompilation frequency. Rather, it shifts where the initial compilation occurs. The resulting cached plan generates based on the initial deferred compilation table variable row count. The cached plan is reused by consecutive queries. It's reused until the plan is evicted or recompiled.
Table variable row count that is used for initial plan compilation represents a typical value might be different from a fixed row count guess. If it's different, downstream operations will benefit. Performance might not be improved by this feature if the table variable row count varies significantly across executions.
Disable table variable deferred compilation at the database or statement scope while still maintaining database compatibility level 150 and higher. To disable table variable deferred compilation for all query executions originating from the database, execute the following example within the context of the applicable database:
ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;
To re-enable table variable deferred compilation for all query executions originating from the database, execute the following example within the context of the applicable database:
ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON;
You can also disable table variable deferred compilation for a specific query by assigning DISABLE_DEFERRED_COMPILATION_TV as a USE HINT query hint. For example:
DECLARE @LINEITEMS TABLE
(L_OrderKey INT NOT NULL,
L_Quantity INT NOT NULL
);
INSERT @LINEITEMS
SELECT L_OrderKey, L_Quantity
FROM dbo.lineitem
WHERE L_Quantity = 5;
SELECT O_OrderKey,
O_CustKey,
O_OrderStatus,
L_QUANTITY
FROM
ORDERS,
@LINEITEMS
WHERE O_ORDERKEY = L_ORDERKEY
AND O_OrderStatus = 'O'
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));
Applies to:
SQL Server 2022 (16.x)
Azure SQL Database
Azure SQL Managed Instance
Parameter Sensitivity Plan (PSP) optimization is part of the Intelligent query processing family of features. It addresses the scenario where a single cached plan for a parameterized query is not optimal for all possible incoming parameter values. This is the case with nonuniform data distributions.
Approximate query processing is a new feature family. It aggregates across large datasets where responsiveness is more critical than absolute precision. An example is calculating a COUNT(DISTINCT())
across 10 billion rows, for display on a dashboard. In this case, absolute precision isn't important, but responsiveness is critical.
Applies to: SQL Server (Starting with SQL Server 2019 (15.x)), Azure SQL Database
The new APPROX_COUNT_DISTINCT aggregate function returns the approximate number of unique non-null values in a group.
This feature is available starting with SQL Server 2019 (15.x), regardless of the compatibility level.
For more information, see APPROX_COUNT_DISTINCT (Transact-SQL).
Applies to: SQL Server (Starting with SQL Server 2022 (16.x)), Azure SQL Database
These aggregate functions compute percentiles for a large dataset with acceptable rank-based error bounds to help make rapid decisions by using approximate percentile aggregate functions.
For more information, see APPROX_PERCENTILE_DISC (Transact-SQL) and APPROX_PERCENTILE_CONT (Transact-SQL)
Applies to: SQL Server (Starting with SQL Server 2019 (15.x)), Azure SQL Database
Batch mode on rowstore enables batch mode execution for analytic workloads without requiring columnstore indexes. This feature supports batch mode execution and bitmap filters for on-disk heaps and B-tree indexes. Batch mode on rowstore enables support for all existing batch mode-enabled operators.
Note
Documentation uses the term B-tree generally in reference to indexes. In rowstore indexes, the Database Engine implements a B+ tree. This does not apply to columnstore indexes or indexes on memory-optimized tables. For more information, see the SQL Server and Azure SQL index architecture and design guide.
SQL Server 2012 (11.x) introduced a new feature to accelerate analytical workloads: columnstore indexes. The use cases and performance of columnstore indexes increased in each subsequent release of SQL Server. Creating columnstore indexes on tables can improve performance for analytical workloads. However, there are two related but distinct sets of technologies:
The two sets of features work together to improve input/output (I/O) and CPU utilization:
The two technologies take advantage of each other whenever possible. For example, batch mode aggregates can be evaluated as part of a columnstore index scan. Also columnstore data that's compressed is processed by using run-length encoding much more efficiently with batch mode joins and batch mode aggregates.
It is important to understand however, that the two features are independent:
You usually get the best results when you use the two features together. Before SQL Server 2019 (15.x), the SQL Server query optimizer considered batch mode processing only for queries that involve at least one table with a columnstore index.
Columnstore indexes might not be appropriate for some applications. An application might use some other feature that isn't supported with columnstore indexes. For example, in-place modifications are not compatible with columnstore compression. Therefore, triggers aren't supported on tables with clustered columnstore indexes. More importantly, columnstore indexes add overhead for DELETE and UPDATE statements.
For some hybrid transactional-analytical workloads, the overhead of a transactional workload outweighs the benefits gained from using columnstore indexes. Such scenarios can benefit from improved CPU usage by employing batch mode processing alone. That is why the batch-mode-on-rowstore feature considers batch mode for all queries regardless of what type of indexes are involved.
The following workloads might benefit from batch mode on rowstore:
Note
Batch mode on rowstore helps only by reducing CPU consumption. If your bottleneck is I/O-related, and data isn't already cached ("cold" cache), batch mode on rowstore will not improve query elapsed time. Similarly, if there is no sufficient memory on the machine to cache all data, a performance improvement is unlikely.
Batch mode on rowstore requires database to compatibility level 150.
Even if a query does not access any tables with columnstore indexes, the query processor uses heuristics to decide whether to consider batch mode. The heuristics consist of these checks:
If batch mode on rowstore is used, you see the actual run mode as batch mode in the query plan. The scan operator uses batch mode for on-disk heaps and B-tree indexes. This batch mode scan can evaluate batch mode bitmap filters. You might also see other batch mode operators in the plan. Examples are hash joins, hash-based aggregates, sorts, window aggregates, filters, concatenation, and compute scalar operators.
Query plans don't always use batch mode. The Query Optimizer might decide that batch mode isn't beneficial for the query.
The Query Optimizer search space is changing. So if you get a row mode plan, it might not be the same as the plan you get in a lower compatibility level. And if you get a batch mode plan, it might not be the same as the plan you get with a columnstore index.
Plans might also change for queries that mix columnstore and rowstore indexes because of the new batch mode rowstore scan.
There are current limitations for the new batch mode on rowstore scan:
There are queries that batch mode isn't used for even with columnstore indexes. Examples are queries that involve cursors. These same exclusions also extend to batch mode on rowstore.
The BATCH_MODE_ON_ROWSTORE
database scoped configuration is ON by default.
You can disable batch mode on rowstore without changing the database compatibility level:
-- Disabling batch mode on rowstore
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF;
-- Enabling batch mode on rowstore
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON;
You can disable batch mode on rowstore via database scoped configuration. But you can still override the setting at the query level by using the ALLOW_BATCH_MODE
query hint. The following example enables batch mode on rowstore even with the feature disabled via database scoped configuration:
SELECT [Tax Rate], [Lineage Key], [Salesperson Key], SUM(Quantity) AS SUM_QTY, SUM([Unit Price]) AS SUM_BASE_PRICE, COUNT(*) AS COUNT_ORDER
FROM Fact.OrderHistoryExtended
WHERE [Order Date Key]<=DATEADD(dd, -73, '2015-11-13')
GROUP BY [Tax Rate], [Lineage Key], [Salesperson Key]
ORDER BY [Tax Rate], [Lineage Key], [Salesperson Key]
OPTION(RECOMPILE, USE HINT('ALLOW_BATCH_MODE'));
You can also disable batch mode on rowstore for a specific query by using the DISALLOW_BATCH_MODE
query hint. See the following example:
SELECT [Tax Rate], [Lineage Key], [Salesperson Key], SUM(Quantity) AS SUM_QTY, SUM([Unit Price]) AS SUM_BASE_PRICE, COUNT(*) AS COUNT_ORDER
FROM Fact.OrderHistoryExtended
WHERE [Order Date Key]<=DATEADD(dd, -73, '2015-11-13')
GROUP BY [Tax Rate], [Lineage Key], [Salesperson Key]
ORDER BY [Tax Rate], [Lineage Key], [Salesperson Key]
OPTION(RECOMPILE, USE HINT('DISALLOW_BATCH_MODE'));
The query processing feedback features are part of the Intelligent query processing family of features.
Query processing feedback is a process by which the query processor in SQL Server, Azure SQL Database, and Azure SQL Managed Instance uses historical data about a query's execution to decide if the query might receive help from one or more changes to the way it's compiled and executed. The performance data is collected in the Query Store, with various suggestions to improve query execution. If successful, we persist these modifications to disk in memory and/or in the Query Store for future use. If the suggestions don't yield sufficient improvement, they're discarded, and the query continues to execute without that feedback.
For information on which query processing feedback features are available in different releases of SQL Server, or in Azure SQL Database or Azure SQL Managed Instance, see Intelligent query processing in SQL databases or the following articles for each feedback feature.
Memory grant feedback has been introduced in waves over the past major releases of SQL Server.
For information about Batch mode memory grant feedback, visit Batch mode memory grant feedback.
For information about row mode memory grant feedback, visit Row mode memory grant feedback.
For information about percentile and persistence mode memory grant feedback, visit Percentile and persistence mode memory grant feedback.
For information about DOP feedback, visit Degree of parallelism (DOP) feedback.
For information about CE feedback, visit Cardinality estimation (CE) feedback.
For information about optimized plan forcing with Query Store, visit Optimized plan forcing with Query Store.
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Understand the built-in query intelligence enhancements of SQL Server 2022 - Training
Learn about and use the built-in query intelligence enhancements of SQL Server 2022.
Certification
Microsoft Certified: Azure Cosmos DB Developer Specialty - Certifications
Write efficient queries, create indexing policies, manage, and provision resources in the SQL API and SDK with Microsoft Azure Cosmos DB.
Documentation
Intelligent query processing - SQL Server
Intelligent query processing features to improve query performance in SQL Server, Azure SQL Managed Instance, and Azure SQL Database.
Parameter Sensitive Plan optimization - SQL Server
Learn about Parameter Sensitive Plan Optimization in the Query Store.
Degree of parallelism (DOP) feedback - SQL Server
Learn about Degree of parallelism (DOP) feedback, part of the Intelligent Query Processing (IQP) feature set.