Issue:
On Windows 2003 64bit running SQL Server 2005 Service Pack 2 (64bit) you find the following error recorded in the SQL Server log.
A significant part of the sql server process memory has been paged out. This may result in a performance degradation. Duration: xxx seconds
You may also have the following issues
- 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.
Cause:
By default SQL server will use all available memory, and dynamically release memory as required. A common cause of this problem occurs when SQL Server does not release memory because the server is under heavy load.
The typical scenario that causes this issue is when the Windows OS notifies all processes on the server that memory is running low. By default SQL server should release memory, but it is unable to process the request in a timely manner because of a heavy load on the SQL Server. When this happens the Windows OS is forced to require all processes to release memory. This will then force SQL Server to log the above events in the SQL Server Log, and page its entire buffer pool memory to disk. Since the request to release memory applies to all processes, all processes will be forced to release memory as well.
This issue is normally limited to 64bit Windows 2003 running SQL Server 2005, but may occur on 32bit Windows 2003 and SQL Server 2005 as well.
There may be other causes of this issue, please see the More Information section for details of those issues.
Solution:
Configure SQL minimum and maximum server memory settings to allow the OS to and other services enough RAM to function. To determine the amount of memory to reserve for the OS and other services use Performance Monitor to record the maximum working set size for each process. Total those values, and subtract that amount from the total physical RAM. Use this value as the maximum server memory setting for SQL Server. However, because the other processes can be memory starved these maximum values may not be adequate. Therefore, it may be necessary to repeat this process several times.
Configure SQL Server to use a minimum server memory of 25% to 50% of the maximum server memory setting. Configuring this value will help prevent all of the SQL Buffer Pool from being paged out should the request to release all buffer pool memory occur again.
Given that the SQL Server is under a heavy load, it may also be necessary to add more memory to your server. You should monitor performance of SQL server to determine if more memory is needed.
Verify that File and Printer Sharing for Microsoft Networks is configured for Maximize data throughput for network applications. This setting will help reduce the amount of system cache used by the by the OS, and is the recommended setting for SQL Server 2005.
More Information:
How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005
Omar Blog - A significant part of sql server process memory has been paged out
Slavao Blog - Memory Pressure
Slavao BLog - SQL memory manager: responding to memory pressure
Dynamic Memory Management and Memory Architecture of SQL 2005
By default Microsoft SQL Server 2005 dynamically acquires and frees memory as needed. It is typically not necessary for an administrator to specify how much memory should be allocated to SQL Server, although the option still exists and is required in some environments.
The default memory management behavior of the Microsoft SQL Server Database Engine is to acquire as much memory as it needs without creating a memory shortage on the system. The Database Engine does this by using Memory Notification functions of the Memory Management APIs in Microsoft Windows 2003.
Under Windows Server 2003, SQL Server uses the memory notification function QueryMemoryResourceNotification to determine when the buffer pool may allocate memory and release memory. This is essentially a dedicated thread that listens for one of two notification types: low-memory-resource or high-memory-resource. The default level of available memory that signals a low-memory-resource notification event is approximately 32 MB per 4 GB, to a maximum of 64 MB. The default level that signals a high-memory-resource notification event is three times the default low-memory value (96MB by default). As of Windows Server 2003 SP1, Windows does not balance memory across applications with the Memory Notification functions. It merely provides global feedback as to the availability of memory on the system.
When available memory becomes low the Windows OS will turn on the low-memory-resource notification. SQL Server and other applications that listen for this notification will be given the opportunity to shrink their memory usage before the Windows OS does it system wide. When the system is under heavy load SQL Server and other processes may not be able release memory in time. When this happens the OS will eventually intervene by paging (trimming) the working sets for all processes to disk.
Allowing SQL Server to use memory dynamically is recommended; however, you can set the memory options manually and restrict the amount of memory that SQL Server can access. Before you set the amount of memory for SQL Server, determine the appropriate memory setting by subtracting, from the total physical memory, the memory required for Windows Server 2003 and any other instances of SQL Server (and other system uses, if the computer is not wholly dedicated to SQL Server). This difference is the maximum amount of memory you should assign to SQL Server. However, because the other processes can be memory starved these maximum values may not be adequate. Therefore, it may be necessary to repeat this process several times.
Working Set Size Option
The set working set size option is still present in the sp_configure stored procedure, but its functionality is unavailable in Microsoft SQL Server 2005. (The setting has no effect.)