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.