SQL Server什么時候啟用“鎖定內存頁”選項 (Windows)
本文轉載自微信公眾號「DBA閑思雜想錄」，作者瀟湘隱者。轉載本文請聯系DBA閑思雜想錄公眾號。
在SQL Server的配置中，尤其是那些應用服務器和數據庫服務器共用/混用的情況下，最好啟用“鎖定內存頁”選項。反而對于單獨的SQL Server數據庫服務器，這個選項反而不是必須的。最近就遇到過這樣一個案例，一個項目的SQL Server數據庫和IIS應用等都部署在同一臺服務器上，當時，Zabbix監控已經出現服務器的CPU告警、以及Swap Usage告警....
登錄服務器，檢查后發現IIS Worker Process進程占用了大量的CPU資源，消耗占用了12.5GB內存(服務器總共16G內存)，由于IIS內存泄露，而SQL Server沒有啟用”鎖定內存頁“選項，即使設定了Maximum server memory，也導致SQL Server的內存被不斷擠占，系統將SQL Server進程用到的數據不斷置換到虛擬內存中去。如下截圖所示，最終導致SQL Server性能嚴重下降，甚至出現無法連接情況。
查看SQL Server的日志信息，你會看到大量這樣的錯誤信息：
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1846 seconds. Working set (KB): 1457444, committed (KB): 10213768, memory utilization: xx%
如果SQL Server啟用了“鎖定內存頁”選項的話，就不會出現這種情況，不會由于內存換出(page out)出現性能和連接問題。下面簡單介紹一下SQL Server服務器”鎖定內存頁“的簡單知識，下面內容節選自官方文檔[1]。官方文檔中的一部分內容，中文翻譯那是相當的糟糕，個人對晦澀難懂或翻譯不當的地方有所糾正。所以下面內容與官方文檔略有差異。敬請知曉。
SQL Server啟用“鎖定內存頁”選項
適用于：SQL Server(所有支持的版本)
此 Windows 策略將確定哪些帳戶的進程可以將數據保留在物理內存中，從而阻止系統將內存中數據換出內存(page out)到磁盤的虛擬內存中。
備注
- 當預計會將內存中頁換出（page out）到磁盤時，鎖定內存中的頁可以大大提高性能。
使用 Windows 組策略工具 (gpedit.msc)，可以為 SQL Server的啟動帳戶啟用此策略。必須是系統管理員才能更改此策略。
啟用“鎖定內存頁”選項
- 在“開始”菜單上，單擊“運行”。在“打開” 框中，鍵入gpedit.msc
- 在“本地組策略編輯器” 控制臺上，展開 “計算機配置” ，再展開 “Windows 設置” 。
- 展開“安全設置”，再展開 “本地策略” 。
- 選擇 “用戶權利指派” 文件夾。細節窗格中隨即顯示出策略。
- 在該窗格中，雙擊“鎖定內存頁”。
- 在“本地安全設置 - 鎖定內存中的頁”對話框中，單擊“添加用戶或組” 。
- 在“選擇用戶”、“服務帳戶”或“組”對話框中，選擇勾選運行sqlservr.exe (SQL Server 啟動帳戶)的帳戶。
- 重啟 SQL Server 服務，以使此設置生效。
鎖定內存頁 (LPIM)
此Windows策略將確定哪些帳戶的進程可以將其使用的數據保留在物理內存中，從而阻止系統將內存中的頁面換出內存(page out)到磁盤的虛擬內存中。將內存中頁換出內存到磁盤時，鎖定內存中的頁可以可使服務器保持響應。向運行sqlservr.exe 的啟動帳戶授予 Windows 鎖定內存頁 (LPIM) 用戶權限時，在 SQL Server Standard Edition 和更高版本的實例中將“鎖定內存頁”選項設置為“打開”。
若要對 SQL Server禁用“鎖定內存頁”選項，請將運行sqlservr.exe(SQL Server 啟動帳戶)的啟動帳戶的帳戶刪除“鎖定內存頁”用戶權限。設置此選項可實現根據其他內存分配器的請求擴大或縮小內存，不影響SQL Server的動態內存管理。使用“鎖定內存頁”用戶權限時，建議按如上所述，為 max server memory 設置一個上限。
重要
應僅在必要時設置此選項，即有跡象表明正在換出 sqlservr 進程的數據時。在這種情況下，錯誤日志將報告錯誤17890，類似于以下示例：A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%. 從 SQL Server 2012 (11.x) 開始，Standard Edition 不需要啟用跟蹤標志 845 來使用“鎖定頁”。
參考資料
[1]鎖定內存頁(LPIM): https://docs.microsoft.com/zh-cn/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-ver15
