Another Windows setting you might want to enable is “Lock pages in memory”. There is some controversy within the SQL Server community and within Microsoft about whether and when you should enable this setting. Before you decide whether you want to do this, let me give you some background. When SQL Server 2005 was first released (in early 2006), and was installed on systems running x64 Windows Server 2003 or x64 Windows Server 2003 R2, it quickly became pretty common to enable “Lock pages in memory”, to try to prevent the operating system from periodically paging out a large amount of memory from SQL Server’s working set, which would cause a very noticeable bad effect on SQL Server performance.

The reason that this usually happened is because the operating system would run low on available memory (typically due to buggy device drivers), and then the operating system would page out a large portion of the working set of the SQL Server process in order to free up memory for the operating system. This made the operating system happier, but had a terrible effect on SQL Server, since it would have to heavily access the storage subsystem to read back in the data that had recently been the buffer pool.

SQL Server 2005 and above is designed to perform dynamic memory management based on the memory requirements of the current load on the system. On a Windows Server 2003 or later operating system, SQL Server can use the memory notifications from the QueryMemoryResourceNotification Windows API. This is meant to keep the operating system from paging out the working set of the SQL Server process, and it helps keep more database pages available in memory to reduce your physical I/O needs.

The problem was that SQL Server 2005 did not always react quickly enough to a low memory notification from the operating system, so the operating system would take matters into its own hands, and force SQL Server to release a large portion of its working set. Using “Lock pages in memory” prevents this from happening, at the cost of masking the root cause of the issue.

Initially, this setting would only be honored by SQL Server 2005 Enterprise Edition. Microsoft’s official stance was that if you encountered this issue, you should open a support incident with Microsoft CSS to try to find the root cause of the problem. Their opinion was that it was better to find the cause of the problem instead of using “Lock pages in memory” as a Band-Aid to cover up the problem.

Many production DBAs disagreed with this idea. Once you had been affected by this issue a couple of times, and had to explain to your management team or customers why your application suddenly started timing out for no apparent reason, you were usually quite ready to enable” Lock pages in memory”. Later, after a lot of pressure from the SQL Server MVP community, Microsoft finally made “Lock pages in memory” available in SQL Server Standard Edition (in later builds of SQL Server 2005, 2008, and 2008 R2), by using a startup trace flag of 845.

Microsoft has published conflicting information about this issue over the years. The current official stance is that you should not have to use “Lock pages in memory” with Windows Server 2008 and newer, because of improvements in memory management and improved device drivers. Off the record, I have heard several Microsoft employees concede that it may still be necessary to use this setting in some situations. I don’t want people to race out and immediately enable “Lock pages in memory”. Instead, you should consider your specific situation. and use your own judgment before you decide what to do. If I was running SQL Server 2005 on top of Windows Server 2003, I would be more tempted to use this setting. If I was running SQL Server 2008 R2 on top of Windows Server 2008 R2, I would be much less tempted to use this setting. You also have to consider whether you have run into the problem that LPIM was designed to alleviate. Remember the old saying “Don’t fix it if it ain’t broke”…

You have to grant this right to the Windows account that the SQL Server Service is using. This would normally be a Windows domain account. You can do this by using the Local Group Policy Editor on the machine where SQL Server will be running. You can just type GPEDIT.MSC in a Run window, which will bring up the Local Group Policy Editor shown in Figure 1. Then you go to Computer Configuration, Windows Settings, Security Settings, Local Policies, User Rights Assignment.

Next, in the right hand portion of the dialog window, you simply right-click on “Lock pages in memory” and select Properties, and click on the Add User or Group button. Then you need to add the name of SQL Server Service account, and click Ok. You have to restart the SQL Server Service to have this setting take effect.

Figure 1: Using the Local Group Policy Editor to grant the “Lock pages in memory” right to the SQL Server Service account

If you do enable “Lock Pages in Memory”, it is very important that you also set an explicit MaxServerMemory setting for your SQL Server instance, which I discussed in more detail here. This will control how much memory can be used by the SQL Server Buffer Pool, allowing a cushion for the operating system and anything else (including other SQL Server components) that may be running on the instance or machine.

You can confirm that the user right is used by the instance of SQL Server by making sure that the following message is written in the SQL Server Error Log at startup: “Using locked pages for buffer pool”.