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 `