How to increase server memory used in SQL Server 2000
I'm using SQL Server 2000 Enterprise Edition and finding the total server memory is 1670976. The target server memory is also 1670976. My question is, if the server has 3GB of RAM, why is it that this value doesn't change much when the server is under I/O or CPU pressure? Why does it seem like the amount of memory used stays constant?
The target server memory is the maximum amount of memory that the server is configured to use. The total server memory is the amount of memory the server is using. Based on the numbers you've provided, your server is set up to use about 1.6 Gigs of memory. In order to increase this closer to the 3 Gigs of memory you have installed, you will want to make sure sp_configure shows SQL Server is set up to use more than 2 Gigs of memory. You'll also need to enable AWE to go past 2 Gigs of memory.
In addition, your operating system will need to be Windows 2000 Enterprise Edition or Windows 2003 Standard or Enterprise edition. If your OS is one of these, enable PAE using the /PAE switch in the boot.ini file. Changes to the boot.ini file will require that the server be rebooted to take effect. You should read Microsoft article, How to edit the Boot.ini file in Windows XP before editing the boot.ini file.
This was first published in December 2007