Lock Pages in Memory is a setting that can be set on 64-bit operating systems that essentially tell Windows not to swap out SQL Server memory to disk. By default, this setting is turned off on 64-bit systems, and depending on the circumstances, the following conditions could occur when this setting is not turned on:

1) Performance of SQL Server could suddenly decreases for no apparent reason.

2) The hardware running SQL Server could suddenly appear to stop responding for a short period of time.

3) Applications connected to SQL Server could time out.

4) Among many others.

This sudden performance degradation can occur when Windows tells SQL Server that it needs more physical memory, and SQL Server, in response to the request, begins to swap the data out of RAM (such as the data cache) to disk, killing performance. Obviously, this is something that you don’t want to have happen on your production server.

Microsoft, and many SQL Server experts, recommend that in most cases, the Lock Pages in Memory setting be turned on for 64-bit SQL Server instances when running under Windows 2003 and earlier. With this option turned on, SQL Server will refuse Window’s request for giving up its memory, this preventing the problems described above. This problem occurs much less in SQL Server instances running under SQL Server 2008, and because of this, turning on Lock Pages in Memory is most likely not needed, and many SQL Server experts are now recommending that it not be turned on when using Windows 2008 unless you actually experience this problem.

According to the poll, 40% of those responders who have 64-bit systems turn this setting on, while another 30% don’t. I don’t know if the 30% who don’t turn it on are deliberately not turning on for a specific reason (perhaps because they are running under Windows 2008), or they are not turning it on because they don’t know about the potential benefits it offers. Another 30% haven’t heard of this option, which is a shame, as this setting can have significant performance effects on a server, and DBAs need to know more about it. If you want to learn more about this option, check out these resources.

• How to Reduce Paging of Buffer Pool Memory in the 64-Bit Version of SQL Server
• Lock Pages in Memory: Do You Really Need It?
• How to Enable the Lock Pages in Memory Option

Before turning Lock Pages in Memory on for your SQL Server instance, be sure to carefully read the article "How to Reduce Paging of Buffer Pool Memory in the 64-Bit Version of SQL Server" listed above. It will provide you the information you need to know before you make the decision as to whether or not you want to turn Lock Pages in Memory on for a particular SQL Server instance. Before making any changes in SQL Server, it is very important that you test them before implementing them in production.