Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
How to reduce paging of buffer pool memory in the 64-bit version of SQL Server
Article ID: 918483 - View products that this article applies to.
Microsoft SQL Server performs dynamic memory management based on the memory requirements of the current load and activities on the system. On a Windows Server 2003 or a Windows XP or later version system, SQL Server can use the memory notification mechanisms that are provided by the QueryMemoryResourceNotification Windows API. On a Microsoft Windows 2000 Server-based system, SQL Server periodically calculates the free physical memory on the system by using the native 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:
In 64-bit editions of SQL Server, various problems may occur. For example, the following problems may occur:
Error message 1
date time 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
date time 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
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:
date time 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%.
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.
2009-05-05 15:43:56.01 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. 2009-05-05 12:54:52.18 Server * ******************************************************************************* 2009-05-05 12:54:52.18 Server * BEGIN STACK DUMP: 2009-05-05 12:54:52.18 Server * 05/05/08 12:54:52 spid 0 2009-05-05 12:54:52.18 Server * Non-yielding Resource Monitor 2009-05-05 12:54:52.18 Server * ******************************************************************************* 2009-06-10 09:13:53.44 Server * ******************************************************************************* 2009-06-10 09:13:53.44 Server * BEGIN STACK DUMP: 2009-06-10 09:13:53.44 Server * 06/10/09 09:13:53 spid 0 2009-06-10 09:13:53.44 Server * Non-yielding IOCP Listener 2009-06-10 09:13:53.44 Server * ******************************************************************************* 2009-06-10 09:13:55.85 spid2s LazyWriter: warning, no free buffers found. 2009-07-15 13:27:45.35 spid4s AppDomain xx (SQLCLR.dbo[runtime].xx) is marked for unload due to memory pressure. 2009-07-15 13:27:45.35 spid4s AppDomain xx (SQLCLR.dbo[runtime].xx) unloaded. 2009-07-15 13:37:51.42 Logon Error: 17189, Severity: 16, State: 1. 2009-07-15 13:37:51.42 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: Error Event Source: SQLBrowser Event ID: 8 Description: The SQLBrowser service was unable to process a client request.
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.
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 Microsoft Web site:
http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspxAdditionally, 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 Microsoft website:
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 For 64-bit editions of SQL Server, only SQL Server Enterprise Edition can use the Lock pages in memory user right. This is applicable for SQL Server 2005 [RTM, SP1, SP2, SP3] and for SQL Server 2008 [RTM and SP1]. SQL Server 2008 SP1 Cumulative Update 2 and SQL Server 2005 SP3 Cumulative Update 4 introduce support for SQL Server Standard editions to use the Lock pages in memory user right. For more information about support for locked pages on 64 bit systems, click the following article number to view the article in the Microsoft Knowledge Base:
970070To assign the Lock pages in memory user right, follow these steps:
(http://support.microsoft.com/kb/970070/ )Support for Locked Pages on SQL Server 2005 Standard Edition 64-bit systems and on SQL Server 2008 Standard Edition 64-bit systems
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 poolThis message applies only to 64-bit editions of SQL Server. For more information about this message in the ERRORLOG, visit the following Microsoft Web site:
http://blogs.msdn.com/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspxWhen 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. This behavior occurs because the working set of a SQL Server process typically does not reach 50 percent or less of the committed memory.
How to troubleshoot this problemTo troubleshoot this problem, follow these steps:
How to determine the memory that is used by 64-bit editions of SQL ServerYou can use Performance Monitor to view the memory page that is used by 64-bit editions of SQL Server. To do this, monitor the following performance counter:
Performance object: MemoryFor 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:
889654Additionally, you can measure the effect of paging on 64-bit editions of SQL Server by monitoring the following performance counters:
(http://support.microsoft.com/kb/889654/ )How to determine the appropriate page file size for 64-bit versions of Windows Server 2003 or Windows XP
Performance object: ProcessThe Private Bytes counter measures the memory that is currently committed. The Working Set counter measures the physical memory that is currently occupied by the process. 64-bit editions of SQL Server also use the following performance counter to expose the memory that the buffer pool allocates:
Counter: Private Bytes
Performance object: Process
Counter: Working Set
Performance object: SQL Server:Memory ManagerNote If the instance of the 64-bit edition of SQL Server is a named instance, the name of the performance object is as follows:
Counter: Total Server Memory(KB)
MSSQL$InstanceName: Memory ManagerIf the value of the Working Set counter is less than the value of the Total Server Memory(KB) counter, at least some memory that is a part of the buffer pool was trimmed from the SQL Server working set.
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:
Important considerations before you assign the "Lock pages in memory" user right for an instance of a 64-bit edition of SQL ServerYou 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:
How to use the information from SQL Server ring buffers to determine the memory conditions when paging occursYou can use information from SQL Server ring buffers to determine the memory conditions on the server when paging occurs. You can use a script such as the following script to obtain this information.
Note This query is valid for both SQL Server 2005, and SQL Server 2008.
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 2008 Books Online reference at the following MSDN Web site:
SQL Server 2008 R2 BPA informationThe SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides multiple rules to detect situations that can result in working set trimming of SQL Server process. The SQL Server 2008 R2 BPA supports SQL Server 2008 and SQL Server 2008 R2.
If you run the BPA tool and receive the warning, "Database Engine - SQL Server memory settings are incorrect," you need to check the max server memory setting for this instance of SQL Server and follow the recommendations in the section “Important considerations before you assign the 'Lock pages in memory' user right for an instance of a 64-bit edition of SQL Server” of this article.
If you run the BPA tool and experience the error, "Database Engine - Operating system files and drivers needs update for working set trimming," you need to review all the fixes discussed in the "Resolution" section of this article and apply them.
If you run the BPA tool and experience the error "Database Engine - Significant portion of SQL Server memory has been paged out," you need to review the "Resolution" and "Workaround" sections of this article and take appropriate action.
|Rule software||Rule title||Rule identifier|
|SQL Server 2008 R2 BPA Rule||SQL Server memory settings are incorrect||RID3804|
|SQL Server 2008 R2 BPA Rule||Operating system files and drivers needs update for working set trimming||RID0059|
|SQL Server 2008 R2 BPA Rule||Significant portion of SQL Server memory has been paged out||RID0049|
Improvements in Windows Server 2008 and in Windows Server 2008 R2
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:
http://download.microsoft.com/download/9/c/5/9c5b2167-8017-4bae-9fde-d599bac8184a/MemMgt.docxFor more information, visit the following Microsoft websites:
http://support.microsoft.com/kb/2001745Note 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.
Article ID: 918483 - Last Review: July 27, 2011 - Revision: 15.0
- Microsoft SQL Server 2005 Standard X64 Edition
- Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
- Microsoft SQL Server 2005 Enterprise X64 Edition
- Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
- Microsoft SQL Server 2008 Standard
- Microsoft SQL Server 2008 Enterprise
|kbsqlsetup kbsql2005engine kbexpertiseadvanced kbinfo KB918483|
- Brasil (Português)
- Česká republika (Čeština)
- Deutschland (Deutsch)
- España, Latinoamérica (Español)
- France (Français)
- Indonesia (Bahasa Indonesia)
- Italia (Italiano)
- Polska (Polski)
- Portugal (Português)
- Slovenská Republika (Slovenčina)
- Türkiye (Türkçe)
- Việt Nam (Tiếng Việt)
- Ελλάδα (Ελληνικά)
- Россия (Русский)
- भारत (हिंदी)
- ไทย (ไทย)
- 대한민국 (한국어)
- 中国 (简体中文)
- 台灣 (繁體中文)
- 日本 (日本語)