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 ),應是不錯的參考值。

歡迎推文分享:
Published 12 March 2018 10:18 PM 由 Jeffrey
Filed under:
Views: 5,527



意見

# Died said on 14 March, 2018 04:12 AM

試過把最大記憶體設成1.......

當下發現記憶體不足connection開不了,連要把設定調回來都沒辦法,還好後來研究出可以用cli 去改...

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<March 2018>
SunMonTueWedThuFriSat
25262728123
45678910
11121314151617
18192021222324
25262728293031
1234567
 
RSS
創用 CC 授權條款
【廣告】
twMVC

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication