KB4538581 - FIX: Scalar UDF Inlining issues in SQL Server 2022 and 2019

Symptoms

User-Defined Functions (UDFs) that are implemented in Transact-SQL and that return a single data value are known as T-SQL Scalar User-Defined Functions (UDFs). 

Microsoft SQL Server 2019 introduced the Scalar UDF Inlining feature. This feature can improve the performance of queries that invoke T-SQL Scalar UDFs, in which UDF execution is the main bottleneck. T-SQL Scalar UDF Inlining automatically transforms inlineable UDFs into relational expressions. 

This cumulative update includes several fixes across the following areas for scenarios in which a query that uses Scalar UDF Inlining might return an error or unexpected results:

• Type mismatch error if the return type of the UDF is a sql_variant (added in SQL Server 2019 CU2)

• UDF invocation from sp_executesql aborts execution (added in SQL Server 2019 CU2)

• UDFs referencing labels without an associated GOTO command return incorrect results (added in SQL Server 2019 CU2)

• Out-of-memory conditions and memory leaks occur because of very large scalar UDFs (added in SQL Server 2019 CU2)

• Uninitialized variables used in condition (IF-ELSE) statements cause errors (added in SQL Server 2019 CU2)

• Explicit conversion error if UDF has the sql_variant parameter (added in SQL Server 2019 CU7)

• Error occurs when the scalar function references CHECKSUM (added in SQL Server 2019 CU7)

• UDF invocation with a large number of scalar expression re-evaluations can cause a non-yielding scheduler error (added in SQL Server 2019 CU7)

  Note: The fix for this issue may sometimes cause a performance regression. To mitigate this performance regression, you can disable the original fix by turning on trace flag (TF) 13156.

  Microsoft is working on a fix for this performance regression that will be available in a future CU.

• Error occurs when the UDF referencing query uses OPTION (RECOMPILE) (added in SQL Server 2019 CU7)

• Permission errors occur when views invoke inlined UDFs (added in SQL Server 2019 CU9)

• Access violations occur when using the XEvent query_tsql_scalar_udf_inlined (added in SQL Server 2019 CU9)

• Inconsistent behavior due to Scalar UDF Inlining not respecting the QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n query hint (added in SQL Server 2019 CU9)

• Inlining UDFs contain aggregations that can cause non-yielding scheduler errors (added in SQL Server 2019 CU9)

• Out of memory errors due to a sequence of scalar operations on the same variable in conditional blocks (added in SQL Server 2019 CU9)

• Access violation can occur when an object invokes a scalar inlineable UDF (UDF1) with a scalar inlineable UDF (UDF2) that's used as an input parameter after upgrading to CU9 (added in SQL Server 2019 CU11)

• Inlining UDFs could cause errors with the error codes 6846, 1011, and 107 (added in SQL Server 2019 CU11)

• Inlining UDFs running as the non-sysadmin account could throw the error "A severe error occurred on the current command.  The results, if any, should be discarded" (added in SQL Server 2019 CU16)

• Access violation occurs when temporary tables are invoked inside UDFs by using synonyms (added in Microsoft SQL Server 2022 CU1 and SQL Server 2019 CU19)

• Access violation occurs if the UDF definition contains only GOTO labels and a RETURN statement (added in SQL Server 2022 CU1 and SQL Server 2019 CU19)

This cumulative update also blocks inlining in the following scenarios:

• If the UDF references certain intrinsic functions (for example, @@ROWCOUNT) that may alter the results when it's inlined (added in SQL Server 2019 CU2)

• When aggregate functions are passed as parameters to a scalar UDF (added in SQL Server 2019 CU2)

• If the UDF references built-in views (for example: OBJECT_ID) (added in SQL Server 2019 CU2)

• If the UDF uses XML methods (added in SQL Server 2019 CU4)

• If the UDF contains a SELECT with ORDER BY and without TOP 1 (added in SQL Server 2019 CU4)

• If the SELECT query performs an assignment in conjunction with the ORDER BY clause (for example, SELECT @x = @x +1 FROM table ORDER BY column_name) (added in SQL Server 2019 CU4)

• If the UDF contains multiple RETURN statements (added in SQL Server 2019 CU5)

• If the UDF is called from a RETURN statement (added in SQL Server 2019 CU5)

• If the UDF references the STRING_AGG function (added in SQL Server 2019 CU5)

• If the UDF definition references remote tables (added in SQL Server 2019 CU6)

• If the UDF-calling query uses GROUPING SETS, CUBE, or ROLLUP (added in SQL Server 2019 CU6)

• If the UDF-calling query contains a variable that's used as a UDF parameter for assignment (for example, SELECT @y=2, @x=UDF(@y)) (added in SQL Server 2019 CU6)

• If the UDF references encrypted columns (added in SQL Server 2019 CU11)

• If the UDF contains references to WITH XMLNAMESPACES (added in SQL Server 2019 CU11)

• If the query invoking the UDF has Common Table Expressions (CTEs) (added in SQL Server 2019 CU11)

• If the UDF definition contains temporary tables or synonyms for temporary tables (added in SQL Server 2022 CU1 and SQL Server 2019 CU19)

After you upgrade to SQL Server 2019 CU2, but before you upgrade to SQL Server 2019 CU5, you should re-evaluate whether a UDF is eligible for inlining. To do this, refresh eligible scalar UDFs through either of the following methods:

• Run sp_refreshsqlmodule for applicable scalar UDFs. For more information about this system stored procedure, see sp_refreshsqlmodule (Transact-SQL).

• Alter or re-create the existing scalar UDF by using existing definitions, permissions, and set properties. For more information, see ALTER FUNCTION (Transact-SQL).

The following command generates a script to refresh the metadata of existing inlined scalar UDFs:

/*

Generates a script that can be used to refresh all active inlineable scalar UDFs.

Note The sp_refreshsqlmodule command does not affect any permissions, extended properties, or SET options that are associated with the object.

*/

SELECT 'EXECUTE sys.sp_refreshsqlmodule ''[' + OBJECT_SCHEMA_NAME(object_id) + +'].' + '[' + OBJECT_NAME(object_id)

+ ']'';' AS 'RefreshStatement'

FROM sys.sql_modules

WHERE is_inlineable = 1

AND inline_type = 1;

GO

Note: After you upgrade to SQL Server 2019 CU5, we will automatically derive UDF inlineability during compilation again.

Resolution

This issue is fixed in the following cumulative updates for SQL Server:

• Cumulative Update 1 for SQL Server 2022

• Cumulative Update 19 for SQL Server 2019

Status

Microsoft has confirmed that this is an issue in the Microsoft products that are listed in the "Applies to" section.

References

Learn about the terminology that Microsoft uses to describe software updates.