sql.sasquatch: SQL Server 2019 Scalar UDF inlining

*** Update 2020 April 7 ***

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.

Msg 701, Level 17, State 123, Line 6
There is insufficient system memory in resource pool 'default' to run this query.
Location:
Expression: false
SPID: 61
Process ID: 4916
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed.
Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.
Froid MEMORY_CLERK_SQLOPTIMIZER

4 comments:

Martin GuthNovember 25, 2019 at 12:27 AM
Amazing...thanks for bringing this up. At first I thought "Well who on earth is gonna write a function like that?"...however reality often told me that such thoughts often are misleading. One more reason to thoroughly test new features based on your environment and your workload before putting them into production.
Pedro LopesNovember 26, 2019 at 10:14 AM
We have a fix scheduled for SQL 2019 CU2. It will be in Azure ASAP too. It's a narrow case, but if Scalar UDF Inlining or other #IQP feature causes an issue for anyone, there is a simple immediate mitigation: disable it via DB scoped config or USE HINT https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query#use_hint

Pedro Lopes - MS

sql.sasquatch

Tuesday, November 19, 2019

SQL Server 2019 Scalar UDF inlining - OOM in some cases

4 comments:

sql.sasquatch Archive