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.
|
1 2 3 4 5 6 7 8 9 |
SET STATISTICS IO ON SET STATISTICS TIME ON SELECT Product.Name AS [Product Name] , Product.ProductNumber AS [Product Number], Sales.UnitPrice AS [Sales UnitPrice] FROM Production.Product Product INNER JOIN Sales.SalesOrderDetail Sales ON Product.ProductID = Sales.ProductID |
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.
|
1 2 3 4 5 6 7 8 9 10 |
SET STATISTICS IO ON SET STATISTICS TIME ON SELECT Product.Name AS [Product Name] , Product.ProductNumber AS [Product Number] , Sales.UnitPrice AS [Sales UnitPrice], dbo.ufnGetStock(Product.ProductID) FROM Production.Product Product INNER JOIN Sales.SalesOrderDetail Sales ON Product.ProductID = Sales.ProductID |
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.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT Product.Name AS [Product Name] , Product.ProductNumber AS [Product Number] , Sales.UnitPrice AS [Sales UnitPrice], Sales.CarrierTrackingNumber, Sales.UnitPriceDiscount, dbo.ufnGetStock(Product.ProductID) AS [Stock Level] FROM Production.Product Product INNER JOIN Sales.SalesOrderDetail Sales ON Product.ProductID = Sales.ProductID |
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.
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.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT Product.Name AS [Product Name] , Product.ProductNumber AS [Product Number] , Sales.UnitPrice AS [Sales UnitPrice], Sales.CarrierTrackingNumber, Sales.UnitPriceDiscount, dbo.ufnGetStock(Product.ProductID) FROM Production.Product Product INNER JOIN Sales.SalesOrderDetail Sales ON Product.ProductID = Sales.ProductID |
When we query the dm_exec_query_stats view, we can see the execution value of the ufnGetStock function.
|
1 2 3 4 5 6 7 8 9 |
SELECT text , execution_count , total_logical_reads / execution_count AvgReads , total_worker_time / execution_count AvgWorkersTime , total_elapsed_time / execution_count AvgElapsedTime FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) WHERE text LIKE '%ufnGetStock%' |
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.
|
1 |
ALTER DATABASE AdventureWorks2017 SET COMPATIBILITY_LEVEL = 150; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SET STATISTICS IO ON SET STATISTICS TIME ON SELECT Product.Name AS [Product Name] , Product.ProductNumber AS [Product Number] , Sales.UnitPrice AS [Sales UnitPrice], Sales.CarrierTrackingNumber, Sales.UnitPriceDiscount, dbo.ufnGetStock(Product.ProductID) FROM Production.Product Product INNER JOIN Sales.SalesOrderDetail Sales ON Product.ProductID = Sales.ProductID |
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.
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.
The following query can disable or enable scalar UDF inlining at the database level.
|
1 |
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON/OFF |
We can disable or enable scalar UDF inlining when we create or alter a scalar function using the WITH INLINE syntax.
|
1 2 3 4 5 6 7 |
CREATE OR ALTER FUNCTION UDF(@Param1 [int]) RETURNS [int] WITH INLINE = OFF /ON AS BEGIN --Code block END |
As a last, we can disable the scalar UDF inlining at the query level using the DISABLE_TSQL_SCALAR_UDF_INLINING keyword.
|
1 |
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING')); |
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.
|
1 2 3 4 5 6 7 |
SELECT * FROM( select OBJECT_NAME(object_id) as ObjectName,CASE is_inlineable WHEN 0 THEN 'Not Inlineable' WHEN 1 THEN 'Is Inlineable' END AS [Inlineable Status] from sys.sql_modules ) AS TMP_TBL WHERE ObjectName='ufnGetStock' |
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
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023