Scalar UDF Inlining now available on Azure SQL

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

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 

 

Pooja_Kamath_6-1631018358092.png

 

 

Pooja_Kamath_7-1631018358081.png

 

 

 Pooja_Kamath_8-1631018358083.png

 

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 

 

Pooja_Kamath_9-1631018358097.png

 

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 

 

Pooja_Kamath_10-1631018358094.png

 

 

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 

 

Pooja_Kamath_11-1631018358096.png

 

 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 featureRead 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 databasestatement, 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 

 

If you have feedback on this feature or other features in the Intelligent QP feature family, please email us atIntelligentQP@microsoft.com . 

 

 

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.