Microsoft SQL Server 2005 performs dynamic memory management based on the memory requirements of the current load and activities on the system. On a Windows Server 2003-based system, SQL Server 2005 can use the memory notification mechanisms that are provided by the QueryMemoryResourceNotification Windows API. On a Microsoft Windows 2000 Server-based system, SQL Server 2005 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 2005 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 2005 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 2005 Books Online.

Back to the top

In 64-bit editions of SQL Server 2005, various problems may occur. For example, the following problems may occur:

•	The performance of SQL Server 2005 decreases suddenly.
•	SQL Server 2005 stops responding for a short time.
•	A time-out occurs for applications that connect to SQL Server 2005.
•	Problems occur when you run even simple commands or use applications on the system.

If you installed SQL Server 2005 Service Pack 2 (SP2) or a later version, one of the following error messages is logged in the SQL Server error log when these problems occur:

Back to the top

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 2005 process reaches 50 percent of the memory that is committed for the SQL Server 2005 process. Therefore, you can use these error messages to determine the case in which SQL Server 2005 performance decreases significantly because the Windows operating system pages out the working set of the SQL Server 2005 process. Additionally, these error messages are logged every 5 minutes in the first 30 minutes. After the first 30 minutes, the frequency of these error messages doubles until the frequency reaches the maximum of 1 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.

Back to the top

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

If you still encounter 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 2005, only SQL Server 2005 Enterprise Edition can use the Lock pages in memory user right.

To assign the Lock pages in memory user right, follow these steps:

1.	Click Start, click Run, type gpedit.msc, and then click OK. 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.

After you assign the Lock pages in memory user right and 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.

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. This behavior occurs because the working set of a SQL Server 2005 process typically does not reach 50 percent of the committed memory.

Back to the top

How to troubleshoot this problem

To troubleshoot the problem that occurs because the Windows operating system pages out the working set of the SQL Server process, follow these steps:

1.	Apply the hotfixes that are described in the following Microsoft Knowledge Base articles. Therefore, you can resolve the known issues that cause the Windows operating system to page out the working set of the SQL Server 2005 process. Note Hotfixes are cumulative. A later version of a hotfix contains the hotfixes that have versions that are earlier. • Microsoft has found an issue that may cause the 64-bit edition of the SQL Server 2005 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 2005 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"
2.	If a device driver uses the MmAllocateContiguousMemory function and 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 2005 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 the hotfixes, apply a Windows hotfix that limits the number of trim operation per process. For more information, click the following article number to view the article 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 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 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 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 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.
5.	If SQL Server 2005 is running together with SAP/R3, 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

Windows Server 2008 improves the contiguous memory allocation mechanism. The improvement lets Windows Server 2008 reduce the side effects of paging out the working set of applications when new memory requests arrive. If you are running SQL Server 2005 on a Windows Server 2008-based computer, you do not have to manually configure the max server memory property as specified in the "Important considerations before you assign the 'Lock pages in memory' user right for an instance of a 64-bit edition of SQL Server 2005" section.

Note On a Windows Server 2008-based computer, you must install SQL Server 2005 Service Pack 2 (SP2) or a later SQL Server 2005 service pack.

Back to the top

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

You can use Performance Monitor to view the memory page that is used by 64-bit editions of SQL Server 2005. To do this, monitor the following performance counter: 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: Additionally, you can measure the effect of paging on 64-bit editions of SQL Server 2005 by monitoring the following performance counters: The 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 2005 also use the following performance counter to expose the memory that the buffer pool allocates: Note If the instance of the 64-bit edition of SQL Server 2005 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 memory that is a part of the buffer pool has been trimmed from the SQL Server working set.

After you assign the Lock pages in memory user right and restart the SQL Server service, the buffer pool of the SQL Server process still responds to memory resource notification events and dynamically grows or reduces 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.

After these pages are locked, these performance counters represent the memory allocations inside the SQL Server 2005 process when those allocations do not use the buffer pool. The Total Server Memory(KB) counter of the SQL Server:Memory Manager performance object accurately represents the memory that is allocated for the buffer pool.

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 2005

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 2005 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 2005. 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 • 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, and linked servers. 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 2005 Books Online.
•	Determine the memory requirement of other applications and of the Windows operating system components. Applications may include other SQL Server 2005 components, for example, SQL Server Agent, SQL Server 2005 Reporting Services, SQL Server 2005 Analysis Services, SQL Server 2005 Integration Services, and SQL Server 2005 Full Text Search. Applications that perform backup operations and file copy operations may use lots of memory. 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 an individual 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 2005 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.

Back to the top

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

You can use information from SQL Server ring buffers to determine the memory conditions on the server when paging occurs. You can use a script as follows to obtain this information.

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

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.

Back to the top