Esat Erkec
SQL Server 2019 feature: Scalar UDF inlining solves the iterative invocation of the scalar functions

Scalar UDF Inlining in SQL Server 2019

September 28, 2020 by

In this article, we will explore a new SQL Server 2019 feature which is Scalar UDF (scalar user-defined) inlining. Scalar UDF inlining is a member of the intelligent query processing family and helps to improve the performance of the scalar-valued user-defined functions without any code changing.

The DRY (Don’t Repeat Yourself) software programming principle aims to avoid rewriting the same code repeatedly so it enables modularity and code reusability. The functions and stored procedures help to implement the DRY principle to database programming. In this context, the scalar function can be described as below:

The scalar user-defined function may not take any parameter or can take one or more parameters as input and returns a single value. The purpose of scalar functions is to encapsulate a piece of code into the single database object, and in this way, it enables us to use functions in the queries without code repetition. On the other hand, scalar functions may impact performance negatively when they perform for a huge number of rows. To be honest, they can be performance killers.

Scalar user-defined functions performance drawbacks

As we stated, scalar functions can impact the performance of the queries negatively. Now we will explore various major performance behaviors of scalar functions that degrade the query performance.

Inaccurate I/O statistics

The SET STATISTICS IO setting offers an I/O statistics report that helps to monitor the disk activity of the executed query and this report helps us to observe disk activities of the executed query. However, the scalar functions I/O statistics can not be measured by this report, and this case cause to observe misleading I/O information. Now, we will execute the following query in the Adventureworks2016 database and parse the statistics output with the help of the Statistics Parser.

I/O statistics of the query before to implement a SQL Server 2019 feature that name is scalar UDF Inlining

As we can see, this query has read the Product and SalesOrderDetail tables and has completed only milliseconds.

As a second step, we will execute the following query and will analyze the I/O statistics. The only difference to this query is that it contains the only ufnGetStock which calculates the inventory level of the products. This function will be invoked for the row number of the query result.

I/O statistics of the query before to implement a SQL Server 2019 feature

As we can see, there is a dramatic difference between query execution times. However, the I / O values ​​of the query that contains a scalar function remained unchanged. The following table shows the I/O and time statistics of the two queries:

Logical Reads

Elapsed Time

Product

SalesOrderDetail

Query without scalar UDF

15

1248

00:00:00:749

Query with Scalar UDF

15

1248

00:00:05.096

Unreliable execution plan cost

SQL Server query optimizer is a cost-based optimizer and before the execution of the query, it calculates the estimated cost of the query. The query optimizer chooses the ideal execution plan candidate according to this estimated cost and it uses this plan for the execution of the query. However, the query optimizer does not consider the scalar user-defined function cost, so the real cost of the scalar functions is not involved in the execution plan appropriately. In this case, the query optimizer will decide on non-effective execution plans. When we analyze the following query execution plan, we can see that the compute scalar operator cost is only 1% of all query plans. However, adding to the scalar function dramatically (5 times) increase the execution time of the query. In fact, until the execution of the query optimizer has no idea about the scalar function codes and all problems arise from this factor.

Execution plan of the query before to implement a SQL Server 2019 feature

When we expand the QueryTimeStats property of the select operator, we see the UdfCpuTime and UdfElapsedTime. This two-property helps to figure out the impact of the user-defined functions into query execution time.

A new SQL Server 2019 feature : UdfCpuTime  and UdfElapsedTime

We can make a very simple calculation to understand the impact of the scalar function on the query execution time.

The formula is: (UdfElapsedTime/ElapsedTime)*100

75% of the query time is consumed to invoke the scalar-function. This situation explains why we call scalar functions performance killers.

Scalar UDF prevents the creation of parallel execution plans

The queries with the scalar function always run serially.

Iterative invocation

The scalar functions are performed on how many rows were forwarded from the previous operator. For the following query, this number is 121.317, the ufnGetStock will be invoked 121.317 times.

SQL Server 2019 feature: Scalar UDF inlining solves the iterative invocation of the scalar functions

When we query the dm_exec_query_stats view, we can see the execution value of the ufnGetStock function.

SQL Server 2019 feature: Scalar UDF inlining solves performance problems

Scalar UDF Inlining

In the imperative programming, a series of command controls determine what the computer has to do and when has to do to obtain the desired result. The scalar UDF functions might include imperative code statements as follows :

  • DECLARE, SET: Variable declaration and assignments
  • IF-ELSE statements
  • RETURN Single or multiple return statements
  • WHILE-LOOP statements

The major problem of the imperative code is that it takes incredible effort to optimize and it is also very difficult. However, the scalar UDF inlining feature converts the suitable scalar functions into the subquery and incorporates this subquery into query with the APPLY statement. All these actions were performed during the runtime of the query execution so we don’t need to change any code for the scalar functions. So, the query optimizer can generate more accurate execution plans.

In order to enable this new SQL Server 2019 feature, we need to alter the compatibility level of the database.

After changing the database compatibility level, we will execute our test query that includes scalar function. We will reanalyze its execution plan and I/O statistics.

SQL Server 2019 feature that improves performance of the scalar functions performance

The red-framed area in the picture has been newly added to the query execution because of the scalar UDF inlining feature. In this execution plan, we can not see the UdfCpuTime and UdfElapsedTime attributes on the execution plan because the query does not invoke any scalar function.

As a result of this case, a more accurate execution plan has been generated. At the same time, the execution time of the query is decreased by 5 times.

SQL Server 2019 feature helps to generate proper I/O statistics.

The following table shows the comparison of the execution times and I/O statistics of the queries:

Logical Reads

Elapsed Time

Product

SalesOrderDetail

ProductInventory

Query without scalar UDF

15

1248

0

00:00:00:749

Query with scalar UDF

15

1248

0

00:00:05.096

Query with scalar UDF inlining

15

1248

1008

00:00:00:848

On the last row of the I/O statistics comparison of the table, we will see the ProductInventory table because this table is used by the ufnGetStock scalar function.

disable or enable at the function level.

The following query can disable or enable scalar UDF inlining at the database level.

We can disable or enable scalar UDF inlining when we create or alter a scalar function using the WITH INLINE syntax.

As a last, we can disable the scalar UDF inlining at the query level using the DISABLE_TSQL_SCALAR_UDF_INLINING keyword.

Scalar UDF Inlining details

The Scalar UDF Inlining feature uses the FROID framework. FROID is an extensible optimization framework that transforms scalar functions into relational algebraic expressions. This code conversion is made by the FROID framework automatically so that the database developers can use the scalar functions without considering performance issues and without making any changes in the code. However, some of the scalar functions can be inalienable when it meets the Inlineable scalar UDFs requirements. At the same time, the is_inlineable column of the sys.sql_modules the function is inalienable or not.

SQL Server 2019 feature: How to monitor which scalar function can use the scalar UDF inlining

Conclusion

In this article, we explored the SQL Server 2019 feature that improves the scalar function performance. The scalar user-defined functions offer modularity and code reusability in SQL Server but performance issues were annoying. The Scalar UDF Inlining feature seems to overcome this performance problem. However, before enabling the Scalar UDF Inlining :

  • Check the latest fixes of the Scalar UDF Inlining feature
  • Learn details about the Froid framework
  • Test the performance impact of the Scalar UDF Inlining in your test database
  • Check which of your scalar functions can be inlineable using sys.sql_modules view
Esat Erkec
168 Views