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.
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
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’.