How to reduce paging of buffer pool memory in SQL Server

Applies to: Microsoft SQL Server 2005 Standard X64 EditionMicrosoft SQL Server 2005 Enterprise X64 EditionSQL Server 2008 Standard

Summary


Microsoft SQL Server performs dynamic memory management based on the memory requirements of the current load and activities on the system. On Windows, SQL Server can use the memory notification mechanisms that are provided by the QueryMemoryResourceNotification Windows API. Based on this information from the QueryMemoryResourceNotification Windows API or from the memory calculation, SQL Server responds to the current memory situation on a specific system. This provides the following benefits:
  • The system does not page out the working set of the SQL Server process.
  • The necessary database pages are available in memory to reduce physical I/O needs.
For more information, see the "Dynamic memory management" topic and the "Server memory options" topic in SQL Server Books Online.

Symptoms


Consider the following issues that may occur in SQL Server:
  • The performance of SQL Server decreases suddenly.
  • A computer that is running SQL Server stops responding for a short time.
  • A time-out occurs for applications that connect to SQL Server.
  • Problems occur when you run even simple commands or use applications on the system.
One of the following error messages may be logged in the SQL Server error log when these problems occur:
Error message 1
DateTime 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
DateTime 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
DateTime 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%.

 
The Microsoft SQL Server support team has also observed additional error messages or warnings that are recorded in the SQL Server Error log or in the Windows Event logs. These messages resemble the following:
DateTime Server      Resource Monitor (0x13c43) Worker 0x0412C1E8 appears to be non-yielding on Node 0. Memory freed: 34152 KB. Approx CPU Used: kernel 171 ms, user 140 ms, Interval: 125093.DateTime Server      * *******************************************************************************DateTime Server      * BEGIN STACK DUMP:DateTime Server      *   DateTime spid 0DateTime Server      * Non-yielding Resource MonitorDateTime Server      * *******************************************************************************DateTime Server      * *******************************************************************************DateTime Server      * BEGIN STACK DUMP:DateTime Server      *   DateTime spid 0DateTime Server      * Non-yielding IOCP ListenerDateTime Server      * *******************************************************************************DateTime spid2s      LazyWriter: warning, no free buffers found.DateTime spid4s      AppDomain xx (SQLCLR.dbo[runtime].xx) is marked for unload due to memory pressure.DateTime spid4s      AppDomain xx (SQLCLR.dbo[runtime].xx) unloaded.DateTime Logon       Error: 17189, Severity: 16, State: 1.DateTime Logon       SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: xx.xxx.xx.xx]Event Type:ErrorEvent Source:SQLBrowserEvent ID:8Description: The SQLBrowser service was unable to process a client request.  
These additional error messages or warnings may appear together with the "A significant part of sql server process memory has been paged out" error message. In several instances, these additional error messages or warnings did not appear together with the error message.

If you notice one of these error messages or warnings, consider the SQL Server working set paging that is discussed in this article as one possible cause but not as the only cause. These additional error messages or warnings could be logged because of various other conditions or causes.

Cause


This problem occurs because the Windows operating system pages out the working set of the SQL Server process.

These error messages are logged when the working set of a SQL Server process reaches 50 percent or less of the memory that is committed to the SQL Server process. You can use these error messages to determine the case in which SQL Server performance decreases significantly because the Windows operating system pages out the working set of the SQL Server process. Additionally, these error messages are logged every five minutes during the first 30 minutes. After the first 30 minutes, the frequency of these error messages doubles until the frequency reaches the maximum of one day.

When this problem occurs, you may notice that the working set of other applications on the system is also paged out around the same time.
For more information, visit the following:

The SQL Server Working Set Message

Separately, a known problem exists in SQL Server in which you may receive an instance of this error message. The error message is logged in the SQL Server Error log during the startup process of SQL Server. The error message might be a false warning. Therefore, it might not indicate that working set trim has actually occurred. For more information, visit the following:

SQL Server reports working set trim warning message during early startup phase

Over allocation of memory in VM settings. For example HyperV or VMWare balloon driver issues. For more information, visit the following:

1003470 Balloon driver retains hold on memory causing virtual machine guest operating system performance issues

2001003 Troubleshooting ESX/ESXi virtual machine performance issues

Workaround


Before you try to work around this problem, perform the steps in the "How to troubleshoot this problem" section to resolve this problem.

If you still experience this problem, you can prevent the Windows operating system from paging out the buffer pool memory of the SQL Server process by locking the memory that is allocated for the buffer pool in physical memory. You lock the memory by assigning the Lock pages in memory user right to the user account that is used as the startup account of the SQL Server service.

Note Using Lock Pages in Memory ensure that the memory managed by SQL Server is not paged out. However, thread stacks, the EXE and any DLL images, heap memory, CLR memory can still be paged out by the OS.

Note Since SQL Server 2008 SP1 Cumulative Update 2 both SQL Server Standard and Enterprise editions can use the Lock pages in memory user right. For more information about support for locked pages, click the following article number to view the article in the Microsoft Knowledge Base:

970070 Support for Locked Pages on SQL Server 2005 Standard Edition and on SQL Server 2008 Standard Edition
 
To assign the Lock pages in memory user right, follow these steps:
  1. Click Start, click Run, type gpedit.msc, and then click OK.

    Note The
    Group Policy dialog box appears.
  2. Expand Computer Configuration, and then expand Windows Settings.
  3. Expand Security Settings, and then expand
    Local Policies.
  4. Click User Rights Assignment, and then double-click Lock pages in memory.
  5. In the Local Security Policy Settingdialog box, click Add User or Group.
  6. In the Select Users or Groups dialog box, add the account that has permission to run the Sqlservr.exe file, and then click
    OK.
  7. Close the Group Policy dialog box.
  8. Restart the SQL Server service.
After you assign the Lock pages in memory user right and you restart the SQL Server service, the Windows operating system no longer pages out the buffer pool memory within the SQL Server process. However, the Windows operating system can still page out the nonbuffer pool memory within the SQL Server process.

You can validate that the user right is used by the instance of SQL Server by making sure that the following message is written in the SQL Server Error Log at startup:
"Using locked pages for buffer pool"
This message applies only to SQL Server. For more information about this message in the ERRORLOG, visit the following:When the Windows operating system pages out the nonbuffer pool memory, you may still encounter performance issues. However, the error messages that are mentioned in the "Symptoms" section are not logged in the SQL Server error log.

More Information


How to troubleshoot this problem

There are three broad categories of problems that can cause this issue:

  1. Application-Related Issues: All applications together have exhausted the available physical memory and the OS must free some memory for new application requests for resources. Typically, the approach here is to find what applications are exhausting the memory and take necessary steps to balance the memory among them without leading to RAM exhaustion.
  2. Device Driver Issues: Device Drivers may cause working set paging of all processes if the driver calls a memory allocation function incorrectly.
  3. Operation System Issues
To troubleshoot this problem, follow these steps:
  1. Application-Related issues: 

    Applications together may consume all of the RAM on the system. If new requests for memory are made, the OS needs to satisfy them and if there is no free memory, it will trim the working set of running applications to satisfy the memory requests. In such cases you may observe that the working set for most if not all applications drops significantly. To observe this collect the following Performance Monitor counter for all applications on the system:

    Performance object: Process
    Counter: Working Set

    Also, monitor the following counter to correlate how much physical memory is available on the system.

    Performance object: Memory
    Counter: Available Memory (MB)

    The typical behavior that you may observe is reduction of Available memory close to 0 MB while at the same time a sudden drop of the Working Set counters for most (all) processes on the system. If you observe such behavior, you may need to take steps to reduce memory usage on the system, which includes for example reducing Max Server Memory for SQL Server.

    Applications may also use the system cache too much, and may cause a large growth of the system cache. To respond to the growth of the system cache, the system pages out the working set of the SQL Server process or of other applications. If you experience this problem, you can use some memory management functions in the application. These functions control the system cache space that file I/O 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 I/O operations can use.

    You can use the Memory performance object to view the values of various counters in this object to determine whether the system cache working set uses too much memory. For example, you can view the Cache Bytes and System Cache Resident Bytes counters. For more information about this topic, visit the following Microsoft websites:

    Too Much Cache

    Microsoft Windows Dynamic Cache Service

    976618 
    You experience performance issues in applications and services when the system file cache consumes most of the physical RAM

    You can download and deploy the "Microsoft Windows Dynamic Cache Service" to control the memory that is consumed by the system cache.

  2. Device Driver Issues:

    If a device driver uses the MmAllocateContiguousMemory function, and if it sets the value of the HighestAcceptableAddress parameter to less than 4 gigabytes (GB), the Windows operating system may page out the working set of the processes on the system including SQL Server process. To resolve this problem, contact the vendor of the device driver for driver updates.

    When a device driver tries to allocate memory, the Windows operating system may page out the working set of other applications. This Windows hotfix lets you use event tracing to find the device driver that causes problem. To find more information about the specific driver that causes the working set trimming behavior, see the MSDN article "Identifying Drivers That Allocate Contiguous Memory."

  3. Operation System Issues: 
    To resolve the known issues that cause the Windows operating system to page out the working set of the SQL Server process, apply the hotfixes that are described in the following Microsoft Knowledge Base articles.
     

    Note Hotfixes are cumulative. A later version of a hotfix contains the earlier versions of that hotfix.

  • The SQL Server set may be trimmed when the system is using some advanced TCP features. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

942861 Error message when an application connects to SQL Server on a server that is running Windows Server: "General Network error," "Communication link failure," or "A transport-level error"

  • On a multiprocessor computer that is running Windows Server 2003, the System process may consume most of the available system memory. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

942486 The System process may consume most of the available system memory on a multiprocessor computer that is running Windows Server 2003

  • If SQL Server is running together with SAP R/3, you may experience a paging issue. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

931308 You may experience increased paging to the hard disk when you run an SAP R/3 program on a Windows Server 2003-based computer

  • If you are running SQL Server on Windows Server 2008, you must apply fixes for known issues that can lead to working set trimming or unnecessary excessive memory consumption by other operating system components. For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

971714 The report generation process may stop responding when you run Perfmon.exe with the Active Directory Diagnostics template to generate a report on a Windows Server 2008-based domain controller

  • If you are running SQL Server on Windows Serve 2008 R2, you must apply fixes for known issues that can lead to working set trimming. For more information, click the following article numbers to view the article in the Microsoft Knowledge Base:

979149 A computer that is running Windows 7 or Windows Server 2008 R2 becomes unresponsive when you run a large application

2155311 Poor performance occurs on a computer that has NUMA-based processors and that is running Windows Server 2008 R2 or Windows 7 if a thread requests lots of memory that is within the first 4 GB of memory

2468345 Computer intermittently performs poorly or stops responding when the Storport driver is used in Windows Server 2008 R2

How to determine the memory that is used by SQL Server 

You can use Performance Monitor to view the memory that is used by SQL Server when Locked Pages in Memory is not enabled.

SQL Server also use the following performance counter to expose the memory that the buffer pool allocates:

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

Note If the instance of the SQL Server is a named instance, the name of the performance object is as follows:

MSSQL$InstanceName: Memory Manager

Additionally, you can measure the effect of paging on SQL Server 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 virtual memory that is currently committed. The Working Set counter measures how much of the currently-committed virtual memory is occupying physical memory (RAM).

If the value of the Working Set counter is less than the value of the Total Server Memory(KB) counter (or that of Private Bytes), at least some memory that is a part of the buffer pool was trimmed from the SQL Server working set.

You can also observe overall paging (working set trimming) on the system by monitoring the following performance counter which shows the pages read from and written to disk:

Performance object: Memory
Counter:  Page Inputs/sec and Page Outputs/sec

For more information about how to use this counters 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
 
After you assign the Lock pages in memory user right and you restart the SQL Server service, the buffer pool of the SQL Server process still responds to memory resource notification events, and it dynamically increases or decreases in response to these events. However, you cannot see memory allocations for the buffer pool that are locked in memory in the following performance counters:
  • The Private Bytes counter and the Working Set counter in Performance Monitor
  • The Mem Usage column on the Processes tab in Task Manager
The reason is that once you enable Locked Pages in Memory, SQL Server starts using the AWE API and does not allocate memory via the OS Virtual Memory Manager. After these pages are locked, these performance counters represent the memory allocations inside the SQL Server process for allocations that do not use the buffer pool. The Total Server Memory(KB) counter of the SQL Server:Memory Manager performance object still accurately represents the memory that is allocated for the buffer pool.

Important considerations before you assign the "Lock pages in memory" user right for an instance of SQL Server

You should make additional considerations before you assign the Lock pages in memory user right. If you assign this user right on systems that are configured incorrectly, the system may become unstable or experience a performance decrease of the whole system. Additionally, event ID 333 may be logged in the event log.

If you contact Microsoft Customer Support Service (CSS) for these problems, CSS engineers may ask you to revoke this user right for the user account that is used as the startup account of the SQL Server service. This step may be necessary to collect important performance data that CSS engineers can use for necessary configuration of the various options for SQL Server and for other applications that are running on the system. After CSS engineers collect the performance data, you can assign the Lock pages in memory user right to the startup account of the SQL Server service.

Before you assign the Lock pages in memory user right, make sure that you capture a Performance Monitor log to determine the memory requirements of various applications and services that are installed on the system. These applications also include SQL Server . To determine the memory requirements, collect the following baseline information:
  • Make sure that you set the max server memory option and the min server memory option correctly. These options reflect only the memory requirement of the buffer pool of the SQL Server process. These options do not include the memory that is allocated for other components within the SQL Server process. These components include the following:
    • The SQL Server worker threads
    • Various DLLs and components that the SQL Server process loads within the address space of the SQL Server process
    • The backup and restore operations
  • The DLLs and components include various OLE DB providers, extended stored procedures, Microsoft COM objects that are used for the sp_OACreate stored procedure, linked servers, and SQL Server CLR. Memory that is allocated for these components falls under the nonbuffer pool region of the address space of the SQL Server process. To ideally determine the maximum amount of memory that the whole SQL Server process can use, you must subtract the memory that is allocated for components that do not use the buffer pool from the total memory that you want the SQL Server process to use. Then, you can use the remainder value to set the max server memory option. Before you set the max server memory option and the min server memory option, you should carefully review the "Setting the memory options manually" topic in SQL Server Books Online.
  • Determine the memory requirement of other applications and of the Windows operating system components. Applications may include other SQL Server components, for example, SQL Server Agent, SQL Server Replication Agents, SQL Server Reporting Services, SQL Server Analysis Services, SQL Server Integration Services, and SQL Server Full Text Search. Applications that perform backup operations and file copy operations may use lots of memory. Consider operations such as bulk copy and the Snapshot Agent that generate file IO. You must consider the memory requirement of all these applications when you determine the value of the max server memory option and of the min server memory option. You can use the Private Bytes counter and the Working Set counter under the Process object for every process to determine the memory requirement for a specific process.
  • By default, the Lock pages in memory user right has already been assigned to the built-in Local System account. For more information, visit the following Microsoft Web site:
  • If you use a Windows user account globally for all SQL Server processes in a domain, determine the user rights that are assigned by using a Group Policy configuration. A 32-bit SQL Server process may use this account as the startup account. However, this account requires the Lock pages in memory user right to enable the Address Windowing Extensions (AWE) feature. For more information, see the "Providing the maximum amount of memory to SQL Server" topic in SQL Server Books Online.
  • Before you configure the max server memory option and the min server memory option for multiple SQL Server instances, consider the memory requirements of the nonbuffer pool for each instance of SQL Server. Then, configure these options for each instance of SQL Server.

Ideally, you collect this baseline information during peak loads. Therefore, you can determine the memory requirements for various applications and components to support the peak load. The memory requirements vary from one system to another system, depending on the activities and the applications that are running on the system.

You can query the information that is provided in the dynamic management view sys.dm_os_process_memory to understand whether the system is encountering low memory conditions. For more information, see the SQL Server Books Online reference at the following site:

sys.dm_os_process_memory (Transact-SQL)

Improvements added in Windows Server 2008 and in Windows Server 2008 R2 that Reduce Working Set Trimming


Windows Server 2008 and Windows Server 2008 R2 improve the contiguous memory allocation mechanism. This improvement lets Windows Server 2008 and Windows Server 2008 R2 reduce to a certain extent the effects of paging out the working set of applications when new memory requests arrive. 

The following is an explanation of the improvements from the Microsoft whitepaper "Advances in Memory Management in Windows": 
 
In Windows Server 2008, the allocation of physically contiguous memory is greatly enhanced. Requests to allocate contiguous memory are much more likely to succeed because the memory manager now dynamically replaces pages, typically without trimming the working set or performing I/O operations. In addition, many more types of pages—such as kernel stacks and file system metadata pages, among others—are now candidates for replacement. Consequently, more contiguous memory is generally available at any given time. In addition, the cost to obtain such allocations is greatly reduced.
To view the "Advances in Memory Management in Windows" whitepaper, visit the following Microsoft Website: For more information, visit the following Microsoft websites: 
2001745 Working Set Trimming can negatively impact SQL, Exchange, and Operating System performance under Windows 2003
Note On a Windows Server 2008-based computer, you must install SQL Server Service Pack 2 (SP2) or a later SQL Server service pack.

The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, about the performance or reliability of these products.