Intelligent Query Processing (IQP)

In SQL Server 2022, a new feature called Intelligent Query Processing (IQP) makes use of machine learning to enhance query performance. Because of the automatic workload adjustment provided by this feature, queries execute more quickly. Approximate Count Distinct, Scalar UDF Inlining, Batch Mode Window Aggregate, and Table Variable Deferred Compilation are just a few of the sub-features included in IQP. Each of these sub-features will be covered in-depth in this article, along with concrete examples of how they operate.

Approximate Count Distinct

Approximate Count Distinct is a sub-feature of IQP that estimates the number of distinct values in a column, resulting in faster query performance. When an estimate of the number of distinct values is adequate and an exact count is not necessary, this feature is helpful. For example, when calculating the number of unique visitors to a website, an approximate count distinct can provide an estimate of the number of visitors without having to scan the entire dataset.
Here’s an illustration of how to use Approximate Count Distinct:

                            SELECT 
                                 APPROX_COUNT_DISTINCT([OrderID]) as approx_distinct_count
                            FROM [dbo].[Orders]

Scalar UDF Inlining

Scalar UDF Inlining is a sub-feature of IQP that optimizes the execution of scalar user-defined functions, improving query performance.

This feature replaces the function call with its definition, eliminating the overhead associated with the function call. For example, if a function calculates the age of a person based on their birthdate, Scalar UDF Inlining can replace the function call with the calculation, resulting in faster query performance.

                      --lets create the Function first 
                         CREATE FUNCTION [dbo].[CalculateAge](@birthdate date)
                           RETURNS int
                              AS
                              BEGIN
                              DECLARE @age int;
                              SET @age = DATEDIFF(year, @birthdate, GETDATE());
                              RETURN @age;
                             END;

now calling this function calculate the age

                        SELECT
                           dbo.CalculateAge (BirthDate) as age
                       FROM
                           employees;

With Scalar UDF Inlining, the above query can be optimized to:

                        SELECT 
                              DATEDIFF(year, birthdate, GETDATE()) as age
                        FROM 
                             employees;

Batch Mode Window Aggregate

Batch Mode Window Aggregate is a sub-feature of IQP that enables batch processing of window aggregates, resulting in faster query performance. This feature improves the processing of aggregate functions that are calculated over a window of rows, such as calculating the moving average of a dataset.

Here is an example of using Batch Mode Window Aggregate:

                       SELECT 
                            AVG([Freight]) OVER (
                            ORDER BY [ShippedDate] 
                            ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
                                       ) as moving_average
                     FROM 
                            Orders;

Table Variable Deferred Compilation

Table Variable Deferred Compilation is a sub-feature of Intelligent Query Processing (IQP) in SQL Server 2022 that improves query performance by deferring the compilation of queries that use table variables. Microsoft introduced this 2019 edition. This feature delays the compilation of queries until after the table variable is populated, allowing the query optimizer to make better decisions about how to execute the query.

Table Variable Deferred Compilation can provide significant performance improvements for queries that use table variables. By delaying compilation until after the table variable is populated, the query optimizer can make better decisions about how to execute the query, resulting in faster query performance.

This feature will propagate cardinality estimates that are based on actual table variable row counts during optimization and initial plan compilation as opposed to the initial one-row guess. Then, the use of this precise row count information will be made to enhance subsequent plan operations.

below example I have created the Temp table @oradertable and inserted data from sales table. after that I have selected all the data from that Temp table.

You can see that reading from table variable , important thing is Actual number of rows for executions are same as estimated number of rows. it helps in performance boost `