為什麼我的 SQL Server 應該有很多記憶體錯誤？

我有一個執行 SQL Server 2019 標準版的生產系統。它最近在 1 天內出現了 3 次問題，在重新啟動之前它變得無響應。錯誤似乎指向記憶體限制。安裝了 32GB，機器專用於 MSSQL。最大記憶體設置為 26GB。

（於 2020 年 11 月 23 日編輯以刪除一些錯誤資訊：MEMORYCLERK_SQLGENERAL。）

日誌中最該死的資訊是一系列 FAIL_PAGE_ALLOCATION 錯誤。

注意：由於遇到問題，我已經增加了更大的實例大小（64GB）

2020-11-22 更新：問題在新的更大實例上再次出現。在計劃備份多個數據庫後幾分鐘，記憶體錯誤開始出現。這可能是巧合，也可能不是巧合。它變得沒有響應，我強行重新啟動它。

• 我將按照 Josh 和 sqL_handLe 的建議啟動 MSFT 支持流程。
• 根據 sqL_handLe 的查詢，我創建了一個 Agent 作業（此處為 sproc），每 5 分鐘從 dm_os_memory_objects 收集一次結果。
• 我在這里分享了最近的日誌。（我用實際的換行符替換了 nl 標籤以使其更易於閱讀。）
• 按照 Jana Sattinathan 的建議禁用 SSAS。我確實確認它正在執行並且有 13GB 的“最小工作集”，如鍊接文章中所示，儘管它的實際工作集很小。我認為這個微妙的問題可能是罪魁禍首，但如果它看起來很危險，我很驚訝我以前沒有遇到過它。Jana，考慮將您的評論轉換為答案嗎？

此伺服器上的非 SQL 負載幾乎為零。備份代理只是 Ola Hallengren 的 TSQL 腳本。預設 SQL 服務正在執行但未使用。如前所述，我已經停止了 SSAS。我們偶爾會使用 SSIS，但自從實例升級後就不再使用了。

辨識與 MEMORYCLERK_SQLGENERAL 關聯的大記憶體對象可能會使 11 月 18 日的這種情況更加清晰。

-- memory objects in MEMORYCLERK_SQLGENERAL
SELECT omo.type, omo.pages_in_bytes, omo.partition_type_desc, omo.memory_node_id
FROM sys.dm_os_memory_objects omo
JOIN sys.dm_os_memory_clerks omc 
ON omo.page_allocator_address = omc.page_allocator_address
WHERE omc.type = 'MEMORYCLERK_SQLGENERAL'
ORDER BY pages_in_bytes DESC;

最近我看到一個案例，由於磁碟空間不足，可用性組副本的自動播種在遠端端產生錯誤。在這種情況下，記憶體對象 MEMOBJ_DBCOPY 隨著時間的推移變得非常大（MEMOBJ_DBCOPY 在 MEMORYCLERK_SQLGENERAL 中）。

編輯此添加 ~~ 顯然，如果 MEMORYCLERK_SQLGENERAL 繼續增長，它將消耗可用於其他活動的記憶體，並且性能會隨著時間的推移而下降。在某些時候，OOM 會發生——例如，池中可能沒有足夠的記憶體，SQL Server 將通過錯誤通知這一點。

還有一個門檻/行為需要注意。當 SQL Server 啟動後“總伺服器記憶體”不再處於初始增長，並且數據庫記憶體在任意一個 SQLOS 記憶體節點上下降到“目標伺服器記憶體”的 2%（在這種情況下看起來只有一個記憶體節點） ，大多數（如果不是全部）使用者工作執行緒可能會嘗試糾正這種情況，並且大量 CPU 將用於執行“空閒列表停頓”。

Microsoft 在這篇 kb 文章中簡要提到了這一點（儘管 kb 文章沒有闡明在這種情況下可能導致 MEMORYCLERK_SQLGENERAL 變得如此之大的原因）。

KB4536005 - 改進：修復不正確的記憶體頁面記帳導致 SQL Server 中的記憶體不足錯誤 https://support.microsoft.com/en-us/help/4536005/kb4536005-improvement-fix-incorrect-memory-page-會計原因

在 SQL Server 記憶體出現內部問題的某些情況下，使用 DBCC DROPCLEANBUFFERS、或 DBCC FREEPROCCACHE 或 DBCC FREESYSTEMCACHE (‘ALL’) 可以暫時挽救實例。不幸的是，這裡不是這樣。必須實際辨識所涉及的記憶體對象才能使 MEMORYCLERK_SQLGENERAL 重新受到控制。

編輯討論 11 月 22 日 SQL Server 日誌… ~~

在 11 月 22 日的 memorystatus 輸出中，memoryclerk_sqlgeneral 似乎沒有立即引起關注：在出現許多錯誤之後，memoryclerk_sqlgeneral 的大小似乎等於或大致相同，為 22040 kb。其他非常有趣的細節：

$$ system physical memory high $$為 1（暗示 SQL Server 記憶體可能會增長）並且$$ current committed $$小於$$ target committed $$（也暗示 SQL Server 記憶體應該能夠增長）。 完整的 SQL Server 日誌顯示實例中 RAM 的增加。

11/18/2020 10:15:11,Server,Unknown,Detected 32409 MB of RAM. This is an informational message; no user action is required.

11/18/2020 15:26:01,Server,Unknown,Detected 64723 MB of RAM. This is an informational message; no user action is required.

我們還看到了對

$$ Max Server Memory $$.

11/18/2020 10:33:03,spid96,Unknown,Configuration option 'max server memory (MB)' changed from 300000 to 280000. Run the RECONFIGURE statement to install.

11/19/2020 11:34:10,spid94,Unknown,Configuration option 'max server memory (MB)' changed from 280000 to 60000. Run the RECONFIGURE statement to install.

300000 mb 和 280000 mb 都太大，SQL Server 無法在具有 32409 mb 記憶體的 VM 上獲得。所以 SQL Server 選擇了較低的值

$$ target server memory $$在它可以達到的 VM 上，並根據這些值管理記憶體。 可能不太直覺的是在新 VM 上，由於目前未知的原因，SQL Server 也選擇了比新 VM 更低的目標

$$ Max Server Memory $$價值 60000 mb。這可以在如下的記憶體狀態部分中看到。

11/22/2020 09:06:59,spid69,Unknown,Memory Manager               KB                             
---------------------------------------- ----------
VM Reserved                               101157820
VM Committed                                 636280
Locked Pages Allocated                     51513180
Large Pages Allocated                             0
Emergency Memory                               1024
Emergency Memory In Use                          16
Target Committed                           58468304
Current Committed                          52149464
Pages Allocated                            50371824
Pages Reserved                               273864
Pages Free                                    35352
Pages In Use                                7855040
Page Alloc Potential                       50512960
NUMA Growth Phase                                 0
Last OOM Factor                                   3
Last OS Error                                     0

這

$$ Target Committed $$這裡的值 58468304 kb 對應於 perfmon 中出現的值$$ Target Server Memory $$. 它大約為 57098 mb。許多錯誤都有這個值。我在那裡看到的另一個價值$$ Target Committed $$是 57882440 kb，或大約 56526 mb。 如果此 VM 上的非 SQL Server 工作負載符合預期（例如，如果有備份代理，其他 SQL Server 相關的東西，如 SSIS、SSAS、SSRS 和任何其他應用程序，包括 SSMS 或 ADS），我建議設置

$$ Max Server Memory $$此實例不高於 56500 mb。這可能會導致 SQL Server 更好的記憶體管理。 但是，還有一個問題：如果 memorystatus 結果顯示

$$ system physical memory high $$為什麼 SQL Server 沒有增長$$ Current Committed $$更接近$$ Target Committed $$? 特別是而不是觸發 OOM 類型錯誤。我還沒有答案。 ~~啊哈！之前錯過了這個。從錯誤日誌中解析 MEMORYSTATUS 結果可能很棘手。錯誤日誌的最新行位於頂部。但是……每個錯誤日誌行中的記憶體狀態結果不應該像錯誤日誌行那樣反轉。首先從錯誤日誌中反轉整行，然後用換行符替換。

正確的順序是：clerk、VM Reserved、VM Committed、Locked Pages Allocated、SM Reserved、SM Committed、Pages Allocated。

11/22/2020 09:06:59,spid69,Unknown,MEMORYCLERK_SQLBUFFERPOOL (node 0)               KB
---------------------------------------- ----------
VM Reserved                                25686200
VM Committed                                 141312
Locked Pages Allocated                      1072140
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                            44537424
11/22/2020 09:06:59,spid69,Unknown,MEMORYCLERK_SQLGENERAL (node 0)                  KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                               22040

確認此順序的最簡單方法是對實例使用 sqlcmd。這應該以正確的順序提供每個職員的詳細資訊。sqlcmd -Q “dbcc 記憶體狀態”

因此，在 Google Drive 上的日誌中一直滾動到 11 月 18 日，我們發現：

11/18/2020 15:10:48,spid51,Unknown,MEMORYCLERK_SQLBUFFERPOOL (node 0)               KB
---------------------------------------- ----------
VM Reserved                                12867644
VM Committed                                  75776
Locked Pages Allocated                       546740
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                            22821672
11/18/2020 15:10:48,spid51,Unknown,MEMORYCLERK_SQLGENERAL (node 0)                  KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                               22416

在 11 月 18 日的事件中，不是 MEMORYCLERK_SQLGENERAL 的大小超過 21.75 GB。那是 MEMORYCLERK_SQLBUFFERPOOL，即數據庫記憶體。在 11 月 18 日的 OOM 期間，MEMORYCLERK_SQLGENERAL 的大小要合理得多，為 22416 kb。

這裡有一些好消息：雖然這仍然需要微軟的參與來解決（因為當有發展空間時，SQL Server 真的不應該拋出 OOM

$$ Max Server Memory $$)，如果問題仍然存在，這是清除記憶體和刪除乾淨緩衝區可以防止需要重新啟動服務或必須重新啟動 VM 的條件類型。我在 SQL Server 2017 CU18 上看到了類似的東西——實際上非常相似。每天一次，我們在與 Microsoft 合作時清除記憶體並刪除乾淨的緩衝區，以試圖了解發生了什麼。這並不理想，但比崩潰要好。不幸的是，在我們診斷出真正的問題之前，問題就神奇地消失了。 這對於創可貼來說是相當激烈的，但如果系統變得絕望……

CHECKPOINT
DBCC DROPCLEANBUFFERS -- drop all clean buffers from database cache
DBCC FREEPROCCACHE -- clear plan/procedure cache 
DBCC FREESYSTEMCACHE('ALL') -- clear any other system caches

除了 FREESYSTEMCACHE(‘ALL’) 之外，它可能對 FREEPROCCACHE 是多餘的 - 我從未檢查過是否是這種情況，或者 FREEPROCCACHE 在這種情況下是否有其自身的一些好處。沒有必要再做任何事情，因為必須重新編譯計劃並且它會從數據庫記憶體中踢出很多 lof 東西。但是，如果您在等待解決病情時變得絕望……

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