Advanced Data Solutions : Scalar UDF Inlining now available on Azure SQL

Scalar UDF inlining is now available for Azure SQL Managed Instance and Azure SQL Database. This is a feature that is part of the Intelligent Query Processing (QP) family & was first introduced in SQL Server 2019 .

The goal of the Scalar UDF inlining feature is to improve performance of queries that invoke T-SQL scalar UDFs, where UDF execution is the main bottleneck.
A key aspect of this improvement is that it falls under the category of “It just runs faster”. You will automatically be eligible for intelligent query processing without changing any code provided you are running applicable database compatibility level & the Scalar UDF conforms to the requirements

Let’s have a quick recap on the “Scalar UDF Inlining” feature:

What is a Scalar UDF?

User-Defined Functions (UDFs) that are implemented in Transact-SQL and return a single data value are referred to as T-SQL Scalar User-Defined Functions.

T-SQL UDFs are an elegant way of achieving code reuse and modularity across Transact-SQL queries. They help in building up complex logic without requiring expertise in writing complex SQL queries

Performance of Scalar User-Defined Functions:
Historically, UDFs in SQL server were known to perform poorly. This has implicitly resulted in customers avoiding UDFs whenever possible. Some of the main reasons for poor performance were the following

Iterative execution: UDFs are invoked in an iterative manner, once per qualifying tuple. This incurs additional costs due to repeated context switching,

Lack of costing: The SQL Query Optimizer treats UDFs as inexpensive black boxes and does not cost them currently.

Interpreted execution: UDFs are evaluated as a batch of statements which are executed sequentially. In other words, UDFs are effectively interpreted statement-by-statement.

Serial execution: SQL Server does not allow intra-query parallelism in queries that invoke UDFs.

Let’s look at a simple example of a Scalar UDF to understand this

--Create a simple Scalar UDF  

DROP FUNCTION IF EXISTS [dbo].Left5FromString;  
GO  

CREATE FUNCTION [dbo].[Left5FromString](@String VARCHAR(50)) 
RETURNS VARCHAR(5) 
AS 

-- Returns the 5 left characters of the last name 

BEGIN 

DECLARE  VARCHAR(5); 
SET  = LEFT(@String, 5) 
RETURN  

END; 

GO 

 

-- Now execute a query that references it 
SELECT CarrierTrackingNumber, dbo.Left5FromString(CarrierTrackingNumber), * 
FROM Sales.SalesOrderDetail 
ORDER BY 2 DESC

The execution plan for this query in Azure SQL Managed Instance database (compatibility level 140 – which will have inlining disabled ) is as follows:

--Notice how SQL Server does not allow intra-query parallelism in queries that invoke UDFs. They are invoked in an iterative manner, once per qualifying tuple

Using scalar (UDF) generally causes SQL Server performance issues when used on a large number of rows, because it is evaluated for each row returned by the query as seen in the Xevents.

A detailed trace can also impact query performance of a query with a scalar UDF

How does the Scalar UDF inlining feature help?

With this “Scalar UDF inlining feature”, scalar UDFs are automatically transformed into scalar expressions or scalar subqueries that are substituted in the calling query in place of the UDF operator.

These expressions and subqueries are then optimized. As a result, the query plan will no longer have a user-defined function operator, but its effects will be observed in the plan, like views or inline TVFs.
The operations inside the UDF are now no longer a black box, and hence the query optimizer is able to cost and optimize those operations

Let’s look at the same scalar UDF when inlining is enabled

--Notice how the inlined query can produce a parallel plan

In this execution plan, will not see the UdfCpuTime and UdfElapsedTime attributes on the execution plan as seen in the previous plan because the query does not invoke any scalar function

You will also be able to see if your UDF is inlined through the Xevent query_tsql_scalar_udf_inlined

NOTE: These numbers are based on a AdventureWorks database, running in Azure SQL Managed Instance- General Purpose Gen5 (96 GB, 8 vCores). The numbers include compilation and execution time with a cold procedure cache and buffer pool & was being monitored using statement level Extended events .The default configuration was used, and no other indexes were created.

How do I enable Scalar UDF inlining for my Azure SQL Managed Instance?

In Azure SQL DB and Azure SQL Managed Instance Scalar UDF inlining will be turned on by default. Workloads are automatically eligible for Scalar UDF Inlining if compatibility level for the database is 150 .Apart from this, there are no other changes required to be made to UDFs or queries to take advantage of this feature. Read more at Enabling Scalar UDF Inlining

Can I disable Scalar UDF inlining?

Yes absolutely. The adoption of Scalar UDF inlining has been great and we see the count of queries taking tangible advantage of the feature on a daily basis is in the millions. We did see a few narrowly scoped issues post SQL 2019 which have been fixed in the subsequent Cumulative updates for On-prem KB4538581 - FIX: Scalar UDF Inlining issues in SQL Server 2019 (microsoft.com) and these fixes have been enabled for Azure too.

But if Scalar UDF Inlining feature causes an issue for anyone, there is a simple immediate mitigation: Scalar UDF inlining can be disabled at the database, statement, or UDF scope while still maintaining database compatibility level 150 and higher. Check Disabling Scalar UDF inlining

What kind of scalar UDFs are inlineable?
A fairly broad set of scalar UDFs are inlineable currently. There are a few limitations such as the T-SQL constructs allowed in the UDF. Read the entire list at- Inlineable scalar UDFs requirements

Additional Resources