Microsoft KB Archive/918483

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

Article ID: 918483

Article Last Modified on 11/30/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Enterprise X64 Edition

-



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

Computers that are running Windows Server 2003 can be too aggressive when they cache dirty buffers if there is an application performing buffered I/O, such as a file copy operation. This behavior can cause the working set in SQL Server to be trimmed. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

920739 You may experience a decrease in overall system performance when you are copying files that are larger than approximately 500 MB in Windows Server 2003 SP1 or in Windows Server 2003 SP2

On a computer that is running SQL Server, applications may use the system cache too much. Therefore, the operating system will trim the working set of SQL Server or of other applications. If you notice that the application uses the system cache too much, you can use some memory management functions in the application. These functions control the system cache space that file IO operations can use in the application. For example, you can use the SetSystemFileCacheSize function and the GetSystemFileCacheSize function to control the system cache space that file IO operations can use.

Note For 64-bit systems, SQL Server 2005 Enterprise Edition is the only edition that is designed to use lock pages in memory.



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.



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$ : 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:
 * 1) Click Start, click Run, type gpedit.msc, and then click OK. The Group Policy window appears.
 * 2) In the left pane, expand Computer Configuration, and then expand Windows Settings.
 * 3) Expand Security Settings, and then expand Local Policies.
 * 4) Click User Rights Assignment. The policies appear in the right pane.
 * 5) In the right pane, double-click Lock pages in memory.
 * 6) In the Local Security Policy Setting dialog box, click Add User or Group.
 * 7) In the Select Users or Groups dialog box, add the account that has permissions to run Sqlservr.exe, and then click OK.
 * 8) 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.

In SQL Server 2005 Service Pack 2 (SP2) or in later versions of SQL Server 2005, if the working set of a SQL Server 2005 process is paged out by Windows, messages that resemble the following are logged in the SQL Server Errorlog file:

Error message 1

2007-01-23 16:30:10.14 spid1s 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): 1086400, committed (KB): 2160928, memory utilization: 50%.

Error message 2

2007-01-23 16:35:26.52 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 315 seconds. Working set (KB): 410156, committed (KB): 2201296, memory utilization: 18%.

Error message 3

2007-01-23 16:40:54.12 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 646 seconds. Working set (KB): 901904, committed (KB): 2215752, memory utilization: 40%.

Note You receive these error messages in both SQL Server 2005 32-bit and SQL Server 2005 64-bit.

These messages are generated when the working set of a SQL Server 2005 process reaches 50 percent of the memory that is committed for the process. Therefore, you can use these messages to determine the case in which SQL Server 2005 performance decreases significantly because Windows trims the working set of the SQL Server 2005 process. Additionally, these messages are logged every 5 minutes in the first 30 minutes. After the first 30 minutes, the frequency of these messages doubles until the frequency reaches the maximum of 1 day. In these messages, notice the percentage values of the memory usage. The percentage values are calculated by using the following formula:

MemoryUsagePercentage = (WorkingSet/CommittedMemory) * 100

Alternatively, run the following statement to examine the percentage value: SELECT * FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

Keywords: kbinfo kbexpertiseadvanced kbsql2005engine KB918483

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.