Server 2019 Intelligent Query Processing: Scalar UDF Inlining

In the previous post, we learned about Batch Mode on Row Store feature. In this post, let’s focus on Scalar UDF Inlining feature introduced as a part of SQL Server 2019 Intelligent Query Processing features. This feature is really useful for improving the performance of scalar user-defined function (UDF) inlining without have to modify any code.

Source: https://www.pexels.com/photo/macro-photography-of-tree-235615/

Scalar user defined function will return a single value by taking one or more parameters. For example, if you use a calculation that be used in many number of your queries, instead of repeating the code each time for the queries, we can place the same code inside the function and use the same function in all queries requiring the same type of calculations. These functions basically simplify the code. Scalar functions are good when they are used for small set of rows but might cause performance impact with large amount of data.

First let’s run a sample query on Adventureworks2016 database without and with the UDF function in it and see the I/O information

SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT
Product.Name AS PName,
Product.ProductNumber AS PNumber,
Product.StandardCost AS StandardPrice,
History.EndDate as Enddate
FROM  Production.Product Product
INNER JOIN Production.ProductListPriceHistory History
ON Product.ProductID = History.ProductID

Look at the number of the logical reads for both the tables, ProductListPriceHistory, Product table and total elapsed time.

Let’s run the same code this time adding just another UDF function to it and look at the information again.

SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT
Product.Name AS PName,
Product.ProductNumber AS PNumber,
Product.StandardCost AS StandardPrice,
History.EndDate as Enddate,
dbo.ufnGetProductListPrice(Product.ProductID,History.StartDate) as Listprice
FROM  Production.Product Product
INNER JOIN Production.ProductListPriceHistory History
ON Product.ProductID = History.ProductID

When you observe the Logical read for ProductListPriceHistory, Product table and total elapsed time its more this time (76ms).

The logical reads information is the same but the difference here is the elapsed time. Its a little difference here for my 395 rows but this time can differ significantly when you are pulling large number of rows. We cannot get the accurate I/O statistics information for scalar UDF functions. Scalar valued UDF also cannot calculate the Execution cost information accurately. When you check the execution plan of the query we executed before with the UDF in it, we can see the cost shown as 0% for the compute scalar operator but when you check the properties for the select we can see the time for the UDF (UdfElapsedTime) is almost 70%. This cost is not properly estimated by the optimizer causing the performance issues for queries using scalar UDF’s.

Scalar UDF’s always run the query in serial and not parallel. If you have a scalar UDF in your query but that not actually touching any of your data (Like for example, UDF getting the Getdate()), even then you can see that causing the performance issue because the query runs serially. Here in this above query execution plan, the compute scalar operator invokes UDF for 395 times because UDF also runs once per row. UDF query plan is not shown in the actual query as it could generate a different query plan for each execution which can be 395 times in this case.

Scalar UDF Inlining

Scalar UDF inlining feature will convert the UDF query into simple subquery that is placed inline into the code we have in our query with APPLY statement. All these operations happen during the query runtime so we would not have to change any code. To enable the new feature on SQL Server 2019, we need to have the database compatibility to 150 and run the same query having UDF and check the elapsed time now. It is 60sec.

ALTER DATABASE AdventureWorks2016 SET COMPATIBILITY_LEVEL = 150;

SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT
Product.Name AS PName,
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT
Product.Name AS PName,
Product.ProductNumber AS PNumber,
Product.StandardCost AS StandardPrice,
History.EndDate as Enddate,
dbo.ufnGetProductListPrice(Product.ProductID,History.StartDate) as Listprice
FROM  Production.Product Product
INNER JOIN Production.ProductListPriceHistory History
ON Product.ProductID = History.ProductID

Now when you check the actual execution plan, you cannot see the UDF being invoked because of the inlining feature. You will not find the UdfCpuTime, UdfElapsedTime in the select properties as UDF won’t be invoked.

We can disable or enable the scalar UDF feature on the database level, function level and also at the query level.

Database level:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON/OFF

Query Level using a HINT:

OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

Function Level:

CREATE OR ALTER FUNCTION UDF(@Parameter1 [int])
RETURNS [int] 
WITH INLINE = OFF /ON
AS 
BEGIN
--Code here
END

For this new Scalar UDF inlining feature, there are few requirements that the scalar UDF functions needs to meet. You can check if the function is inlineable or not by using the sys.sqlmodules and look for the column is_inlineable.

Conclusion:

Scalar UDF Inlining feature is introduced to improve the performance of your queries invoking the scalar UDF’s where the execution is performance bottleneck. As this new feature transform the UDF into scalar expression or scalar subqueries that are placed inline in the code of the UDF so the query gets called replacing the UDF operator. These expressions and subqueries are then optimized by the optimizer. As a result, we would not be able to see the UDF operator inside our execution plan providing the performance boost.

Thank you for Reading!

5 thoughts on “Server 2019 Intelligent Query Processing: Scalar UDF Inlining

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s