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:
DROP FUNCTION IF EXISTS [dbo].Left5FromString;
CREATE FUNCTION [dbo].[Left5FromString](@String VARCHAR(50))
-- Returns the 5 left characters of the last name
DECLARE @left VARCHAR(5);
SET @left = LEFT(@String, 5)
-- Now execute a query that references it
SELECT CarrierTrackingNumber, dbo.Left5FromString(CarrierTrackingNumber), *
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.Running 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 scalarexpressions 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 may also use Xeventquery_tsql_scalar_udf_inlined to see if your UDF is inlined
NOTE: These numbers are based on a AdventureWorks database, running in Azure SQL Managed Instance- Business Critical Gen5 (8vcores). The numbers include compilation and execution time with a cold procedure cache and buffer pool for a simple scalar UDF example.The default configuration was used, and no other indexes were created. Note that running a detailed trace can also impact query performance of a query with a scalar UDF .
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 atEnabling 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