After upgrading to SQL Server 2019, function throws "insufficient memory" error

Question

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.

Answer 1

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 [email protected].

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.