SQL Server 2000 and AWE Memory

In a previous post, I described what AWE memory is, and how it works with SQL Server 2005, against both Windows 2000 Server and Windows 2003 Server.  In this post, I’ll describe how it works in SQL Server 2000.

Firstly, run

SELECT @@version

to check what version of SQL Server you are running.  This will give you a version number, and the edition. 

The first thing to be aware of is that SQL Server 2000 will only use more than 2 GB of memory with Enterprise (or Developer) Edition.  This is the main limitation of SQL Server 2000 Standard, and if more than 2 GB of memory is required, you must upgrade to SQL Server 2000 Enterprise, or SQL Server 2005 Standard.

If you are running 8.00.2039 (Service Pack 4), you will also need to upgrade to 8.00.2040 to avoid a bug where only 50% of the available memory is available for use.  See http://support.microsoft.com/kb/899761/ for more information.

Enabling AWE on SQL Server 2000 is the same as SQL Server 2005:

1. Add the /pae switch to boot.ini to allow Windows 2000/2003 to access more than 4 GB of memory
2. Grant “Lock Pages in Memory” to the user account that runs the SQL Server process
3. Set the configuration setting “AWE Enabled” to 1.

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO

Note that AWE requires 2 GB of kernel VAS to address AWE memory above 16 GB, so using the /3gb switch in boot.ini will limit AWE to 16 GB.  See http://support.microsoft.com/kb/274750 for more information about the maximum memory useable for each operating system/SQL Server 2000 combination.

SQL Server 2000 will allocate all memory upon startup, to the value of the “Max Server Memory” configuration option.  If there is not enough memory, SQL Server will take as much as it can, leaving as little as 128 MB available.  This memory comes from the Windows non-pageable pool, and so this memory will never be swapped to disk.

If the server has less than 3 GB of memory available when SQL Server starts, AWE will not be available, and SQL Server will run in non-AWE mode.

It is always recommended to set a value for “Max Server Memory” to ensure that other processes running on the server have some memory available.  Leaving ~2 GB is a good setting, for under 32 GB total server memory, ~4 GB if 64 GB total server memory.  Additionally, when multiple instances of SQL Server are running on the one machine, “Max Server Memory” is necessary to prevent one instance from using too much memory and starving the other.  If “Max Server Memory” has not been set, the first instance of SQL Server 2000 starting will allocate all bar 128 MB, and leave no memory for the second instance.