The ability to lock pages in memory is used on 64-bit systems to help prevent the operating system from paging out SQL Server’s working set. This may be enabled when the DBA starts seeing errors like the following:
"A significant part of sql server process memory has been paged out. This may result in a performance degradation."
If you’re running SQL Server 2005/2008 Enterprise, you would take the steps to lock pages in memory and you’re done with it. If you’re on SQL Server 2005/2008 Standard Edition, you still have a ways to go. The ability to lock pages in memory for standard
edition is handled through a trace flag. For SQL Server 2005 SP3, you need to apply CU4 . For SQL Server 2008 SP1, you need to apply CU2. Once those CUs have been applied, set trace flag 845 as a startup parameter. Here’s a good ServerFault question that explains how to set a trace flag as a startup parameter.
Once the trace flag was enabled, the memory issues were solved. Day saved, once again.
As with anything, this has the potential to degrade system performance. In this article, scroll to the section entitled “Important considerations before you assign “Lock Pages in memory” user right for an instance of a 64-bit edition of SQL Server”. Read it thoroughly prior to making any changes to your production systems.