What is Scalar UDF Inlining?
You can read about Scalar UDF Inlining in great details here: https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15, but in general, it’s exactly what how it sounds. UDF is an abbreviation for User Defined Function, and scalar means it returns a single value of simple type. Like int, varchar, float, etc. To improve query performance, SQL Server 2019 do not call functions for each row, but inline them (i.e. copy-paste function text right into the query text). This makes function’s text a part of query’s text. So, SQL Server engine can make query plan taking in account function’s calculation/logic. In contrast, when calling function, it acts a a black box and therefore cannot be optimized/included in the plan. Sounds good? Yes, but…
Now let’s look at… CPUs. Why? There is a particular feature in most of the modern high-performance CPUs, which behaves similar to Scalar UDF Inlining. It’s called Speculative Execution. In CPUs with long word instructions (specifically with x86) architecture, execution of a command is a long story. A command is split in stages and sent to a pipeline. But to maximize command throughput, next command starts executing before previous command is fully completed. This increase command flow, but put a huge drawback on branching. If a program branches on a condition going in one direction, but Speculative Execution went another direction, then the whole pipeline gets reset and performance goes low. However, Speculative Execution is transparent to program’s user. If pipeline is reset, then any exceptions (such as division by zero) are also whitdrawn, when they happened in not-comes-to-reality branch. But how’s that related to UDF inlining?
Let’s consider the following SQL scalar UDF definition:
CREATE FUNCTION fn_Test(@input int) RETURNS INT AS BEGIN DECLARE @result INT IF not (Subfunction will fail if this condition is true) BEGIN SET @result = fn_SubFunction(@input) END ELSE BEGIN SET @result = @input END RETURN @result END
This user defined function doesn’t look like a trap. A non-SQL programmer doesn’t expect any tricks — the program flow checks if the sub-function can be safely executed, then only executes it if it’s safe. And this is exactly true for all SQL Server versions before 2019. With SQL Server 2019 and UDF Inlining on, all branches of the IF condition are executed simultaneously, wich is pretty similar to Speculative Execution. But, unlike Speculative Execution, any exceptions happened in non-realized branches makes the top function fails too. In other words, this function works OK at SQL Server 2017, but fails when run at SQL Server 2019 with UDF Inlining on.
How this affects SCOM.
When SCOM imports a management pack with classes, which defines default property values, these values are passed through the
fn_GetDefaultGuidPropValue function. It’s seen, that this function does nothing to input value (just passes it through) unless property type is 5 (GUID) and default value contains ‘-‘ character. The
fn_MPReferencedObjectId sub-function simply casts input value to
CREATE function [dbo].[fn_GetDefaultGuidPropValue] (@DefaultValue nvarchar(256),@MPName nvarchar(256), @MPKeyToken nvarchar(32),@PropType tinyint) RETURNS nvarchar(3000) AS BEGIN DECLARE @ConvertValue nvarchar(3000) IF @PropType = 5 BEGIN -- If we are passed in a Guid, just return it. Due to xsd grammar, only GUIDS will contain the '-' character IF CHARINDEX(N'-', @DefaultValue, 1) > 0 SET @ConvertValue = @DefaultValue ELSE SET @ConvertValue = dbo.fn_MPReferencedObjectId(@MPName, @MPKeyToken, @DefaultValue) END ELSE SET @ConvertValue = @DefaultValue RETURN @ConvertValue END
My management pack (https://github.com/MaxxVolk/Maximus.Connectivity.Monitoring) contains classes with integer properties, which used to be defaulted to “-1” (negative one), which is absolutely valid integer value. However, when it comes to the
fn_GetDefaultGuidPropValue function, it causes SQL error. Despite PropType for integer is 0, so the branch with call to
fn_MPReferencedObjectId must not be executed, SQL Server engine executes it anyway, due to inlining and because the input value contains ‘-‘ character. And when
fn_MPReferencedObjectId failed to convert ‘-1’ to GUID,
fn_GetDefaultGuidPropValue also fails. And MP import fails as well.
Well, a possible workaround is very simple. Regardless of property datatype in your management packs, never use ‘-‘ in default values. Say don’t use negative numbers for integer properties, neither use dash in words like “first-class”, etc.
Who is more brave and don’t afraid of touching SCOM DB, and alter the function disabling inlining: https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15#disabling-scalar-udf-inlining-without-changing-the-compatibility-level.
No doubts, is a developer is aware of such behavior, the issue can be avoided. Say, the function can use try-catch block or use try convert, instead of explicit convert, etc. However, I’m tending to tread this as a bug in SQL Server, or at least as a serious lack of backward compatibility. And in this particular case, Microsoft actually declared SCOM being compatible with SQL Server 2019 since UR 8. But this issue shows they were wrong.