SQLServer因為OS虛擬記憶體不足而hang住異常解決-鎖定記憶體頁 (LPIM)

異常：

DB: SQL Server2012SP3

OS: Windows2012

一線工程師反應系統訪問DB突然卡住了，DBA登入DB檢查SQLServer發現DB無法登入，之前SMSS已經連線的session中無法執行任何SQL。伺服器CPU使用10%，memory使用達到了99%以上。過了十分鐘系統memory自己降低到了90%以下，DB可以訪問。

分析：

檢查SQLServer error log:

只是在一異常時間段有stack dump的記錄，但沒有太多有價值的資訊

檢查windows效能計數器：

異常時間段OS memory確實耗盡

檢查windows log:

有虛擬記憶體不夠警告，

sqlserver.exe程式使用28GB(DBA設定的DB最大記憶體使用量)，dllhost.exe程式使用12GB

正好把伺服器的32GB+虛擬記憶體設定的8GB佔滿!所以報錯記憶體不足!!

解決：

上述dllhost.exe程式使用12GB一定存在問題，此塊可能系統層面補丁或病毒相關，已交給系統工程師分析。

但是否有辦法在OS記憶體不足時，DBA透過設定可以不影響到SQL server的正常執行呢?

答案是有的，軟微官方提供鎖定記憶體頁 (LPIM) 解決方法，透過Windows 策略將確定哪些帳戶可以使用程式將資料保留在實體記憶體中，從而阻止系統將資料分頁到磁碟的虛擬記憶體中。這樣即使OS記憶體不足時對SQL server的影響也會是最小了。

設定：

cmd->gpedit.msc->local computer policy->computer configuration->windows settings->security settings->local policies->user rights assignment-> Lock pages memory中加入新增 SQL Server 啟動帳戶

至此，鎖定記憶體頁 (LPIM)設定完成。

再持續觀察一段時間，系統再沒有發生系統記憶體不足SQLServer hang住問題。

------------------------------------------------------------ ------------------------------------------

以下附，軟微官方文件說明：

鎖定記憶體頁 (LPIM)

此 Windows 策略將確定哪些帳戶可以使用程式將資料保留在實體記憶體中，從而阻止系統將資料分頁到磁碟的虛擬記憶體中。 鎖定記憶體中的頁可以在發生將記憶體分頁到磁碟時保持伺服器的響應能力。 已向有權執行 sqlservr.exe 的帳戶授予 Windows 鎖定記憶體頁 (LPIM) 使用者許可權時，在 SQL Server Standard Edition 和更高版本的例項中將“鎖定記憶體頁”  選項設定為“開啟”  。

若要對 SQL Server 禁用“鎖定記憶體頁”選項，請為有權執行 sqlservr.exe（ SQL Server 啟動帳戶）啟動帳戶的帳戶刪除“鎖定記憶體頁”使用者許可權  。

設定此選項可實現根據其他記憶體分配器的請求擴大或縮小記憶體，不影響 SQL Server 動態記憶體管理。 使用“鎖定記憶體頁”使用者許可權時，建議按 如上所述，為 max server memory 設定一個上限   。

啟用“鎖定記憶體頁”

啟用“鎖定記憶體頁”選項：

1. 在 “開始” 選單上，單擊 “執行” 。 在“開啟”  框中，鍵入 gpedit.msc

   將開啟 “組策略” 對話方塊。

2. 在 “組策略” 控制檯上，展開 “計算機配置” ，再展開 “Windows 設定” 。

3. 展開 “安全設定” ，再展開 “本地策略” 。

4. 選擇 “使用者權利指派” 資料夾。

   細節窗格中隨即顯示出策略。

5. 在該窗格中，雙擊“鎖定記憶體頁”  。

6. 在“本地安全策略設定”對話方塊中，新增有權執行 sqlservr.exe （ SQL Server 啟動帳戶）的帳戶  。