Managing AWE Memory

Microsoft® SQL Server™ 2000 uses the Microsoft Windows® 2000 Address Windowing Extensions (AWE) API to support very large memory sizes. SQL Server 2000 can use as much memory as Windows 2000 Advanced Server or Windows 2000 Datacenter Server allows. For more information about the AWE API, search on "awe memory" in the MSDN® Online Microsoft Web site.

Note This feature is available only in the SQL Server 2000 Enterprise and Developer editions.

Using AWE Memory

To use AWE memory, you must run the SQL Server 2000 database engine under a Windows 2000 account that has been assigned the Windows 2000 lock pages in memory privilege.

SQL Server Setup will automatically grant the MSSQLServer service account permission to use the Lock Page in Memory option. If you are starting an instance of SQL Server 2000 from the command prompt using sqlservr.exe, you must manually assign this permission to the interactive user's account using the Windows 2000 Group Policy utility (gpedit.msc), or SQL Server will be unable to use AWE memory when not running as a service.

To enable the Lock Page in Memory option

Windows

To enable the use of AWE memory by an instance of SQL Server 2000, set the sp_configure option awe enabled. Then restart SQL Server to activate AWE. Because the AWE option is enabled during SQL Server startup and lasts until SQL Server shutdown, SQL Server will notify users when awe enabled is in use by sending an "Address Windowing Extension enabled" message to the SQL Server error log. For more information about the awe enabled configuration option, see awe enabled Option.

For more information about using AWE memory, see the Windows 2000 documentation.

AWE Memory and System Resources

Instances of SQL Server 2000 do not dynamically manage the size of the address space when you enable AWE memory. Therefore, when you enable AWE memory and start an instance of SQL Server 2000, one of the following occurs:

If sp_configure max server memory has been set and there are at least 3 gigabytes (GB) of free memory available on the computer, the instance acquires the amount of memory specified in max server memory. If the amount of memory available on the computer is less than max server memory (but more than 3 GB), then the instance acquires almost all of the available memory and may leave only up to 128 megabytes (MB) of memory free.
If max server memory has not been set and there is at least 3 GB of free memory available on the computer, then the instance acquires almost all of the available memory and may leave only up to 128 MB of memory free.
If there is less than 3 GB of free memory available on the computer, memory is dynamically allocated and, regardless of the parameter setting for awe enabled, SQL Server will run in non-AWE mode.

Evaluating Memory Usage

The memory pool of an instance of SQL Server 2000 using AWE cannot be swapped out to the page file. Windows 2000 has to swap out other applications if it needs to use additional physical memory, which may hinder the performance of the other applications.

Therefore, you must ensure that there is enough memory outside of the amount used by all instances of SQL Server to satisfy the virtual memory needs of other applications running on the computer.

Use System Monitor (Performance Monitor in Microsoft Windows NT® 4.0) to retrieve information on SQL Server memory usage and available memory. Task Manager does not provide accurate memory usage information for AWE. Therefore, the memory quoted for sqlservr.exe is not correct. To obtain the correct amount of SQL Server memory usage, you can use the Total Server Memory (KB) performance counter, activated through System Monitor, or select the memory usage from sysperfinfo. For more information, see Monitoring Memory Usage.

Running Multiple Instances

If you are running multiple instances of SQL Server 2000 on the same computer, and each instance uses AWE memory, you must ensure the following:

Each instance has a max server memory setting.
The sum of the max server memory values for all the instances is less than the amount of physical memory in the computer.
If the sum of the settings exceeds the physical memory on the computer, some of the instances either will not start or will have less memory than is specified in max server memory. For example, suppose a computer has 16 GB of physical RAM and has three instances of SQL Server 2000 running on it. Furthermore, max server memory is set to 8 GB for each instance. If you stop and restart all three instances:
- The first instance will start with the full amount of 8 GB of memory.
- The second instance will start, but with slightly less than 8 GB of memory (up to 128 MB less).
- The third instance will start in dynamic memory mode and will have 128 MB or less memory available to it.

Windows 2000 Usage Considerations

Before you configure Windows 2000 for AWE memory, consider the following:

To enable Windows 2000 Advanced Server or Windows 2000 Datacenter Server to support more than 4 GB of physical memory, you must add the /pae parameter to the boot.ini file.
To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server to support a 3-GB virtual address space, you must add the /3gb parameter to the boot.ini file. This allows user applications to address 3 GB of virtual memory and reserves 1 GB of virtual memory for the operating system.
However, if there is more than 16 GB of physical memory available on a computer, Windows 2000 needs 2 GB of virtual memory address space for system purposes and therefore can support only a 2-GB virtual address space.

In order to allow AWE to use the memory range above 16 GB, be sure the /3gb parameter is not in the boot.ini file. If it is, Windows 2000 will be unable to address any memory above 16 GB. When allocating SQL Server AWE memory on a 32-GB system, Windows 2000 may require at least 1 GB of available memory to manage AWE. Therefore, when starting an instance of SQL Server with AWE enabled, it is recommend you do not use the default max server memory setting, but instead limit it to 31 GB or less.

For more information, see Using AWE Memory on Windows 2000.

Using Failover Clustering

If you are using SQL Server 2000 failover clustering and AWE memory, you must ensure that the summed value of the max server memory settings for all the instances is less than the lowest amount of physical RAM available on any of the servers in the failover cluster. If the failover node has less physical memory than the original node, the instances of SQL Server 2000 may fail to start or may start with less memory than they had on the original node.