SQL 2019 UDF (User defined function) inlining

,

SQL Server 2019 brings a lot of great new features. Many are introduced by the IQP (Intelligent Query Processing) features and greatly improve query performance.

 

Some time ago I posted about Halloween protection and how using SCHEMABINDING in your Scalar UDFs can eliminate the need for ugly SPOOL operators. You can read more about that here: https://sqltechblog.com/2016/10/31/why-halloween-slowed-your-queries/

Microsoft has a great post on this new feature here:

https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15

As for the Halloween protection, let’s take a quick look at how this behaves in SQL Server 2019.

As a quick refresher SQL Server 2017 and older behave like this without using SCHEMABINDING.

image

Note the first query, which uses the scalar UDF, is higher cost due to the SPOOL.

If we run this in SQL Server 2019 using the 150 compatibility mode, what happens?

image

Woohoo! The SPOOL is gone without having to alter the code and anything that improves performance without code changes is amazing!

Gotchas

As you may have guessed. UDF inlining is quite new and will be improving over time as the feature matures. You may note that there are hotfixes that have been released to fix some bugs related to this new feature.  (https://support.microsoft.com/en-us/help/4538581/fix-scalar-udf-inlining-issues-in-sql-server-2019)

As of today, SQL Server 2019 CU4 is the latest and it would seem there’s still some work to be done.

The following code block seems to reproduce a bug in the current latest:

CREATE DATABASE ReproBug;

GO

USE ReproBug

GO

CREATE TABLE Contact (id int identity(1,1), fname varchar(50), lname varchar(50));

GO

USE ReproBug

GO

CREATE OR ALTER PROCEDURE [dbo].[proc_InsertContact]

(

@lastName     VARCHAR(100),

@firstName     VARCHAR(100)

)

AS

BEGIN

BEGIN TRAN

BEGIN TRY

INSERT INTO Contact (fname, lname) values (@lastName, @firstName);

COMMIT

return 0;

END TRY

BEGIN CATCH

ROLLBACK TRAN

RETURN dbo.fx_GetErrorNumber(Error_number());

END CATCH

END

GO

CREATE OR ALTER FUNCTION [dbo].[fx_GetErrorNumber] (@errorCode INT = NULL)

RETURNS INT  AS

BEGIN

DECLARE @retValue INT

SET @retValue = 500

IF(@errorCode = 2627)

SET @retValue = 501

RETURN @retValue

END

GO

exec dbo.[proc_InsertContact] @lastName=’Vader’,@firstName=’Darth’;

Msg 596, Level 21, State 1, Line 43

Cannot continue the execution because the session is in the kill state.

Msg 0, Level 20, State 0, Line 43

A severe error occurred on the current command.  The results, if any, should be discarded.

Is there a work-around?

Yes; you can set the compatibility mode to 140 or change the UDF to disable inlining. Either change the UDF itself with “WITH INLINE = OFF” or update the database to disable inlining “ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

This is an issue I’m watching and I’ll follow up on this post when this issue is resolved.

Original post (opens in new tab)

Rate

Share

Share

Rate