SQL Server 2000 may overcommit physical memory on computers that are running Windows XP (884593)

SYMPTOMS

Microsoft SQL Server 2000 may commit more memory than is physically available on a computer that is running Microsoft Windows XP. This problem only occurs for instances of SQL Server 2000 Service Pack 3 (SP3) or later versions. This problem may cause paging on the computer and lead to poor or slow performance for all applications.

CAUSE

This problem occurs because of an issue with the implementation of memory notification APIs in Windows XP. For additional information about this issue, click the following article number to view the article in the Microsoft Knowledge Base:

884034 FIX: An application that is running on a Windows XP-based computer is not notified of memory resource notification events

RESOLUTION

To resolve this problem, upgrade to Windows XP SP2. For additional information about how to obtain Windows XP SP2, click the following article number to view the article in the Microsoft Knowledge Base:

322389 How to obtain the latest Windows XP service pack

You can also use trace flag 822 to work around this problem until you can upgrade to Windows XP SP2. Do not use trace flag 822 as a permanent solution to this problem.

MORE INFORMATION

Instances of SQL Server 2000 SP3 rely on the QueryMemoryResourceNotification Windows API to dynamically adjust memory consumption to avoid overcommitting physical memory. Problems occur with this API on computers that are running Windows XP SP2 or earlier versions. SQL Server may not be notified of pressure on the physical memory on the computer. Therefore, SQL Server may continue to increase its memory consumption on the computer until all virtual memory is exhausted. The virtual memory includes both the physical memory and the limits of the paging file.

This API is only used on computers that are running Windows XP and Windows Server 2003. However, problems do not occur with this API on computers that are running Windows Server 2003. SQL Server does not use this API on computers that are running Windows 2000.