Intelligent Query Processing in SQL Server: Detailed Overview with Examples

Leave a reply

Intelligent Query Processing (IQP) is a feature set introduced in SQL Server 2017 and extended in SQL Server 2019 and SQL Server 2022 to enhance query performance. It automatically adapts and optimizes the execution of queries based on runtime conditions, making it highly valuable for improving performance without needing code changes. IQP helps improve both OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) workloads by addressing common performance bottlenecks.

This blog will cover:

  • How Intelligent Query Processing works
  • Commands involved
  • Practical examples
  • Benefits of using IQP in real-world scenarios

Key Features of Intelligent Query Processing

Intelligent Query Processing consists of several enhancements to the SQL Server query engine. Some of the key features include:

  1. Adaptive Joins: SQL Server dynamically chooses between a nested loop join and a hash join based on runtime statistics.
  2. Batch Mode on Rowstore: Batch processing, previously available only on columnstore indexes, is extended to rowstore data, improving performance in OLTP workloads.
  3. Scalar UDF Inlining: Optimizes the performance of scalar user-defined functions by converting them into relational expressions.
  4. Parameter Sensitive Plan (PSP) Optimization (SQL Server 2022): Helps SQL Server adapt to different parameter values in queries that previously led to suboptimal plans due to parameter sniffing.
  5. Memory Grant Feedback: Adjusts memory grants for queries dynamically based on actual execution feedback to prevent over- or under-allocation of memory.
  6. Cardinality Estimation Feedback: Provides real-time feedback on query execution and adjusts the estimated row count for better performance.
  7. Deferred Compilation for Table Variables: Improves the handling of table variables by deferring their compilation until actual values are available.

How Intelligent Query Processing Works

Intelligent Query Processing is designed to reduce the need for manual query tuning. The SQL Server engine leverages real-time execution statistics and adapts the execution plan dynamically to optimize performance. It uses feedback mechanisms to monitor query performance and make adjustments without requiring intervention from the developer or DBA.

How to Enable IQP Features

Most IQP features are enabled automatically by SQL Server. To enable or disable specific IQP features like Batch Mode on Rowstore, you can adjust the database compatibility level.

Here’s how to check and change the compatibility level:

-- Check the current compatibility level
SELECT compatibility_level
FROM sys.databases
WHERE name = 'YourDatabaseName';

-- Change the compatibility level to enable IQP features (150 or higher for SQL Server 2019+)
ALTER DATABASE YourDatabaseName 
SET COMPATIBILITY_LEVEL = 150;

IQP features generally do not require any application changes, but in some cases, you can modify settings for individual queries to take advantage of these optimizations.

Detailed Breakdown of Key IQP Features

1. Adaptive Joins

Adaptive joins allow SQL Server to switch between nested loop joins and hash joins at runtime based on the number of rows processed. This is particularly useful in cases where the estimated number of rows may differ significantly from the actual number of rows processed during query execution.

-- Example Query with Join that uses Adaptive Join
SELECT p.Name, s.SalesOrderID
FROM Production.Product p
JOIN Sales.SalesOrderDetail s
ON p.ProductID = s.ProductID
WHERE s.OrderQty > 10;

SQL Server will initially plan a nested loop join but will switch to a hash join if the data volume crosses a certain threshold during execution.

2. Scalar UDF Inlining

In earlier versions, user-defined functions (UDFs) would be executed row by row, leading to significant performance overhead. With Scalar UDF Inlining, SQL Server automatically rewrites scalar UDFs into inlined relational expressions.

-- Example UDF
CREATE FUNCTION dbo.CalculateDiscount(@price DECIMAL(10, 2)) 
RETURNS DECIMAL(10, 2)
AS
BEGIN
    RETURN @price * 0.9;
END;

-- Query using UDF
SELECT SalesOrderID, dbo.CalculateDiscount(UnitPrice)
FROM Sales.SalesOrderDetail;

In SQL Server 2019+ with IQP, this function is inlined into the query plan, avoiding row-by-row execution and improving performance.

3. Batch Mode on Rowstore

Batch Mode on Rowstore allows queries that operate on traditional rowstore tables (not just columnstore) to benefit from batch processing, which can significantly reduce CPU utilization.

-- Query that benefits from Batch Mode on Rowstore
SELECT COUNT(*)
FROM Sales.SalesOrderDetail
WHERE OrderQty > 10;

With Batch Mode on Rowstore, the query execution operates on chunks of data (batches), improving performance for large queries even on row-based tables.

4. Memory Grant Feedback

Memory grant feedback dynamically adjusts memory allocation for subsequent query executions based on actual usage. If a query underestimates or overestimates the memory required, the engine will adjust this for future executions, optimizing both CPU and memory usage.

-- Example query that benefits from Memory Grant Feedback
SELECT CustomerID, SUM(TotalDue)
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

If the query initially over-allocates memory, SQL Server will adjust the memory allocation on future executions.

5. Parameter Sensitive Plan (PSP) Optimization

PSP optimization helps avoid performance issues caused by parameter sniffing. SQL Server generates multiple plans based on different parameter values instead of sticking with one plan, improving performance for queries with varying parameters.

-- Example of query affected by Parameter Sensitivity
CREATE PROCEDURE GetOrderDetails (@OrderID INT)
AS
BEGIN
    SELECT * 
    FROM Sales.SalesOrderDetail 
    WHERE SalesOrderID = @OrderID;
END;

-- Execute with different parameter values
EXEC GetOrderDetails @OrderID = 1;
EXEC GetOrderDetails @OrderID = 100000;

In SQL Server 2022, PSP optimization allows SQL Server to use different execution plans for @OrderID = 1 and @OrderID = 100000, optimizing performance for each case.

Monitoring Intelligent Query Processing

You can monitor IQP features and their impact on query performance using Dynamic Management Views (DMVs). For example, you can track Memory Grant Feedback using the following query:

-- Query to track Memory Grant Feedback
SELECT query_id, plan_id, requested_memory_kb, granted_memory_kb
FROM sys.dm_exec_query_memory_grants;

Similarly, you can monitor Adaptive Joins and Batch Mode on Rowstore via query execution plans in SQL Server Management Studio (SSMS).

Benefits of Intelligent Query Processing

  1. Improved Performance without Code Changes: Most IQP features do not require any modifications to your existing queries, making it easy to gain performance benefits.
  2. Reduced CPU and Memory Utilization: Features like Batch Mode on Rowstore and Memory Grant Feedback optimize resource usage, allowing SQL Server to handle more concurrent requests.
  3. Automatic Plan Adaptation: Features like Adaptive Joins and PSP optimization dynamically adapt query plans based on runtime statistics, improving execution efficiency.
  4. Scalable Workloads: IQP is particularly useful for workloads that scale, allowing the database to handle increasing amounts of data without sacrificing performance.

Leave a Reply