SQL 最大伺服器記憶體設定值研究
1 | 25,227 |
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 設定。
文章冑並提供一個判斷記憶體是不是給太多的方法 – 觀察以下三個效能計數器:
- SQL Server:Buffer Manager\Page Life Expectancy
- SQL Server:Buffer Manager\Page Reads/Sec
- 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 ),應是不錯的參考值。
Comments
# by Died
試過把最大記憶體設成1....... 當下發現記憶體不足connection開不了,連要把設定調回來都沒辦法,還好後來研究出可以用cli 去改...