Configure Intelligent Query Processing (IQP)
In this we will learn about how to configure Intelligent Query Processing (IQP).
However, 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.
Moreover, 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:
SQL
ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;
Batch mode Adaptive joins
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. However, the Adaptive Join operator defines a threshold that is used to decide when to switch to a Nested Loops plan. Further, your plan can dynamically switch to a better join strategy during execution.
Batch mode memory grant feedback
A query’s post-execution plan in SQL Server includes the minimum required memory needed for execution and the ideal memory grant size to have all rows fit in memory. Performance suffers when memory grant sizes are incorrectly sized. However, excessive grants result in wasted memory and reduced concurrency. Insufficient memory grants cause expensive spills to disk. Further, by addressing repeating workloads, batch mode memory grant feedback recalculates the actual memory required for a query and then updates the grant value for the cached plan. However, the following graph shows one example of using batch mode adaptive memory grant feedback. For the first execution of the query, duration was 88 seconds due to high spills:
SQL
DECLARE @EndTime datetime = ‘2016-09-22 00:00:00.000’;
DECLARE @StartTime datetime = ‘2016-09-15 00:00:00.000’;
SELECT TOP 10 hash_unique_bigint_id
FROM dbo.TelemetryDS
WHERE Timestamp BETWEEN @StartTime and @EndTime
GROUP BY hash_unique_bigint_id
ORDER BY MAX(max_elapsed_time_microsec) DESC;
Further, with memory grant feedback enabled, for the second execution, duration is 1 second (down from 88 seconds), spills are removed entirely, and the grant is higher:
Tracking memory grant feedback activity
You can track memory grant feedback events using the memory_grant_updated_by_feedback xEvent. This event tracks the current execution count history, the number of times the plan has been updated by memory grant feedback. And, the ideal additional memory grant before modification and the ideal additional memory grant after memory grant feedback has modified the cached plan.
Disabling batch mode memory grant feedback without changing the compatibility level
Memory grant feedback can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher. however, to disable batch mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database:
SQL
— SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;
— Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;
When enabled, this setting will appear as enabled in sys.database_scoped_configurations.
Further, to re-enable batch mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database:
SQL
— SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;
— Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;
However, you can also disable batch mode memory grant feedback for a specific query by designating DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK as a USE HINT query hint. For example:
SQL
SELECT * FROM Person.Address
WHERE City = ‘SEATTLE’ AND PostalCode = 98104
OPTION (USE HINT (‘DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK’));
Row mode memory grant feedback
Row mode memory grant feedback expands on the batch mode memory grant feedback feature by adjusting memory grant sizes for both batch and row mode operators. However, to enable row mode memory grant feedback in Azure SQL Database, enable database compatibility level 150 for the database you are connected to when executing the query.
Now, starting with row mode memory grant feedback, two new query plan attributes will be shown for actual post-execution plans: IsMemoryGrantFeedbackAdjusted and LastRequestedMemory, which are added to the MemoryGrantInfo query plan XML element.
LastRequestedMemory shows the granted memory in Kilobytes (KB) from the prior query execution. IsMemoryGrantFeedbackAdjusted attribute allows you to check the state of memory grant feedback for the statement within an actual query execution plan. Values surfaced in this attribute are as follows:
Disabling row mode memory grant feedback without changing the compatibility level
Row mode memory grant feedback can disable at the database or statement scope while still maintaining database compatibility level 150 and higher. However, to disable row mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database:
SQL
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;
Further, to re-enable row mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database:
SQL
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;
However, you can also disable row mode memory grant feedback for a specific query by designating DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK as a USE HINT query hint. For example:
SQL
SELECT * FROM Person.Address
WHERE City = ‘SEATTLE’ AND PostalCode = 98104
OPTION (USE HINT (‘DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK’));
A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.
Reference: Microsoft Documentation