While the SQL Server process is designed to be self-tuning with regard to its own
memory allocation needs, it can't account for memory demands made by other processes running on the server, or by operations such as file copies. Its self-tuning nature means that it will respond to memory pressure, as and when signaled by the operating system. This means that, ultimately, the operating system is in control of the response to memory pressure, and one of the ways that the OS responds is by trimming, and possibly paging out, the working set of processes that are consuming memory, including, of course, the SQL Server process. When this occurs, memory allocations that are backed by physical memory are written to virtual memory in the system page file on disk. If the process's working set is relatively small, this is not generally a big problem, but on 64-bit SQL Servers with large amounts of RAM installed, where all memory is committed using VAS only and so is pageable, this can significantly impact performance.
If SQL Server experiences a working set trim, or memory gets paged out, SQL will write a message in the error log as follows:
A significant part of sql server process memory has been paged out. This may result in a performance degrada- tion. Duration: 0 seconds. Working set (KB): 16484, committed (KB): 6239692, memory utilization: 0%.
If this message is frequently seen, it indicates there is a problem with SQL getting paged out.
On 32-bit SQL Servers this wasn't generally a big issue since the pageable memory that could be allocated by SQL Server was limited to 2 GB of user mode VAS. However, on 64-bit systems, the SQLOS can, by default, allocate all of the memory on the server using calls to VirtualAlloc, which are non-locked and pageable. On a server with 16 GB of RAM, this can result in a significantly sized, pageable working set for the SQL Server process. This was especially problematic on Windows Server 2003 where the operating system responded aggressively to memory pressure by trimming working sets. First, Windows would ask SQL Server to trim its working set, which would prompt de-allocation of memory down to the min server memory setting. If SQL Server didn't trim enough memory, or didn't trim fast enough, the OS could force part of SQL Server's memory allocation out of physical memory and into the paging file. At this point, the buffer cache is still allocated, but it is not backed by physical memory, it is backed by page file on disk which is very slow and causes the performance issues.
To prevent this from happening on 64-bit SQL Servers, the Lock Pages in Memory privilege can be assigned to the SQL Server service account, in which case memory is allocated to the buffer pool using the Win32 function AllocateUserPhysicalPages, which is provided by the AWE API. This is the same function that is used in 32-bit SQL Servers that have AWE enabled in order to allocate memory above 4 GB. Even though the same function is used to perform the memory allocations, it is important to note that AWE is not being used by the 64-bit SQL Server and the 'awe enabled' sp_configure option does not have to be set. The function is being used simply so that the allocated pages are locked and cannot be paged to disk.
Do NOT enable AWE on 64-bit SQL Server
The ability to lock pages in 64-bit SQL Server requires only the Lock Pages in Memory privilege for
the service account; it does not require setting the 'awe enabled' sp_configure option, which is a
One of the problems that people often run into when running SQL Server using Lock Pages in Memory is that the information returned by Task Manager no longer reflects the total memory use by the sqlservr.exe process. The information shown in Task Manager is only for the non-buffer pool memory usage by the sqlservr.exe process and does not include the memory allocated to the buffer pool using locked pages, as shown in Figure 4.3.
Figure 4.3: SQL Server Process memory usage (locked pages).
The server shown in Figure 4.3 has 64 GB of RAM installed in it, of which 97% is currently allocated, primarily by SQL Server. To determine the total amount of memory that is being used by SQL Server, the SQL Server:Memory Manager\Total Server Memory performance counter should be used.
Initially, in SQL Server 2005 and 2008, Lock Pages in Memory was an Enterprise-only feature. However, it was added to SQL Server Standard Edition in SQL Server 2008 R2, and can be used in SQL Server 2008 SP1 with Cumulative Update 2 or later, and in SQL Server 2005 SP3 with Cumulative Update 4 or later. Having applied the appropriate update to the server, the startup Trace Flag –T845 must be added to the SQL Server service startup parameters for the instance, in order to begin using the feature. Since the memory allocated using Lock Pages in Memory is locked and cannot be paged, it is recommended that the max server memory sp_configure option be set to limit the amount of memory that SQL Server can use, and so prevent starving the OS of memory.
Additional references for Lock Pages in Memory and how to reduce paging
How to reduce paging of buffer pool memory in the 64-bit version of SQL Server: http://support. microsoft.com/kb/918483.
Support for locked pages on SQL Server 2005 Standard Edition 64-bit systems and on SQL Server 2008 Standard Edition 64-bit systems: http://support.microsoft.com/kb/970070.