升級到 SQL Server 2019 後，函式拋出“記憶體不足”錯誤

我已將 SQL Server 2012 數據庫（大小為 8GB）移動到具有相同記憶體和 CPU 配置的新設置的 SQL Server 2019 虛擬機，並將兼容性級別更改為 SQL Server 2019。

我的應用程序中的所有東西都執行良好，除了一個儲存過程，它由一個帶有兩個參數的大 SQL 查詢組成（並且沒有花哨的選項）。當這個 SP 執行時，它讓 SQL Server 程序的記憶體上升到指定的最大級別，然後返回錯誤：

“記憶體不足，無法執行此查詢”

當我在 SSMS 的單獨查詢視窗中執行 SQL 查詢（在儲存過程中）時，它會立即執行並返回預期的 300 行。另外，當我將數據庫的兼容級別更改為“SQL Server 2017”並執行儲存過程時，一切正常。

我首先認為這可能是參數嗅探問題，但沒有任何解決方法有幫助（例如OPTION (RECOMPILE)）。

我已將問題深入到標量值函式的呼叫。每次呼叫這個函式，都會出現記憶體錯誤。

這是函式的 DDL（對不起，部分是德語）：

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

這些是表定義（德語）：

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);

我嘗試了提示：

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

…但他們並沒有阻止錯誤。

我將兩個表、測試數據和函式（GetWtmTime取決於另外兩個標量函式）插入到一個空的測試數據庫中，並且能夠執行該函式兩次。然後我又得到了記憶體錯誤。

原因

SQL Server 正在嘗試內聯該函式，但由於復雜性而失敗。

這樣做時使用這麼多記憶體是出乎意料的，幾乎可以肯定是一個錯誤。

dbo.IstFeiertag完整的複制需要嵌套函式的定義。

解決方法

添加WITH INLINE = OFF到函式定義。解決此問題後，您應該能夠刪除該選項以獲得函式內聯的性能優勢。

報告和狀態

您應該將此問題報告給 Microsoft。如果您有支持協議，請走那條路。或者，在User Voice上發布錯誤報告，並通過 smartqp@microsoft.com 向智能查詢處理團隊發送電子郵件。

Joe Sack（Microsoft SQL Server 產品團隊首席項目經理）評論道：

感謝您的報告。保羅懷特給了我一個提示，我已經向我們的團隊報告了調查。

---

解析度

此問題的修復程序已作為SQL Server 2019 累積更新 2 的一部分發布。

引用自：https://dba.stackexchange.com/questions/253499