[[409898]]

本文轉(zhuǎn)載自微信公眾號(hào)「DBA閑思雜想錄」，作者瀟湘隱者。轉(zhuǎn)載本文請(qǐng)聯(lián)系DBA閑思雜想錄公眾號(hào)。

在SQL Server的配置中，尤其是那些應(yīng)用服務(wù)器和數(shù)據(jù)庫服務(wù)器共用/混用的情況下，最好啟用“鎖定內(nèi)存頁”選項(xiàng)。反而對(duì)于單獨(dú)的SQL Server數(shù)據(jù)庫服務(wù)器，這個(gè)選項(xiàng)反而不是必須的。最近就遇到過這樣一個(gè)案例，一個(gè)項(xiàng)目的SQL Server數(shù)據(jù)庫和IIS應(yīng)用等都部署在同一臺(tái)服務(wù)器上，當(dāng)時(shí)，Zabbix監(jiān)控已經(jīng)出現(xiàn)服務(wù)器的CPU告警、以及Swap Usage告警....

登錄服務(wù)器，檢查后發(fā)現(xiàn)IIS Worker Process進(jìn)程占用了大量的CPU資源，消耗占用了12.5GB內(nèi)存(服務(wù)器總共16G內(nèi)存)，由于IIS內(nèi)存泄露，而SQL Server沒有啟用”鎖定內(nèi)存頁“選項(xiàng)，即使設(shè)定了Maximum server memory，也導(dǎo)致SQL Server的內(nèi)存被不斷擠占，系統(tǒng)將SQL Server進(jìn)程用到的數(shù)據(jù)不斷置換到虛擬內(nèi)存中去。如下截圖所示，最終導(dǎo)致SQL Server性能嚴(yán)重下降，甚至出現(xiàn)無法連接情況。

查看SQL Server的日志信息，你會(huì)看到大量這樣的錯(cuò)誤信息：

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啟用了“鎖定內(nèi)存頁”選項(xiàng)的話，就不會(huì)出現(xiàn)這種情況，不會(huì)由于內(nèi)存換出(page out)出現(xiàn)性能和連接問題。下面簡單介紹一下SQL Server服務(wù)器”鎖定內(nèi)存頁“的簡單知識(shí)，下面內(nèi)容節(jié)選自官方文檔[1]。官方文檔中的一部分內(nèi)容，中文翻譯那是相當(dāng)?shù)脑愀?，個(gè)人對(duì)晦澀難懂或翻譯不當(dāng)?shù)牡胤接兴m正。所以下面內(nèi)容與官方文檔略有差異。敬請(qǐng)知曉。

SQL Server啟用“鎖定內(nèi)存頁”選項(xiàng)

適用于：SQL Server(所有支持的版本)

此 Windows 策略將確定哪些帳戶的進(jìn)程可以將數(shù)據(jù)保留在物理內(nèi)存中，從而阻止系統(tǒng)將內(nèi)存中數(shù)據(jù)換出內(nèi)存(page out)到磁盤的虛擬內(nèi)存中。

備注

當(dāng)預(yù)計(jì)會(huì)將內(nèi)存中頁換出（page out）到磁盤時(shí)，鎖定內(nèi)存中的頁可以大大提高性能。 

使用 Windows 組策略工具 (gpedit.msc)，可以為 SQL Server的啟動(dòng)帳戶啟用此策略。必須是系統(tǒng)管理員才能更改此策略。

啟用“鎖定內(nèi)存頁”選項(xiàng)

1. 在“開始”菜單上，單擊“運(yùn)行”。在“打開” 框中，鍵入gpedit.msc
2. 在“本地組策略編輯器” 控制臺(tái)上，展開 “計(jì)算機(jī)配置” ，再展開 “Windows 設(shè)置” 。
3. 展開“安全設(shè)置”，再展開 “本地策略” 。
4. 選擇 “用戶權(quán)利指派” 文件夾。細(xì)節(jié)窗格中隨即顯示出策略。
5. 在該窗格中，雙擊“鎖定內(nèi)存頁”。
6. 在“本地安全設(shè)置 - 鎖定內(nèi)存中的頁”對(duì)話框中，單擊“添加用戶或組” 。
7. 在“選擇用戶”、“服務(wù)帳戶”或“組”對(duì)話框中，選擇勾選運(yùn)行sqlservr.exe (SQL Server 啟動(dòng)帳戶)的帳戶。
8. 重啟 SQL Server 服務(wù)，以使此設(shè)置生效。

鎖定內(nèi)存頁 (LPIM)

此Windows策略將確定哪些帳戶的進(jìn)程可以將其使用的數(shù)據(jù)保留在物理內(nèi)存中，從而阻止系統(tǒng)將內(nèi)存中的頁面換出內(nèi)存(page out)到磁盤的虛擬內(nèi)存中。將內(nèi)存中頁換出內(nèi)存到磁盤時(shí)，鎖定內(nèi)存中的頁可以可使服務(wù)器保持響應(yīng)。向運(yùn)行sqlservr.exe 的啟動(dòng)帳戶授予 Windows 鎖定內(nèi)存頁 (LPIM) 用戶權(quán)限時(shí)，在 SQL Server Standard Edition 和更高版本的實(shí)例中將“鎖定內(nèi)存頁”選項(xiàng)設(shè)置為“打開”。

若要對(duì) SQL Server禁用“鎖定內(nèi)存頁”選項(xiàng)，請(qǐng)將運(yùn)行sqlservr.exe(SQL Server 啟動(dòng)帳戶)的啟動(dòng)帳戶的帳戶刪除“鎖定內(nèi)存頁”用戶權(quán)限。設(shè)置此選項(xiàng)可實(shí)現(xiàn)根據(jù)其他內(nèi)存分配器的請(qǐng)求擴(kuò)大或縮小內(nèi)存，不影響SQL Server的動(dòng)態(tài)內(nèi)存管理。使用“鎖定內(nèi)存頁”用戶權(quán)限時(shí)，建議按如上所述，為 max server memory 設(shè)置一個(gè)上限。

重要

應(yīng)僅在必要時(shí)設(shè)置此選項(xiàng)，即有跡象表明正在換出 sqlservr 進(jìn)程的數(shù)據(jù)時(shí)。在這種情況下，錯(cuò)誤日志將報(bào)告錯(cuò)誤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 不需要啟用跟蹤標(biāo)志 845 來使用“鎖定頁”。

參考資料

[1]鎖定內(nèi)存頁(LPIM): https://docs.microsoft.com/zh-cn/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-ver15