SQL 最大伺服器記憶體設定值研究

SQL Server 預設會用光主機所有記憶體(預設上限為 2PB = 2048TB)，除非整個資料庫容量小於總記憶體，否則把記憶體當成 Cache 能提升效能、減少磁碟 I/O 耗損，絕對利多於弊。如果同主機有多個資料庫執行個體，或是要與其他應用程式、服務分享記憶體，就必須透過 Max_Server_Memory 設定伺服器可用記憶體上限，保留一部分供其他程式使用。

最近被問到一個問題，針對專屬資料庫用途的 SQL Server 主機，剩餘記憶體的監控警戒值該怎麼抓？若依通用慣例抓 90%，主機應該 7x24x365 都處於紅色警戒，警告信件簡訊滿天飛，明顯不可行。那麼：針對專屬 SQL 主機，Max Server Memory 該設多少? 記憶體警戒水位該怎麼抓？

關於 SQL 最大伺服器記憶體(Max Server Memory)的估算，我查到 SQL MVP Jonathan Kehayias 一篇常被普遍的指南 - How much memory does my SQL Server actually need- - Jonathan Kehayias。

SQL 最大伺服器記憶體基本規則：

總記憶體 4-16GB 時，保留 1GB 給 OS，剩下全撥給 SQL Server。總記憶體超過 16GB 時，每增加 8GB 再多留 1GB 給 OS。

要估許是否能撥更多記憶體給 SQL 可觀察 Memory\Available Mbytes 效能指標，目標是可用記憶體最起碼維持 150-300MB
即可。( 低於96MB Windows 會發警報 LowMemoryResourceNotification，>256GB 的主機建議保留可用 1GB 以上較保險 )
另一版更詳細的計算公式為：

總記憶體 – 執行緒堆疊用量[最大工作執行緒數目 * 堆疊大小(x86 512K、x64 2MB)] – OS保留量(1-4GB) – 其他應用程式保留量 – SQLCLR/Linked Server 等使用量

而官方文件也提到類似估算方法:

• 從 OS 總記憶體中保留 1GB - 4GB 的記憶體給 OS 本身。
• 減去等於不受 [最大伺服器記憶體] 控制的潛在 SQL Server 記憶體配置，該配置的算法為堆疊大小 1 * 計算得出的最大背景工作執行緒數 2 + -g 啟動參數 3 (若未設定 -g，則預設為 256MB)。 餘數即為單一執行個體安裝的 max_server_memory 設定。

文章冑並提供一個判斷記憶體是不是給太多的方法 – 觀察以下三個效能計數器：

1. SQL Server:Buffer Manager\Page Life Expectancy
2. SQL Server:Buffer Manager\Page Reads/Sec
3. Physical Disk\Disk Reads/sec

若 Page Life Expectancy 持續上升永不下降而 Page Reads/Sec 及 Disk Reads/Sec 偏低，象徵記憶體過剩，可試著減少 Max Server Memory 再觀察指標變化，設法讓 Page Life Expectancy 落入合理值。(依 Jonathan 的建議，PLE合理值 = 資料Cache GB數 / 4GB * 300，每 300 秒平均淘換掉 4GB 的 Cache 內容，時間過短將導致讀寫 IO 偏高)

除此之外，專屬 SQL Server 有時也會拿來跑 SSIS、SSAS，這部分不算在 SQL Server 的記憶體用量，要記得歸類為其他應用程式計算。

最後，回到記憶體警戒水位如何決定。只跑資料庫引擎的 SQL 主機，若無意外會將撥給它用的記憶體吃好吃滿，只留 1GB 給 Windows， 故系統剩餘可用記憶體用量會依 Windows 及其他程式用剩多少而訂，改抓 Memory: Available MBs 指標會比百分比更容易性，依 Janathan 建議可抓 >300MB 以求保險( 256GB 以上的主機抓 1GB )，應是不錯的參考值。