Sql-server – After upgrading to SQL Server 2019, function throws “insufficient memory” error

I have moved an SQL Server 2012 Database (8GB in size) to a newly setup SQL Server 2019 virtual machine with the same memory and CPU configuration and changed the compatibility level to SQL Server 2019.

Everything in my application works fine except for one stored procedure that consists of one big SQL query with two parameters (and no fancy options). When this SP executed, it lets the memory of the SQL Server process go up to the specified max level and then returns an error:

"There is insufficient memory to run this query"

When I execute the SQL query (inside the stored procedure) in a separate query window of SSMS, it executes in no-time and returns the expected 300 rows. Also, when I change the DB's compatibility level to "SQL Server 2017" and execute the stored procedure, everything is ok.

I first thought it might be a parameter sniffing issue, but none of the workarounds helped (e.g. OPTION (RECOMPILE)).

I have drilled down the problem to the call of a scalar valued function. Every time I call this function, the memory error occurs.

Here is the DDL of the function (sorry, partly in German):

CREATE FUNCTION [dbo].[GetWtmTime] (
    @WorkTimeModelID uniqueidentifier,
    @Date DATETIME,
    @SequenceNo TINYINT)
  RETURNS VARCHAR(5)
AS
BEGIN
    -- SET DATEFIRST 7; has to be executed before calling this function
    DECLARE @WtmTime VARCHAR(5)
    DECLARE @WtmWeeks INT
    DECLARE @WtmTakeHolidays BIT
    DECLARE @WtmMaxMemberCount TINYINT
    SELECT @WtmWeeks = AnzahlWochen
         , @WtmTakeHolidays = ÜbernimmtFeiertage 
         , @WtmMaxMemberCount = MaxAnzahlMitglieder
    FROM Arbeitszeitmodelle 
    WHERE ArbeitszeitmodellID = @WorkTimeModelID;
    IF @WtmWeeks = 1
    BEGIN
        IF (dbo.IstFeiertag(@Date, 0) = 1     -- Holiday
            AND @WtmMaxMemberCount = 1)
        BEGIN
            IF @WtmTakeHolidays = 0
            BEGIN 
                IF @Date >= '20130901'
                    SET @WtmTime = 'KD'
                ELSE
                    SET @WtmTime = 'ZA';
            END ELSE
            BEGIN
                IF EXISTS ( SELECT *
                            FROM AzmWochen
                            WHERE ArbeitszeitmodellID = @WorkTimeModelID
                                AND Folgenummer = @SequenceNo
                                AND AzmZeitMo IN ('KD','T')
                                AND AzmZeitDi IN ('KD','T')
                                AND AzmZeitMi IN ('KD','T')
                                AND AzmZeitDo IN ('KD','T')
                                AND AzmZeitFr IN ('KD','T')
                                AND AzmZeitSa IN ('KD','T')
                                AND AzmZeitSo IN ('KD','T') )
                    SET @WtmTime = 'T';
                ELSE
                    SET @WtmTime = 'G';
            END
        END ELSE IF DATEPART(dw, @Date) = 1             -- Sunday
            SELECT @WtmTime = AzmZeitSo FROM AzmWochen 
                WHERE ArbeitszeitmodellID = @WorkTimeModelID
                    AND Folgenummer = @SequenceNo;
        ELSE IF DATEPART(dw, @Date) = 2             -- Monday
            SELECT @WtmTime = AzmZeitMo FROM AzmWochen 
                WHERE ArbeitszeitmodellID = @WorkTimeModelID
                    AND Folgenummer = @SequenceNo;
        ELSE IF DATEPART(dw, @Date) = 3             -- Tuesday
            SELECT @WtmTime = AzmZeitDi FROM AzmWochen 
                WHERE ArbeitszeitmodellID = @WorkTimeModelID
                    AND Folgenummer = @SequenceNo;
        ELSE IF DATEPART(dw, @Date) = 4             -- Wednesday
            SELECT @WtmTime = AzmZeitMi FROM AzmWochen 
                WHERE ArbeitszeitmodellID = @WorkTimeModelID
                    AND Folgenummer = @SequenceNo;
        ELSE IF DATEPART(dw, @Date) = 5             -- Thursday
            SELECT @WtmTime = AzmZeitDo FROM AzmWochen 
                WHERE ArbeitszeitmodellID = @WorkTimeModelID
                    AND Folgenummer = @SequenceNo;
        ELSE IF DATEPART(dw, @Date) = 6             -- Friday
            SELECT @WtmTime = AzmZeitFr FROM AzmWochen 
                WHERE ArbeitszeitmodellID = @WorkTimeModelID
                    AND Folgenummer = @SequenceNo;
        ELSE                                            -- Saturday
            SELECT @WtmTime = AzmZeitSa FROM AzmWochen 
                WHERE ArbeitszeitmodellID = @WorkTimeModelID
                    AND Folgenummer = @SequenceNo;
    END ELSE
    BEGIN
        DECLARE @NUMWEEKS INT
        SELECT @NUMWEEKS = DATEDIFF(week, CONVERT(CHAR(10), '01.01.2000', 104), @Date)
        IF DATEPART(dw, @Date) = 1 
            SET @NUMWEEKS = @NUMWEEKS - 1;
        DECLARE @WEEKNUMBER INT 
        IF @NUMWEEKS % 2 = 0
            SET @WEEKNUMBER = 1
        ELSE
            SET @WEEKNUMBER = 2;
        IF DATEPART(dw, @Date) = 1              -- Sunday
            SELECT @WtmTime = AzmZeitSo FROM AzmWochen 
                WHERE Folgenummer = @SequenceNo AND Wochennummer = @WEEKNUMBER 
                  AND ArbeitszeitmodellID = @WorkTimeModelID
        ELSE IF DATEPART(dw, @Date) = 2     -- Monday
            SELECT @WtmTime = AzmZeitMo FROM AzmWochen 
                WHERE Folgenummer = @SequenceNo AND Wochennummer = @WEEKNUMBER 
                  AND ArbeitszeitmodellID = @WorkTimeModelID
        ELSE IF DATEPART(dw, @Date) = 3     -- Tuedsay
            SELECT @WtmTime = AzmZeitDi FROM AzmWochen 
                WHERE Folgenummer = @SequenceNo AND Wochennummer = @WEEKNUMBER 
                  AND ArbeitszeitmodellID = @WorkTimeModelID
        ELSE IF DATEPART(dw, @Date) = 4     -- Wednesday
            SELECT @WtmTime = AzmZeitMi FROM AzmWochen 
                WHERE Folgenummer = @SequenceNo AND Wochennummer = @WEEKNUMBER 
                  AND ArbeitszeitmodellID = @WorkTimeModelID
        ELSE IF DATEPART(dw, @Date) = 5     -- Thursday
            SELECT @WtmTime = AzmZeitDo FROM AzmWochen 
                WHERE Folgenummer = @SequenceNo AND Wochennummer = @WEEKNUMBER 
                  AND ArbeitszeitmodellID = @WorkTimeModelID
        ELSE IF DATEPART(dw, @Date) = 6     -- Friday
            SELECT @WtmTime = AzmZeitFr FROM AzmWochen 
                WHERE Folgenummer = @SequenceNo AND Wochennummer = @WEEKNUMBER 
                  AND ArbeitszeitmodellID = @WorkTimeModelID
        ELSE                                    -- Saturday
            SELECT @WtmTime = AzmZeitSa FROM AzmWochen 
                WHERE Folgenummer = @SequenceNo AND Wochennummer = @WEEKNUMBER 
                  AND ArbeitszeitmodellID = @WorkTimeModelID
    END
    IF @Date >= '20130901' AND @WtmTime = 'ZA'
        SET @WtmTime = 'KD';

    RETURN @WtmTime;
END


CREATE FUNCTION [dbo].[IstFeiertag] (
    @Datum DATETIME,
    @IstEvangelisch BIT)
  RETURNS INT
AS
BEGIN
    DECLARE @I INT
    DECLARE @Y INT
    DECLARE @A INT
    DECLARE @B INT
    SET @I = DATEPART(year, @Datum) / 100 - DATEPART(year, @Datum) / 400 + 4;
    SET @Y = @I - DATEPART(year, @Datum) / 300 + 11;
    SET @A = (((DATEPART(year, @Datum) % 19) * 19) + @Y) % 30;
    SET @B = (((DATEPART(year, @Datum) % 4) * 2 + 4 * DATEPART(year, @Datum) + 6 * @A + @I) % 7) + @A - 9;
    DECLARE @OstTag INT
    DECLARE @OstMon INT
    IF @B < 1
    BEGIN
        SET @OstTag = 31 + @B
        SET @OstMon = 3
    END ELSE
    BEGIN
        IF ((@B = 26) OR ((@A = 28) AND (@B = 25) AND ((11 * (@Y + 1) % 30) < 19)))
        BEGIN
            SET @B = @B - 7;
        END
        SET @OstTag = @B
        SET @OstMon = 4
    END

    DECLARE @Ostersonntag DATETIME
    SET @Ostersonntag = dbo.CreateDate(DATEPART(year, @Datum), @OstMon, @OstTag)

    IF @Datum >= @Ostersonntag
    BEGIN
        DECLARE @TAGE INT
        SET @TAGE = DATEDIFF(day, @Ostersonntag, @Datum)
        IF @TAGE = 0 OR @TAGE = 1 OR @TAGE = 39 OR @TAGE = 50 OR @TAGE = 60
        BEGIN
            RETURN 1
        END
    END
    DECLARE @TEMP INT 
    SET @TEMP = DATEPART(month, @Datum) * 100 + DATEPART(day, @Datum)
    IF @TEMP = 101 OR @TEMP = 106 OR @TEMP = 501 OR @TEMP = 815 OR @TEMP = 1026
         OR @TEMP = 1101 OR @TEMP = 1208 OR @TEMP = 1225 OR @TEMP = 1226
    BEGIN
        RETURN 1
    END 
    RETURN 0
END
GO


CREATE FUNCTION [dbo].[CreateDate] (
    @Year int, 
    @Month int, 
    @Day int)
  RETURNS DATETIME
AS
BEGIN
    declare @d datetime;
    set @d = dateadd(year,(@Year - 1753),'1/1/1753');
    set @d = dateadd(month,@Month - 1,@d);
    return dateadd(day,@Day - 1,@d)
END
GO

These are the table definitions (in German):

CREATE TABLE [dbo].[Arbeitszeitmodelle]
(
    [ArbeitszeitmodellID] uniqueidentifier ROWGUIDCOL NOT NULL 
        CONSTRAINT [DF_Arbeitszeitmodelle_ArbeitszeitmodellID] DEFAULT (newid()) 
        CONSTRAINT [PK_Arbeitszeitmodelle_ArbeitszeitmodellID] PRIMARY KEY CLUSTERED,
    [Name] nvarchar(25) NOT NULL,   
    [MaxAnzahlMitglieder]  tinyint NOT NULL 
    CONSTRAINT [CK_Arbeitszeitmodelle_MaxAnzahlMitglieder] CHECK (([MaxAnzahlMitglieder] > 0) AND ([MaxAnzahlMitglieder] < 10)), 
    [AnzahlWochen] tinyint NOT NULL
    CONSTRAINT [CK_Arbeitszeitmodelle_AnzahlWochen] CHECK (([AnzahlWochen] > 0) AND ([AnzahlWochen] < 5)),
    [ÜbernimmtFeiertage] bit 
);

CREATE TABLE [dbo].[AzmWochen]
(
    [AzmWochenID] uniqueidentifier ROWGUIDCOL NOT NULL 
        CONSTRAINT [DF_AzmWochen_AzmWochenID] DEFAULT (newid()) 
        CONSTRAINT [PK_AzmWochen_AzmWochenID] PRIMARY KEY CLUSTERED,
    [Folgenummer] tinyint NOT NULL
        CONSTRAINT [CK_AzmWochen_Folgenummer] CHECK (([Folgenummer] > 0) AND ([Folgenummer] < 10)),     
        [Wochennummer] tinyint NOT NULL
        CONSTRAINT [CK_AzmWochen_Wochennummer] CHECK (([Wochennummer] > 0) AND ([Wochennummer] < 3)),
        [ArbeitszeitmodellID] uniqueidentifier NOT NULL 
        CONSTRAINT [FK_AzmWochen_ArbeitszeitmodellID] FOREIGN KEY ([ArbeitszeitmodellID]) REFERENCES [dbo].[Arbeitszeitmodelle] ([ArbeitszeitmodellID]) ON UPDATE CASCADE ON DELETE CASCADE,    
    [AzmZeitMo] varchar(5) NOT NULL,
    [AzmZeitDi] varchar(5) NOT NULL,
    [AzmZeitMi] varchar(5) NOT NULL,
    [AzmZeitDo] varchar(5) NOT NULL,
    [AzmZeitFr] varchar(5) NOT NULL,
    [AzmZeitSa] varchar(5) NOT NULL,
    [AzmZeitSo] varchar(5) NOT NULL
);
ALTER TABLE AzmWochen ADD CONSTRAINT [UQ_AzmWochen_FolgeWochen] UNIQUE ([ArbeitszeitmodellID] ASC, [Folgenummer] ASC, [Wochennummer] ASC);

I tried the hints:

OPTION(USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))
OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140'))

… but they did not prevent the error.

I inserted the two tables, test data, and the functions (GetWtmTime depends on two other scalar functions) into an empty test database and was able to execute the function twice. Then I again got the memory error.

Best Answer

Cause

SQL Server is trying to inline the function but failing due to the complexity.

Using so much memory while doing so is unexpected and almost certainly a bug.

A definition for the nested function dbo.IstFeiertag would be needed for a full repro.

Workaround

Add WITH INLINE = OFF to the function(s) definition. Once this issue is resolved, you should be able to remove that option to reap the performance benefits of function inlining.

Reporting and Status

You should report this issue to Microsoft. If you have a support agreement, go that route. Alternatively, post a bug report on User Voice, and email the Intelligent Query Processing team at intelligentqp@microsoft.com.

Joe Sack (Principal Program Manager, Microsoft SQL Server product team) commented:

Thank you for reporting. Paul White gave me a heads-up and I've reported to our team for investigation.

Resolution

A fix for this issue was released as part of Cumulative Update 2 for SQL Server 2019.

Best Answer

Cause

Workaround

Reporting and Status

Resolution

Related Solutions

Sql-server – Indexing – Uniqueidentifier Foreign Key or Intermediary mapping table

Sql-server – Oracle GoldenGate add trandata errors

Related Question