How does Scalar UDF Inlining affect the performance of scalar functions?

SQL Server 2019 introduces a new feature called ā€œScalar UDF Inliningā€. In a nutshell, this feature will take a scalar function and it will inline it into the query plan (similar to an Inline Table Valued Function (TVF), or even a view).

This blog post will examine changes to the query plan and performance when Scalar UDF Inlining is occurring.

I have previously blogged about function performance ā€“ here and here. For a quick recap, the performance test ranks these function in duration. The order of the types of functions by duration is Inline TVF, Scalar UDF, and then finally a Multi-Statement TVF (MSTVF) – and the MSTVF is way behind the other two types of functions.

I’m using a Linux (Ubuntu) VM with SQL Server 2019 to perform these comparison performance tests. I use one database in the SQL 2019 compatibility level, and another one in the SQL 2017 compatibility level. Iā€™m using the same performance test used in the previous blog posts.

Creating the test environment

I start off by creating two databases, and putting one of them in the SQL 2017 compatibility level:

Next, I create a table with a million rows of random, and three functions. These functions perform the same work, the only difference is in the type of function. The functions remove the non-numeric characters from the string, and return the result in the order that the digits appear. This table and the functions are created in both databases:

The following query tests that all the functions do truly return the same data:

 

Differences in the query plans

The first thing that I want to do is to compare the difference in the query plans produced in each database. I run the following query in each database with the actual execution plan turn on:

For the database in the SQL 2017 compatibility level, the resulting query plan is:

SQL 2017 Query Plan

SQL 2017 Query Plan

Hovering over the Select operator:

SQL 2017 query plan cost

SQL 2017 query plan cost

For the database in the SQL 2019 compatibility level, the resulting query plan is:

SQL 2019 Query Plan

SQL 2019 Query Plan

Hovering over the Select operator:

SQL 2019 Query Plan Cost

SQL 2019 Query Plan Cost

A casual look shows that the Scalar UDF Inlining has occurred. The work being performed by the scalar function has been inlined into the query plan. In SQL 2017, all of the work is shown by a “Compute Scalar” operator. This operator hides all of the underlying work going on in the Scalar UDF. This results in a query plan with a cost that does not truly represent the work going on.

The performance test

The next step is to test the performance of each of the functions. This run is performed 11 times for each type of function, in each of the databases. Why 11 times? Well, I did it once, then decided to run this in a batch for ten loops. The performance testing code is:

The results for SQL 2017:

SQL 2017 Results

SQL 2017 Results

As expected (based on the other two blog posts), the MSTVF is the slowest. Overall, the inline TVF is the fastest, with the scalar UDF falling between the two. For each batch, the Inline TVF is about 20% of the batch, the Scalar UDF is about 35%, and the MSTVF is about 45%.

How does the Scalar UDF Inlining help out? Well, the results for that are:

SQL 2019 Results

SQL 2019 Results

In this result set, we can see that the Scalar functions have improved, but the overall ranking has remained the same. The scalar functions are still between the Inline TVF and the MSTVF. However, when you look at the percent of batch, the Inline TVF is about 17%, the Scalar UDF is about 25% and the MSTVF is about 58%.

And if you look at the overall duration for all batches, we see that the SQL 2019 runs shaved off about 1/3 of the total time.

Both of these show that Scalar UDF Inlining is improving those Scalar UDFs!

In Conclusion…

We can see that the Scalar UDF Inlining is improving the performance of the Scalar UDFs. When the batches are running in 1/3 less time, that is a pretty dramatic improvement.

Going into this test, I was hopeful that the Scalar UDF Inlining performance would be on par with the Inline TVFs. While the performance has dramatically improved, it wasn’t enough to match the Inline TVFs. This means that when you are working with functions, the best choice is still to use an Inline TVF where possible. This also shows that we still want to avoid using a MSTVF.

Be sure to read the Microsoft article (the link is in the first line of this post) for all that you can, and can’t, do with Scalar UDF Inlining.