You can enable the lock pages in memory permissions to prevent SQL Server 2005 64-bit buffer pool memory from being paged out of physical memory (918483)

INTRODUCTION

Under certain circumstances, the working set of the Microsoft SQL Server 2005 64-bit process may be paged out by Microsoft Windows. This behavior may cause a decrease in SQL Server 64-bit performance. Microsoft has found an issue that may cause the SQL Server 2005 64-bit working set to be trimmed. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

905865 The sizes of the working sets of all the processes in a console session may be trimmed when you use Terminal Services to log on to or log off from a computer that is running Windows Server 2003

Additionally, third-party device drivers that use the MmAllocateContiguousMemory function and specify the value of the HighestAcceptableAddress parameter to less than 4 gigabytes (GB) may also cause the SQL Server 2005 64-bit working set to be trimmed.

To prevent SQL Server 2005 64-bit buffer pool memory from being paged out of physical memory, you can enable the lock pages in memory permissions.

MORE INFORMATION

How to determine the memory page that is used by SQL Server 2005 64-bit

You can use Performance Monitor to view the Memory page that is used by SQL Server 2005 64-bit. To do this, monitor the following performance counter:

Performance object: Memory
Counter: Pages/Sec

For more information about how to use this counter to determine the memory page, click the following article number to view the article in the Microsoft Knowledge Base:

889654 How to determine the appropriate page file size for 64-bit versions of Windows Server 2003 or Windows XP

Additionally, you can measure the effect of paging on SQL Server 2005 64-bit by monitoring the following performance counters:

Performance object: Process
Counter: Private Bytes
Instance: sqlservr

Performance object: Process
Counter: Working Set
Instance: sqlservr

The Private Bytes counter measures the amount of memory that is currently committed. The Working Set counter measures the amount of physical memory that the process currently occupies. SQL Server 2005 64-bit also uses the following performance counter to expose the amount of memory that the buffer pool allocates:

Performance object: SQL Server:Memory Manager
Counter: Total Server Memory(KB).

Note If the instance of SQL Server 2005 64-bit is a named instance, the name of the performance object is MSSQL$InstanceName: Memory Manager.

If the value of the Working Set counter is less than the value of the Total Server Memory(KB) counter, at least some of the buffer pool has been trimmed from the SQL Server working set.

How to lock pages in memory for an instance of SQL Server 64-bit

In SQL Server 64-bit, you can improve performance by locking memory that is allocated for the buffer pool in physical memory. To enable this capability in SQL Server 64-bit, you must grant the SQL Server startup account the Lock pages in memory permission. When you enable this option, you must restart the computer. To do this, follow these steps:

Click Start, click Run, type gpedit.msc, and then click OK. The Group Policy window appears.
In the left pane, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Click User Rights Assignment. The policies appear in the right pane.
In the right pane, double-click Lock pages in memory.
In the Local Security Policy Setting dialog box, click Add User or Group.
In the Select Users or Groups dialog box, add the account that has permissions to run Sqlservr.exe, and then click OK.
Close the Group Policy window, and then restart the SQL Server service.

After you restart the SQL Server service, all memory that is allocated for the buffer pool will be ineligible for paging by Windows. The buffer pool retains the ability to respond to memory resource notification events and dynamically grow or reduce in response to these events. Allocations that are locked in memory will no longer be visible in any one of the following locations:

The Private Bytes and Working Set counters in Performance Monitor
The Mem Usage column on the Processes tab in Windows Task Manager

These counters will reflect allocations that are performed inside the SQL Server 2005 process when those allocations are not buffer pool related. The Total Server Memory(KB) performance counter of the SQL Server:Memory Manager object will accurately reflect the total amount of memory that is allocated for the buffer pool.