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

• 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.

Back to the top

• 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.

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. 

Back to the top

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 Setting dialog 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.

Back to the top

How to troubleshoot this problem

1. 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.
   • Microsoft has found an issue that may cause the 64-bit edition of the SQL Server working set to be trimmed. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
     905865  (http://support.microsoft.com/kb/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  (http://support.microsoft.com/kb/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 S or in Windows Server 2003 S
   • The SQL Server working 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  (http://support.microsoft.com/kb/942861/ ) Error message when an application connects to SQL Server on a server that is running Windows Server 2003: "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  (http://support.microsoft.com/kb/942486/ ) The System process may consume most of the available system memory on a multiprocessor computer that is running Windows Server 2003
2. 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 64-bit SQL Server process. To resolve this problem, contact the vendor of the device driver for driver updates.
3. If you still encounter these problems after you apply these hotfixes, apply a Windows hotfix that limits the number of trim operations per process. For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
   938486  (http://support.microsoft.com/kb/938486/ ) A Windows Server 2003-based computer becomes unresponsive because of a memory manager trimming operation that is caused by an indeterminate module that requests lots of memory
   956341  (http://support.microsoft.com/kb/956341/ ) A Windows Server 2003-based computer becomes unresponsive because of a large memory allocation request
   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.
4. Applications may 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 Web sites:
   http://blogs.msdn.com/ntdebugging/archive/2007/11/27/too-much-cache.aspx (http://blogs.msdn.com/ntdebugging/archive/2007/11/27/too-much-cache.aspx)
   
   http://blogs.msdn.com/ntdebugging/archive/2009/02/06/microsoft-windows-dynamic-cache-service.aspx (http://blogs.msdn.com/ntdebugging/archive/2009/02/06/microsoft-windows-dynamic-cache-service.aspx)
   You can download and deploy the "Microsoft Windows Dynamic Cache Service" to control the memory that is consumed by the system cache.
5. 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  (http://support.microsoft.com/kb/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

Back to the top

Improvements in Windows Server 2008

Back to the top

How to determine the memory that is used by 64-bit editions of SQL Server

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

Back to the top

Important considerations before you assign the "Lock pages in memory" user right for an instance of a 64-bit edition of SQL Server

• 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
  • The Multipage Allocator of SQL Server Memory Manager
  • 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:
  http://blogs.msdn.com/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx (http://blogs.msdn.com/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx)
• 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.

Back to the top

How to use the information from SQL Server ring buffers to determine the memory conditions when paging occurs

SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime,
DATEADD (ms, -1 * ((sys.cpu_ticks / sys.cpu_ticks_in_ms) - a.[Record Time]), GETDATE()) AS Notification_time,  
 a.* , sys.ms_ticks AS [Current Time]
 FROM 
 (SELECT x.value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type], 
 x.value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %], 
 x.value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB], 
 x.value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB], 
 x.value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB], 
 x.value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB], 
 x.value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB], 
 x.value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB], 
 x.value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id], 
 x.value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB], 
 x.value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB], 
 x.value('(//Record/@id)[1]', 'bigint') AS [Record Id], 
 x.value('(//Record/@type)[1]', 'varchar(30)') AS [Type], 
 x.value('(//Record/ResourceMonitor/Indicators)[1]', 'bigint') AS [Indicators], 
 x.value('(//Record/@time)[1]', 'bigint') AS [Record Time]
 FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers 
 WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS R(x)) a 
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY a.[Record Time] ASC

Back to the top