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

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.

StackExchange Database Administrators Q#253499, answer score: 11