SQL Server什么时候启用“锁定内存页”选项 (Windows)

    在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 servre进程用到的数据置换到虚拟内存中去。如下截图所示，最终导致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服务器”锁定内存页“的简单知识，下面内容节选自官方文档。官方文档中的一部分内容，中文翻译那是相当的糟糕，个人对晦涩难懂或翻译不当的地方有所纠正。所以下面内容与官方文档略有差异。敬请知晓。

 

 

sql server启用“锁定内存页”选项

 

 

适用于： sql server（所有支持的版本）

 

此 windows 策略将确定哪些帐户的进程可以将数据保留在物理内存中，从而阻止系统将内存中数据换出内存（page out）到磁盘的虚拟内存中。

 

备注

当预计会将内存中页换出（page out）到磁盘时，锁定内存中的页可以大大提高性能。

 

使用 windows 组策略工具 (gpedit.msc)，可以为 sql server的启动帐户启用此策略。必须是系统管理员才能更改此策略。

 

启用“锁定内存页”选项

 

1.在“开始”菜单上，单击“运行”。 在“打开” 框中，键入gpedit.msc

 

2.在“本地组策略编辑器” 控制台上，展开 “计算机配置” ，再展开 “windows 设置” 。

 

3.展开“安全设置”，再展开 “本地策略” 。

 

4.选择 “用户权利指派” 文件夹。

细节窗格中随即显示出策略。

 

5.在该窗格中，双击“锁定内存页”。

 

6.在“本地安全设置 - 锁定内存中的页”对话框中，单击“添加用户或组” 。

 

7.在“选择用户”、“服务帐户”或“组”对话框中，选择勾选运行sqlservr.exe （sql server 启动帐户）的帐户。

 

8.重启 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 来使用“锁定页”。

 

 

参考资料：

 

https://docs.microsoft.com/zh-cn/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-ver15