The issue described below was corrected in kb4536075, with the fix for the scalar UDF inlining OOMs first available in SQL Server 2019 CU2.
FIX: Scalar UDF Inlining issues in SQL Server 2019
https://support.microsoft.com/en-us/help/4538581/fix-scalar-udf-inlining-issues-in-sql-server-2019
SQL Server 2019 CU2
https://support.microsoft.com/en-us/help/4536075/cumulative-update-2-for-sql-server-2019
*** end update ***
Here's a little something I stumbled across. A caution about scalar UDF inlining.
Well, ok, maybe Joe Obbish stumbled across it first :-)
Today's adventure is on my laptop. Because no lie this ugly UDF combined with current UDF inlining memory consumption will take down your server no matter *how* big it is.
Here's my laptop SQL Server version and some important database details.
OK, let's create a scalar UDF with a few logic branches. The function is nonsense, I'm sorry. But if you try this... you can try making it as sensible as you'd like. :-)
In the function below there is one IF, 25 ELSE IFs, and 1 ELSE.
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO CREATE OR ALTER FUNCTION dbo.test__inline_udf ( @in_param nvarchar(250) ) RETURNS nvarchar(250) AS BEGIN DECLARE @retValue nvarchar(250) = @in_param IF @in_param = N'A' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'1' END END ELSE IF @in_param = N'B' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'2' END END ELSE IF @in_param = N'C' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'3' END END ELSE IF @in_param = N'D' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'4' END END ELSE IF @in_param = N'E' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'5' END END ELSE IF @in_param = N'F' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'6' END END ELSE IF @in_param = N'G' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'7' END END ELSE IF @in_param = N'H' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'8' END END ELSE IF @in_param = N'I' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'9' END END ELSE IF @in_param = N'J' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'10' END END ELSE IF @in_param = N'K' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'11' END END ELSE IF @in_param = N'L' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'12' END END ELSE IF @in_param = N'M' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'13' END END ELSE IF @in_param = N'N' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'14' END END ELSE IF @in_param = N'O' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'15' END END ELSE IF @in_param = N'P' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'16' END END ELSE IF @in_param = N'Q' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'17' END END ELSE IF @in_param = N'R' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'18' END END ELSE IF @in_param = N'S' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'19' END END ELSE IF @in_param = N'T' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'20' END END ELSE IF @in_param = N'U' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'21' END END ELSE IF @in_param = N'V' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'22' END END ELSE IF @in_param = N'W' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'23' END END ELSE IF @in_param = N'X' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'24' END END ELSE IF @in_param = N'Y' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'25' END END ELSE IF @in_param = N'Z' BEGIN IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) BEGIN SET @retValue = N'26' END END ELSE BEGIN SET @retValue = N'00' END RETURN @retValue END
On my laptop, the following result took from 8 to 10 seconds repeatedly.
I'll tell you what. I ran this same function on a VM with 930 GB vRAM, with Max Server Memory set to 690GB. It ran for over 15 minutes before crashing. Not just crashing itself. Crashing any other query on the instance that was trying to allocate memory (eg stealing against a query memory grant). It had amassed over 500 GB of optimizer memory at that point, like this...
Wow. Now you'll notice that the big ol' server is a slightly different version. No matter. As far as I know, this behavior will be found on every version of SQL Server 2019 up to date (today is 2019 November 19).
Once the instance reached the total limit for "stealable" memory, the query crashed. Same thing if an estimated plan was requested - so its in compilation rather than execution that the aggressive memory consumption occurs. Once the OOM occurs, the large amount of optimizer memory is freed within a few seconds and the instance is back to normal for all other purposes.
Now, if I disable UDF inlining... the following result comes in well under 1 second.
Here's the final thing I can say about this for now...
If you generate an estimated plan for a query that tries to inline that UDF, it'll also crash due to excessive optimizer memory*.
I'll update this blog post in the future when a fix is available.
* well, I speculate that there is some amount of memory which may be sufficient to allow this to complete with generating an OOM. But once it's more than 500 GB does it really matter?
~~~~~
The scalar UDF used above is really, really bad :-)
So here's a nicer one.
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO CREATE OR ALTER FUNCTION dbo.test__inline_udf_2 ( @in_param INT ) RETURNS INT AS BEGIN DECLARE @retValue INT = @in_param IF @in_param = 1 BEGIN IF @retValue IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32) SET @retValue = 1 END ELSE IF @in_param = 2 BEGIN IF @retValue IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32) SET @retValue = 2 END ELSE IF @in_param = 3 BEGIN IF @retValue IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32) SET @retValue = 3 END ELSE IF @in_param = 4 BEGIN IF @retValue IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32) SET @retValue = 4 END ELSE IF @in_param = 5 BEGIN IF @retValue IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32) SET @retValue = 5 END ELSE SET @retValue = 0 RETURN @retValue END
No more implicit conversions. Took out some unnecessary BEGIN-END pairs. Its only one IF, four ELSE IFs, and an ELSE. Changed the NOT IN clauses to IN clauses.
And when i run it on my monster VM, it also generates an error.
That looks even more severe that the previous error. But its the same underlying condition: working on the inlining of the scalar UDF during plan compile kept gobbling optimizer memory until something gave way.
