SQL Server 2017；記憶體故障排除；資源池“內部”中的系統記憶體不足，無法執行此查詢

我們正在執行本地 SQL Server 2017 來支持數據倉庫數據庫。數據庫通過 SSIS 按計劃載入，主要是通過使用臨時表和 MERGE 功能。最近，我們開始看到錯誤“資源池’內部’中的系統記憶體不足，無法執行此查詢。”。在過去的幾周里，它變得越來越猖獗。

我們嘗試過：

1. 關閉查詢儲存
2. 通過 CU27 更新 SQL Server
3. 跟踪查詢（沒有好的結果）
4. 執行盡可能多的報告以查明問題

與錯誤發生時間相關的最新更改：

1. 打開查詢儲存
2. 添加大量索引

其他資訊：

1. 我們在伺服器上有 32gb 的記憶體並分配 26gb 給 sql server
2. MERGE 的 TARGET 表是一個 CLUSTERED COLUMNSTORE INDEX
3. MERGE 的 SOURCE 表是一個 HEAP
4. 隨著時間/多次失敗，已暫存更改記錄的 SOURCE 表已增長到超過 200,000 條記錄。TARGET 表大約有 1000 萬行。

任何幫助，將不勝感激。我在過去的幾天裡搜尋了網際網路，尋找任何指導。到目前為止，我所看到的是：

1. 更新 SQL 版本
2. 修改您的查詢
3. 向伺服器添加記憶體

合併聲明：

DROP TABLE IF EXISTS #Changes;
DROP TABLE IF EXISTS #TransformedChanges;

CREATE TABLE #Changes
(
   [Change Type] VARCHAR(100)
);
MERGE [dbo].[FactOrderLine] AS TARGET
USING ( SELECT
[FactOrderLine].[OrderLine_Key],
[FactOrderLine].[BookedDate_Date_Key],
[FactOrderLine].[BookedDate_Time_Key],
[FactOrderLine].[Account_Key],
[FactOrderLine].[CCN_Key],
[FactOrderLine].[BillTo_SalesOffice_Key],
[FactOrderLine].[BillTo_Territory_Key],
[FactOrderLine].[ShipTo_SalesOffice_Key],
[FactOrderLine].[ShipTo_Territory_Key],
[FactOrderLine].[AssemblyLocation_Key],
[FactOrderLine].[ProductDivision_Key],
[FactOrderLine].[Product_Key],
[FactOrderLine].[Booked Date],
[FactOrderLine].[Ordered Quantity],
[FactOrderLine].[Unit Price - CCN],
[FactOrderLine].[Unit Price - Transaction],
[FactOrderLine].[Discount Factor],
[FactOrderLine].[Split Factor],
[FactOrderLine].[Is Split?],
[DW_Checksum] = CHECKSUM([FactOrderLine].[BookedDate_Date_Key],
[FactOrderLine].[BookedDate_Time_Key],
[FactOrderLine].[Account_Key],
[FactOrderLine].[CCN_Key],
[FactOrderLine].[BillTo_SalesOffice_Key],
[FactOrderLine].[BillTo_Territory_Key],
[FactOrderLine].[ShipTo_SalesOffice_Key],
[FactOrderLine].[ShipTo_Territory_Key],
[FactOrderLine].[AssemblyLocation_Key],
[FactOrderLine].[ProductDivision_Key],
[FactOrderLine].[Product_Key],
[FactOrderLine].[Booked Date],
[FactOrderLine].[Ordered Quantity],
[FactOrderLine].[Unit Price - CCN],
[FactOrderLine].[Unit Price - Transaction],
[FactOrderLine].[Discount Factor],
[FactOrderLine].[Split Factor],
[FactOrderLine].[Is Split?],
0)
FROM [changeLog].[FactOrderLine] ) AS SOURCE
ON [Source].[OrderLine_Key] = [Target].[OrderLine_Key]
WHEN MATCHED AND ISNULL([Source].[DW_Checksum], 0) <> ISNULL([Target].[DW_Checksum], 0) THEN UPDATE SET
[Target].[BookedDate_Date_Key] = [Source].[BookedDate_Date_Key],
[Target].[BookedDate_Time_Key] = [Source].[BookedDate_Time_Key],
[Target].[Account_Key] = [Source].[Account_Key],
[Target].[CCN_Key] = [Source].[CCN_Key],
[Target].[BillTo_SalesOffice_Key] = [Source].[BillTo_SalesOffice_Key],
[Target].[BillTo_Territory_Key] = [Source].[BillTo_Territory_Key],
[Target].[ShipTo_SalesOffice_Key] = [Source].[ShipTo_SalesOffice_Key],
[Target].[ShipTo_Territory_Key] = [Source].[ShipTo_Territory_Key],
[Target].[AssemblyLocation_Key] = [Source].[AssemblyLocation_Key],
[Target].[ProductDivision_Key] = [Source].[ProductDivision_Key],
[Target].[Product_Key] = [Source].[Product_Key],
[Target].[Booked Date] = [Source].[Booked Date],
[Target].[Ordered Quantity] = [Source].[Ordered Quantity],
[Target].[Unit Price - CCN] = [Source].[Unit Price - CCN],
[Target].[Unit Price - Transaction] = [Source].[Unit Price - Transaction],
[Target].[Discount Factor] = [Source].[Discount Factor],
[Target].[Split Factor] = [Source].[Split Factor],
[Target].[Is Split?] = [Source].[Is Split?],
[Target].[DW_Checksum] = [Source].[DW_Checksum],
[Target].[DW_ModifiedOn] = GETUTCDATE(),
[Target].[DW_IsDeleted?] = 0
WHEN NOT MATCHED BY TARGET THEN INSERT
(
[OrderLine_Key],
[BookedDate_Date_Key],
[BookedDate_Time_Key],
[Account_Key],
[CCN_Key],
[BillTo_SalesOffice_Key],
[BillTo_Territory_Key],
[ShipTo_SalesOffice_Key],
[ShipTo_Territory_Key],
[AssemblyLocation_Key],
[ProductDivision_Key],
[Product_Key],
[Booked Date],
[Ordered Quantity],
[Unit Price - CCN],
[Unit Price - Transaction],
[Discount Factor],
[Split Factor],
[Is Split?],
[DW_IsDeleted?], [DW_Checksum], [Source_ModifiedOn], [DW_ModifiedOn], [DW_CreatedOn] ) VALUES (
[Source].[OrderLine_Key],
[Source].[BookedDate_Date_Key],
[Source].[BookedDate_Time_Key],
[Source].[Account_Key],
[Source].[CCN_Key],
[Source].[BillTo_SalesOffice_Key],
[Source].[BillTo_Territory_Key],
[Source].[ShipTo_SalesOffice_Key],
[Source].[ShipTo_Territory_Key],
[Source].[AssemblyLocation_Key],
[Source].[ProductDivision_Key],
[Source].[Product_Key],
[Source].[Booked Date],
[Source].[Ordered Quantity],
[Source].[Unit Price - CCN],
[Source].[Unit Price - Transaction],
[Source].[Discount Factor],
[Source].[Split Factor],
[Source].[Is Split?],
0,
[Source].[DW_Checksum],NULL,GETUTCDATE(),GETUTCDATE()
)
OUTPUT $action INTO #Changes;
CREATE TABLE #TransformedChanges
   (
       [Update Record Count] INT,
       [Insert Record Count] INT
   );
INSERT INTO #TransformedChanges
SELECT *
FROM
   (
       SELECT 
           TRIM(#Changes.[Change Type])+' Record Count' AS [Change Type],
           COUNT(*) AS [Record Count]
       FROM #Changes
       GROUP BY TRIM(#Changes.[Change Type])+' Record Count'
   ) A
PIVOT
   (
       SUM([Record Count])
       FOR A.[Change Type] IN ([Update Record Count], [Insert Record Count])
   ) [B];
DECLARE @TransformationChecker INT;
SET @TransformationChecker = (SELECT COUNT(*) FROM #TransformedChanges);
IF @TransformationChecker = 0 INSERT INTO #TransformedChanges VALUES(0,0);
SELECT ISNULL([Update Record Count], 0) [Update Record Count], ISNULL([Insert Record Count], 0) [Insert Record Count] FROM #TransformedChanges;

DROP TABLE IF EXISTS #Changes;
DROP TABLE IF EXISTS #TransformedChanges;

感謝多位評論者的幫助，我發現我的問題已通過刪除 MERGE 語句的使用得到解決，而是執行獨立的 INSERT/UPDATE/DELETE 語句。這是由多個部落格、與我一起工作的 DBA 和這裡的評論者推薦的，但由於沒有可用的文件表明 MERGE 和記憶體問題可能相關（以及我自己的固執），我沒有嘗試切換它們，直到嘗試了多個其他路線。

我仍然很好奇究竟是什麼導致了記憶體問題，而表面上你會期望相同數量的更新、插入和刪除需要相同（或相似）數量的資源。顯然，MERGE 語句不是以這種方式執行的，有時您必須對解決方案進行實際操作。

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