How to reduce paging of buffer pool memory in the 64-bit SQL Server 2005 Standard version of SQL Server

  • Article

SQL Server 2005 SP2 introduces a new warning message that indicate that working set (RAM resident portion of SQL Server) has been paged out. This is a common problem that points to excessive paging that leads to potential severe performance problems. The following warning message is logged on SQL Server 2005 error log file:

(…)

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 42988<c/> committed (KB): 105584<c/> memory utilization: 40%.      

(…)

The signaled trim allows SQL Server to properly remove the oldest references from the BPool (LRU) and maintain optimal performance for the overall server load. The Self Trim and Hard Trim activities can trigger the SQL Server error log message indicating that a significant portion of the SQL Server memory has been placed in the page file and generally leads to performance problems.

Typical scenarios that this could happen are described here - https://support.microsoft.com/kb/918483 - How to reduce paging of buffer pool memory in the 64-bit version of SQL Server

On this article it’s mentioned a method to prevent Windows from paging out the buffer pool memory of SQL Server memory by locking the memory that is allocated for the buffer pool in physical memory. However this method only applies for SQL Server 2005 Enterprise Edition not for SQL Server 2005 Standard Edition.

We got a lot of feedback from customers who wanted to have support for this in SQL Server Standard Edition too.  So we are very pleased to announce that it will be available in SQL Server 2005 and SQL Server 2008 Standard Edition too (as of SQL Server 2005 SP3 CU4 and SQL Server 2008 SP1 CU2).  As with everything do not just enable this option by default but carefully plan and test.

More information on this can be found on https://blogs.msdn.com/psssql/archive/2009/04/24/sql-server-locked-pages-and-standard-sku.aspx
Slava Oks did a great job in explaining the Lock Pages In Memory (and AWE) mechanism on https://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx and https://blogs.msdn.com/slavao/archive/2005/08/31/458545.aspx.