由於記憶體壓力，AppDomain 2 SSISDB 被標記為解除安裝

August 17, 2021

我有一個儲存過程，它將三個大表連接在一起（每個大約 2000 萬條記錄）並將記錄載入到一個臨時表中。然後將臨時表中的數據合併到一個包含大約 6000 萬條記錄的現有表中。
伺服器離線並顯示以下錯誤消息：
應用域 2 (SSISDB.dbo.
$$ runtime $$.1] 由於記憶體壓力而被標記為解除安裝。
讓伺服器重新聯機後，我重新啟動 SQL 服務以清除任何可能一直存在的程序。再次開始工作，並且沒有問題地完成。
我正在執行具有 128GB RAM 的 SQL Server 2019。64 位虛擬伺服器上的最大伺服器記憶體為 117964MB。有人在任務管理器中告訴我記憶體使用率為 94%，這可能是問題所在。但是 SQL 不會佔用所有可用記憶體並保留它嗎？所以看起來它正在按預期執行。
sp_WhoIsActive揭示了一些狀態為“暫停”和“等待命令”的查詢，但我認為這些不會產生太大影響。128GB 的記憶體似乎足夠了，但我想這與它被要求做的工作有關。知道如何排除故障或防止再次發生嗎？
伺服器的數據驅動器約為 1.6TB。連接中的兩個較大的數據庫是 10GB 的 1900 萬行和 13GB 的 2000 萬行。這些進入一個臨時表，然後MERGE進入一個有 5300 萬行的 26GB 表。
請求的記憶體授權為 45GB，實際為 30GB。這項工作是在正常工作時間之外進行的，所以應該沒有競爭性查詢，但我不能 100% 確認有人沒有工作到很晚。
我確實注意到它也必須這樣做CONVERT_IMPLICIT。這對所需的記憶體有重大影響嗎？
查詢計劃連結： https ://www.brentozar.com/pastetheplan/?id=SyXaty7xK

關於您的伺服器
儘管授予了 29 GB 記憶體，但此查詢在兩個地方嚴重溢出。這是關於最大伺服器記憶體設置為 115 GB 的系統上查詢可以要求的高端內容。
授予該大小的記憶體的原因是優化器估計它將需要對 46 GB 的數據進行排序：
您可以通過查看資源調控器來查看記憶體授予百分比的詳細資訊——單個查詢可以請求最大伺服器記憶體設置的 25% 左右——並且最多三個查詢可以同時請求完全授予。
如果您獲得了該大小的授權，並且仍然看到如此數量的溢出和時間消耗，這可能意味著您的伺服器在記憶體方面遠遠低於配置。
由於兩個運算符都在批處理模式下執行，因此您看到的時間是per operator。
操作員時間
我敢肯定，如果您查看此伺服器的等待統計資訊（考慮到 1.6 TB 的數據），PAGEIOLATCH_可能會出現這種情況，SLEEP_TASK或者IO_COMPLETION這種大小的溢出很常見。雖然這兩種等待也可以與其他事情相關聯，但我經常看到它們在像你這樣的情況下堆積起來。
我首先建議為您的伺服器添加更實際的記憶體量。我不知道這個數字是多少（我也不能在這裡告訴你），但我可能會根據數據大小而不是其他目標來瞄準 512 GB 或更高。如果還沒有，您還應該打開記憶體中的鎖定頁面。
關於您的查詢
我看不到全文，因為查詢計劃中出現的只是插入和選擇列表的一部分，但鑑於看起來我可以對整體程式碼質量做出一些合理的假設。
例如，有幾個連接在表達式上，這可能表明您正在將連接列包裝在類似rtrim或之類的函式中isnull。
但是您的主要問題是在查詢的末尾和上圖所示的溢出。
讓我們談談那些！
雜湊連接
雜湊聯接用於DISTINCT您在143 列查詢中拋出的問題。這讓我毛骨悚然，寫這篇文章的人如果要繼續使用 SQL Server，應該強烈考慮接受一些培訓。
我建議找到產生唯一行的列的較小組合，並使用row_number來標記它們，就像我在這個影片中展示的那樣：
非常糟糕
一個虛擬碼範例如下所示：
WITH 
  cte AS
(
  SELECT
      *,
       n = 
           ROW_NUMBER..
   FROM ...
)
SELECT
   c.*
FROM cte AS c
WHERE c.n = 1;
只需確保您的索引適合支持視窗功能。
視窗函式的常見查詢計劃模式：行與批處理模式
視窗函式的常見查詢計劃模式：行儲存與列儲存索引
當索引排序方向很重要時
種類
排序在那裡，因為您的#temp表上有一個聚集索引。
如果您將表創建為堆，並稍後添加索引，則可以避免查詢執行時的不愉快，但根據各種本地因素，添加索引可能並不有趣。
什麼時候應該索引臨時表？
如果可能的話，我還建議在您的插入tablock中添加一個提示以鼓勵完全並行插入。
充分利用臨時表第 1 部分：並行插入
現在，您的查詢在插入之前是單執行緒的，這肯定會損害此行數的性能。
鑑於查詢的整體狀態，可能值得探索重寫以將查詢分解為更小的部分，以辨識您感興趣的唯一鍵集，然後獲取您感興趣的完整列集：
資訊與關係