Did My UDF Inlined?

December 30, 2019

With the release of SQL Server 2019, CTP 2.1 new features were added to Intelligent Query Processing (QP) feature family. One of those features was Scalar UDF Inlining. You can read about the announcement by Karthik Ramachandra from Microsoft in this blog post. Karthik explained why UDFs are a problem when inlining is not done.

Another good post “Scalar UDF Inlining in SQL Server 2019” about Scalar UDF Inlining I recommend is by Aaron Bertrand.

Karthik and Aaron emphasized on caveats that every function will not be inlined in SQL Server 2019. There are limitations to when a function may not be inlined. Read Microsoft documentation ‘Scalar UDF Inlining‘ to get a broad list of criteria when a function is inalienable and when a function might not be inlined. Even though your function meets all the criteria listed in the documentation you are not guaranteed to achieve inlining with your function.

From Microsoft Documentation:

If a scalar UDF is inlineable, it does not imply that it will always be inlined. SQL Server will decide (on a per-query, per-UDF basis) whether to inline a UDF or not.

How do you find if your function was inlined during execution?

Starting SSMS 18.2 a new attribute was added in QueryPlan. When the inline scalar UDF feature is enabled ‘ContainsInlineScalarTsqludfs’ value will be true. Let’s look at this in action. Run below tsql using the latest version of SSMS and after turning on the actual execution plan in SSMS.

-- Script copied form:
-- https://blogs.msdn.microsoft.com/sql_server_team/more-showplan-enhancements-udfs/
-- Run this in SQL2019
-- Download AdventureWorks backup
-- https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

-- Create User Defined Function
USE [AdventureWorks];
GO
DROP FUNCTION IF EXISTS ufn_CategorizePrice;
GO
CREATE FUNCTION ufn_CategorizePrice(@Price money)
RETURNS NVARCHAR(50)
AS
BEGIN
    DECLARE @PriceCategory NVARCHAR(50)

    IF @Price < 100 SELECT @PriceCategory = 'Cheap'
    IF @Price BETWEEN 101 and 500 SELECT @PriceCategory =  'Mid Price'
    IF @Price BETWEEN 501 and 1000 SELECT @PriceCategory =  'Expensive'
    IF @Price > 1001 SELECT @PriceCategory =  'Unaffordable'
    RETURN @PriceCategory 
END;
GO
--Run the function
USE AdventureWorks
GO
SELECT dbo.ufn_CategorizePrice(UnitPrice), 
    SalesOrderID, SalesOrderDetailID, 
		CarrierTrackingNumber, OrderQty, 
		ProductID, SpecialOfferID, 
		UnitPrice, UnitPriceDiscount, 
    LineTotal, rowguid, ModifiedDate 
FROM Sales.SalesOrderDetail
GO
Properties of Root Node, under Misc showing the new attribute ‘Contains Inline Scalar Tsql Udfs value is True.

The sys.sql_modules catalog view includes two new columns for SQL Server 2019.

  • is_inlineable Indicates whether the module is inlineable or not. Inlineability is based on the conditions specified here. One piece of caution, inlineable=1 does not guarantee that inlining happened, you need to confirm by inspecting the actual execution plan as explained above.
  • inline_type Indicates whether inlining is turned on for the module currently.

If your function is not inlined you will NOT see the attribute ‘Contains Inline Scalar Tsql Udfs’ in the root node. Instead, you will see two different attributes under ‘QueryTimeStats’ named ‘UdfCpuTime’ and ‘UdfElapsedTime’. I explained this in detail here.

If you want to manually disable function inlining, you have few options:

  • Using the INLINE clause in the CREATE FUNCTION or ALTER FUNCTION statement.
  • Disable Scalar UDF Inlining for a specific Database by using ‘TSQL_SCALAR_UDF_INLINING’ Database scoped configuration setting.
  • Disable Scalar UDF Inlining for a specific query by using this hint in a query ‘DISABLE_TSQL_SCALAR_UDF_INLINING’.

1 reply on “Did My UDF Inlined?”

Leave a Reply

Your email address will not be published. Required fields are marked *

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