SQL Server 的“總伺服器記憶體”消耗數月停滯不前，但可用容量超過 64GB

我遇到了一個奇怪的問題，SQL Server 2016 標準版 64 位似乎已經將自己限制在分配給它的總記憶體的一半（64GB 的 128GB）。

的輸出@@VERSION是：

Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64) 2017 年 12 月 22 日 11:25:00 版權所有 (c) Microsoft Corporation Standard Edition (64-bit) o​​n Windows Server 2012 R2 Datacenter 6.3 (內部版本 9600：）（管理程序）

的輸出sys.dm_os_process_memory是：

當我查詢時sys.dm_os_performance_counters，我看到Target Server Memory (KB)是 at131072000並且Total Server Memory (KB)是 at 的一半以下65308016。在大多數情況下，我認為這是正常行為，因為 SQL Server 尚未確定它需要為自己分配更多記憶體。

但是，它已經“卡在”~64GB 兩個多月了。在此時間範圍內，我們對一些數據庫執行了大量記憶體密集型操作，並向實例添加了近 40 個數據庫。我們共有 292 個數據庫，每個數據庫的預分配數據文件為 4GB，自動增長速度為 256MB，日誌文件為 2GB，自動增長速度為 128MB。我每晚在上午 12:00 執行一次完整備份，並從周一到週五從上午 6:00 到晚上 8:00 開始每隔 15 分鐘進行一次事務日誌備份。這些數據庫的整體吞吐量相對較低，但我懷疑有些事情是錯誤的，因為 SQL Server 還沒有爬上Target Server Memory自然地通過新的數據庫添加、正常的查詢執行以及已經執行的記憶體密集型 ETL 管道。

SQL Server 實例本身位於一個虛擬化 (VMware) Windows Server 2012R2 伺服器之上，該伺服器具有 12 個 CPU、144GB 記憶體（128GB 用於 SQL Server，16GB 為 Windows 保留）和 4 個虛擬磁碟，這些虛擬磁碟位於具有 15K SAS 驅動器的 vSAN 之上. Windows 自然位於 64GB C: 磁碟上，頁面文件為 32GB。數據文件位於 2TB D: 磁碟上，日誌文件位於 2TB L: 磁碟之上，而 tempdb 位於 256GB T: 磁碟上，其中包含 8x16GB 文件，沒有自動增長。

我已經驗證除了MSSQLSERVER.

此伺服器完全專用於 SQL Server 實例，因此我們沒有在其上執行可能會消耗記憶體的其他應用程序或服務。

我使用 RedGate SQL Monitor 進行分析，下面是過去 18 天的Total Server Memory. 如您所見，除了 4 月初單次上升約 300MB 之外，記憶體使用率一直完全停滯不前。

這可能是什麼原因？為了確定為什麼 SQL Server 不想使用分配給它的額外 64GB+ 記憶體，我可以仔細查看什麼？

執行的輸出sp_Blitz：

sp_Blitz @OutputType = 'markdown', @CheckServerInfo = 1;

優先級 50：性能：

• CPU 調度程序離線 - 由於關聯屏蔽或許可問題，SQL Server 無法訪問某些 CPU 核心。
• 記憶體節點離線 - 由於關聯屏蔽或許可問題，某些記憶體可能不可用。

優先級 50：可靠性：

• 遠端 DAC 已禁用 - 未啟用對專用管理員連接 (DAC) 的遠端訪問。當 SQL Server 無響應時，DAC 可以更輕鬆地進行遠端故障排除。

優先級 100：性能：

• 一個查詢的許多計劃 - 計劃記憶體中的單個查詢存在 300 個計劃 - 這意味著我們可能存在參數化問題。
• 啟用伺服器觸發器

+ 伺服器觸發器

$$ RG_SQLLighthouse_DDLTrigger $$已啟用。確保您了解觸發器在做什麼——它做的工作越少越好。 + 伺服器觸發器

$$ SSMSRemoteBlock $$已啟用。確保您了解觸發器在做什麼——它做的工作越少越好。

優先級 150：性能：

• 強制加入提示的查詢 - 自重啟以來已記錄了 1480 個加入提示實例。這意味著查詢支配著 SQL Server 優化器，如果他們不知道自己在做什麼，這可能弊大於利。這也可以解釋為什麼 DBA 調整工作不起作用。
• 查詢強制訂單提示 - 自重啟以來已記錄 2153 個訂單提示實例。這意味著查詢支配著 SQL Server 優化器，如果他們不知道自己在做什麼，這可能弊大於利。這也可以解釋為什麼 DBA 調整工作不起作用。

優先級 170：文件配置：

• C盤系統數據庫

+ master - master 數據庫在 C 驅動器上有一個文件。將系統數據庫放在 C 驅動器上可能會在伺服器空間不足時導致伺服器崩潰。 + 模型 - 模型數據庫在 C 驅動器上有一個文件。將系統數據庫放在 C 驅動器上可能會在伺服器空間不足時導致伺服器崩潰。 + msdb - msdb 數據庫在 C 驅動器上有一個文件。將系統數據庫放在 C 驅動器上可能會在伺服器空間不足時導致伺服器崩潰。

優先級 200：資訊：

• 代理作業同時啟動 - 多個 SQL Server 代理作業配置為同時啟動。有關詳細的時間表列表，請參閱 URL 中的查詢。
• Master 數據庫 master 中的表 - master 數據庫中的 CommandLog 表由最終使用者於 2017 年 7 月 30 日下午 5:22 創建。發生災難時，主數據庫中的表可能無法恢復。
• TraceFlag 開啟

+ 跟踪標誌 1118 全域啟用。 + 跟踪標誌 1222 全域啟用。 + 跟踪標誌 2371 已全域啟用。

優先級 200：非預設伺服器配置：

• 代理 XP - 此 sp_configure 選項已更改。它的預設值為 0，並已設置為 1。
• 備份校驗和預設值 - 此 sp_configure 選項已更改。它的預設值為 0，並已設置為 1。
• 備份壓縮預設值 - 此 sp_configure 選項已更改。它的預設值為 0，並已設置為 1。
• 並行度的成本門檻值 - 此 sp_configure 選項已更改。它的預設值為 5，已設置為 48。
• max degree of parallelism - 此 sp_configure 選項已更改。它的預設值為 0，已設置為 12。
• 最大伺服器記憶體 (MB) - 此 sp_configure 選項已更改。其預設值為 2147483647，已設置為 128000。
• 針對臨時工作負載進行優化 - 此 sp_configure 選項已更改。它的預設值為 0，並已設置為 1。
• 顯示高級選項 - 此 sp_configure 選項已更改。它的預設值為 0，並已設置為 1。
• xp_cmdshell - 此 sp_configure 選項已更改。它的預設值為 0，並已設置為 1。

優先級 200：可靠性：

• Master中的擴展儲存過程
• 掌握

$$ sqbdata $$擴展儲存過程在 master 數據庫中。CLR 可能正在使用中，並且主數據庫現在需要成為您的備份/恢復計劃的一部分。 + 掌握

$$ sqbdir $$擴展儲存過程在 master 數據庫中。CLR 可能正在使用中，並且主數據庫現在需要成為您的備份/恢復計劃的一部分。 + 掌握

$$ sqbmemory $$擴展儲存過程在 master 數據庫中。CLR 可能正在使用中，並且主數據庫現在需要成為您的備份/恢復計劃的一部分。 + 掌握

$$ sqbstatus $$擴展儲存過程在 master 數據庫中。CLR 可能正在使用中，並且主數據庫現在需要成為您的備份/恢復計劃的一部分。 + 掌握

$$ sqbtest $$擴展儲存過程在 master 數據庫中。CLR 可能正在使用中，並且主數據庫現在需要成為您的備份/恢復計劃的一部分。 + 掌握

$$ sqbtestcancel $$擴展儲存過程在 master 數據庫中。CLR 可能正在使用中，並且主數據庫現在需要成為您的備份/恢復計劃的一部分。 + 掌握

$$ sqbteststatus $$擴展儲存過程在 master 數據庫中。CLR 可能正在使用中，並且主數據庫現在需要成為您的備份/恢復計劃的一部分。 + 掌握

$$ sqbutility $$擴展儲存過程在 master 數據庫中。CLR 可能正在使用中，並且主數據庫現在需要成為您的備份/恢復計劃的一部分。 + 掌握

$$ sqlbackup $$擴展儲存過程在 master 數據庫中。CLR 可能正在使用中，並且主數據庫現在需要成為您的備份/恢復計劃的一部分。

優先級 210：非預設數據庫配置：

• Read Committed Snapshot Isolation Enabled - 此數據庫設置不是預設設置。

+ 紅門 + 紅門監視器

• 啟用快照隔離 - 此數據庫設置不是預設設置。

+ 紅門 + 紅門監視器

優先級 240：等待統計：

• 1 - SOS_SCHEDULER_YIELD - 1770.8 小時等待，115.9 分鐘平均每小時等待時間，100.0% 信號等待，1419212079 個等待任務，4.5 毫秒平均等待時間。

優先級 250：資訊：

• SQL Server 在 NT 服務帳戶下執行 - 我作為 NT Service\MSSQLSERVER 執行。我希望我有一個 Active Directory 服務帳戶。

優先級 250：伺服器資訊：

• 預設跟踪內容 - 預設跟踪包含 2018 年 4 月 14 日晚上 11:21 到 2018 年 4 月 16 日上午 11:13 之間的 36 小時數據。預設跟踪文件位於：C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log
• 驅動器 C 空間 - C 驅動器上有 196816.00MB 可用空間
• 驅動器 D 空間 - E 驅動器上有 894823.00MB 可用空間
• 驅動器 L 空間 - F 驅動器上有 1361367.00MB 可用空間
• 驅動器 T 空間 - G 驅動器上有 114441.00MB 可用空間
• 硬體 - 邏輯處理器：12 個。物理記憶體：144GB。
• 硬體 - NUMA 配置

+ 節點：0 狀態：ONLINE 線上調度程序：4 離線調度程序：2 處理器組：0 記憶體節點：0 記憶體 VAS 保留 GB：186 + 節點：1 狀態：OFFLINE 線上調度程序：0 離線調度程序：6 處理器組：0 記憶體節點：0 記憶體 VAS 保留 GB：186

• 已啟用即時文件初始化 - 服務帳戶具有執行卷維護任務權限。
• 電源計劃 - 你的伺服器有 2.60GHz 的 CPU，並且處於平衡電源模式——呃……你希望你的 CPU 全速執行，對嗎？
• 伺服器上次重啟 - 2018 年 3 月 9 日上午 7:27
• 伺服器名稱 -

$$ redacted $$

• 服務

+ 服務：SQL Server (MSSQLSERVER) 在服務帳戶 NT Service\MSSQLSERVER 下執行。上次啟動時間：2018 年 3 月 9 日上午 7:27。啟動類型：自動，目前正在執行。 + 服務：SQL Server 代理 (MSSQLSERVER) 在服務帳戶 LocalSystem 下執行。上次啟動時間：未顯示。啟動類型：自動，目前正在執行。

• SQL Server 上次重新啟動 - 2018 年 3 月 9 日上午 6:27
• SQL Server 服務 - 版本：13.0.4466.4。更新檔級別：SP1。累積更新：CU7。版本：標準版（64 位）。啟用的可用性組：0。可用性組管理器狀態：2
• 虛擬伺服器 - 類型：（HYPERVISOR）
• Windows 版本 - 您正在執行一個非常現代的 Windows 版本：Server 2012R2 時代，版本 6.3

優先級 254：執行日期：

• 船長的日誌：給某事和某事約會……

我敢打賭，您已經以某些 CPU 節點和/或記憶體節點處於離線狀態的方式配置了虛擬 CPU。

下載sp_Blitz（免責聲明：我是該免費開源腳本的作者之一）並執行它：

sp_Blitz @CheckServerInfo = 1;

查找有關 CPU 和/或記憶體節點離線的警告。SQL Server 標準版只看到前 4 個 CPU 插槽，您可能已將 VM 配置為 6 個雙核 CPU。它最終會遇到一個類似於Enterprise Edition 的 20-core-limits 如何限制您可以看到的記憶體量的問題。

如果您想在此處分享 sp_Blitz 的輸出，您可以像這樣執行它以輸出到 Markdown，然後您可以將其複制/粘貼到您的問題中：

sp_Blitz @OutputType = 'markdown', @CheckServerInfo = 1;

**更新 2018/04/16 - 確認。**您附加了 sp_Blitz 輸出（謝謝！），它確實表明您的 CPU 和記憶體節點處於離線狀態。建構 VM 的人將其配置為 12 個單核 CPU，因此 SQL Server 標準版只看到前 4 個插槽（核心）以及連接到它們的記憶體。

要修復它，請關閉 VM，將其配置為 2 插槽、6 核 VM，然後 SQL Server 標準版將看到所有核心和記憶體。這也將減少您的 SOS_SCHEDULER_YIELD 等待 - 現在，您的 SQL Server 正在敲擊前 4 個核心，但僅此而已。在此修復之後，它將能夠在所有 12 個核心上工作。

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