This article explores the scalar UDF performance issues and improvements in Azure SQL Database using UDF Inlining.
Introduction
SQL Server 2017 and 2019 include intelligent query processing (IQP) features for improving query performance without modifying the code and minimum implementation efforts. The following diagram shows the IQP features and their availability in Azure SQL Database, SQL Server 2017,2019. These IQP features are as below:
- Adaptive QP
- Table variable deferred compilation
- Batch mode on Rowstore
- T-SQL scalar UDF Inlining
- Approximate QP – Approximate count distinct
This article will cover the IQP feature – Scalar UDF Inlining with Azure SQL Database in detail. Let’s understand scalar UDF and its performance impact on running UDF queries.
Requirements
You should have an Azure SQL Database for executing queries. If you are not familiar with Azure, refer to SQLShack articles from the link – Azure – SQL Shack – articles about database auditing, server performance, data recovery, and more
Scalar User-Defined functions (UDF)
The T-SQL scalar user-defined function returns a single data value. It helps to build complex logic without writing complex queries. UDFs are commonly known for performance issues in most cases.
Let’s change and verify the Azure SQL Database compatibility level to SQL Server 2017(140) for the demonstration.
Note: Do not change database compatibility level unless you have a specific reason to do so. The application code might not work correctly in older compatibility levels.
|
1 2 3 4 5 6 |
ALTER DATABASE AdventureWorks2019 SET COMPATIBILITY_LEVEL = 140; go SELECT compatibility_level FROM [sys].[databases] WHERE [name] = 'AdventureWorks2019'; |
The following scalar function returns the sum of products quantities from the [Production].[ProductInventory] table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE FUNCTION [dbo].[ufnGetStock](@ProductID [int]) RETURNS [int] AS BEGIN DECLARE @ret int; SELECT @ret = SUM(p.[Quantity]) FROM [Production].[ProductInventory] p WHERE p.[ProductID] = @ProductID AND p.[LocationID] = '6'; IF (@ret IS NULL) SET @ret = 0 RETURN @ret END; GO |
In the following select statement, we refer to the UDF for retrieving the stock quantity.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT Product.Name AS [Product Name] , dbo.ufnGetStock(Product.ProductID) as stockquantity, Product.ProductNumber AS [Product Number] , Sales.UnitPrice AS [Sales UnitPrice], Sales.CarrierTrackingNumber, Sales.UnitPriceDiscount FROM Production.Product Product INNER JOIN Sales.SalesOrderDetail Sales ON Product.ProductID = Sales.ProductID |
Before we execute these stored procedures, enable the actual execution plan in SSMS. To enable the actual execution plan, press CTRL + M before running the query.
As shown below, the execution plan uses clustered index scan and computer scaler operator to get results using UDF.
In the compute scalar, we can see the input rows as 121317. It means that the SQL Server needs to execute the UDF many times for retrieving the records.
To get more details of an operator, view the properties in SSMS. You can see the following query time statistics for
user-defined functions.
-
- UdfCpu Time
- UdfElapsed Time
We can get the number of UDF executions using the dynamic management function (DMF) sys.dm_exec_function_stats. The function returns aggregate performance statistics for the cached function.
As per the output, it executed UDF 30997 times in my lab environment.
|
1 2 3 |
SELECT [function] = OBJECT_NAME([object_id]), execution_count FROM sys.dm_exec_function_stats WHERE object_name(object_id) IS NOT NULL; |
The scalar UDF shows a noticeable performance impact if we have a large number of affected rows, and its performance impact is due to the following reasons.
- Lack of costing: SQL Server query optimizer does not cost the scalar operators since it is considered cheap from the cost point of view. Therefore, the UDFs are not optimized by SQL Server
- Iterative invocation: The scalar UDFs involved iteratively; therefore, it causes additional costs due to context switching
- Interpreted execution: The UDFs interpretation is done by statement level. Therefore, it executes the batch of statements sequentially
- Serial execution: SQL Server does not use intra-query parallelism in queries that use UDFs
Azure SQL Database Scalar UDF Inlining
Intelligent query processing (IQP) focuses on improving query performance without you changing the code. The Scalar UDF Inlining feature transforms UDF into the scalar expressions or subqueries. Therefore, the query optimizer can generate their cost and optimize them. It optimizes the resource usage such as CPU, memory allocation, and you get results quickly since SQL does not need to execute the UDF for the ‘N’ number of rows returned in the output.
The Scalar UDF Inlining feature in Azure SQL Database of compatibility level 150 is automatically enabled. It is a database scoped feature that you can verify from the sys.database_scoped_configurations. The column is_value_default shows that this TSQL scalar UDF Inlining feature is enabled by default.
|
1 2 |
SELECT * FROM sys.database_scoped_configurations WHERE name='TSQL_SCALAR_UDF_INLINING' |
You get a different execution plan if we re-execute the query on Azure SQL Database with compatibility level 150. The query execution plan does not use compute scalar operator.
The new execution plan does not show the parameters – UdfCpuTime and UdfElapsedTime as seen in the plan without UDF Inlining.
s
Previously, without the scalar UDF Inlining, the query optimizer could not generate the parallel plan. The following image shows a parallel plan for the Inlining query.
Image Reference: Microsoft docs
Inlining scalar UDFs requirements
SQL Server can inline the scalar UDF if the following conditions are true.
-
The UDF has any of the following constructs.
- Declare, Set
- Select
- If / Else
- Return
- UDF
- Relational operators such as EXISTS, ISNULL
- It does not invoke intrinsic functions such as GETDATE()
- The UDF uses the EXECUTE AS CALLER clause
- You do not use UDF in the ORDER BY clause
- UDF is not a partitioned function
- It should not refer to common table expressions (CTE)
- The UDF should not have multiple RETURN statements
- You cannot use encrypted columns in the UDFs
- The UDF should not refer to remote tables or built-in views
You can refer to Scalar UDF Inlining for more details on scalar UDF Inlining requirements.
How to verify the UDF Inlining has happened or not
As stated earlier, the SQL Server transforms the UDF into a relational expression as part of UDF Inlining. If you observe the XML execution plan and search for the <UserDefinedFunction> XML node.
- If the plan has <UserDefinedFunction> XML node, it shows Inlining has not happened. For example, the following XML plan is for SQL queries with compatibility level 140.
- If there is no <UserDefinedFunction> XML node, it stats the UDF Inlining is successful.
If a UDF is not inlined, you can look at the sys.sql_modules column is_inlineable after creating the scalar user-defined function. For example, the UDF [ufnGetStock ] returns value 1 that means it can benefit from UDF Inlining in Azure SQL Database.
|
1 2 3 |
SELECT is_inlineable, object_name(object_id) AS objectname, definition FROM sys.sql_modules WHERE object_name(object_id) IS NOT NULL ORDER BY is_inlineable |
Disabling Scalar UDF Inlining
Users can disable the Scalar UDF Inlining feature of Intelligent query processing as per their requirements. You can disable scalar UDF Inlining in the following ways.
- Changing database compatibility level: The Scalar UDF Inlining feature is available for Azure SQL Database or Managed instance with compatibility level 150. You can change the compatibility level to lower, such as 140, 130 to disable the functionality
|
1 2 3 |
ALTER DATABASE [DBName] SET COMPATIBILITY_LEVEL = 130; go |
Note: You must not change the compatibility level unless for a specific requirement. It might break your code, and certain features might not work as expected.
- Use database scoped configurations:
You can turn off the scalar UDF Inlining using the database scoped configurations on an individual database. Run the following statement on the database for which you want to disable the feature.
|
1 |
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF; |
- Disable UDF Inlining for a specific query
In specific requirements, you want to disable the UDF Inlining for a specific query. You can specify the query hint DISABLE_TSQL_SCALAR_UDF_INLINING as shown in the following query.
|
1 2 3 4 5 6 7 |
SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT)) FROM LINEITEM INNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING')); |
Note: The query hint always takes precedence over the compatibility level or the database scoped configuration.
- Use the INLINE clause: We can disable scalar UDF Inlining by using the INLINE clause in the Create or Alter function, as shown below
|
1 2 3 4 5 6 7 |
CREATE OR ALTER FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2)) RETURNS DECIMAL (12,2) WITH INLINE = OFF --Disable IDF Inlining AS BEGIN RETURN @price * (1 - @discount); END; |
If we have the function with INLINE=OFF, SQL Server does not perform UDF Inlining for any T-SQL that references the UDF. You can re-enable the UDF for Inlining with the INLINE=ON statement as below.
|
1 2 3 4 5 6 7 |
CREATE OR ALTER FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2)) RETURNS DECIMAL (12,2) WITH INLINE = ON AS BEGIN RETURN @price * (1 - @discount); END; |
Note: The argument INLINE=ON/OFF is optional. If we do not specify it, SQL Server automatically sets it based on whether the UDF can be inlined or not.
Conclusion
This article explored the Scalar UDF Inlining for Azure SQL Database and managed instances. It improves the query performance by optimizing the way SQL Server treats UDFs. With the compatibility level 150, SQL Server transforms user-defined functions into scalar expressions or scalar subqueries. You can control the Inlining behavior at the database, query, or functions level.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023