Enabling AWE Memory for SQL Server
Address Windowing Extensions (AWE) allow 32-bit operating systems to access large amounts of memory. AWE is exposed by the operating system, and is implemented with slight differences in Microsoft Windows 2000 Server and Windows Server 2003. AWE is enabled using the awe enabled Option.
Using AWE-mapped Memory with Windows 2000 Server
Instances of Microsoft SQL Server running on Windows 2000 allocate memory during SQL Server startup according to the following sequence of events:
If the available physical memory is less than the user mode virtual address space, AWE cannot be activated. In this case, SQL Server runs in non-AWE mode, regardless of the setting of the awe enabled option.
If the available physical memory is greater than the user mode virtual address space, AWE can be activated.
If the available physical memory is greater than the value of the max server memory option, the SQL Server instance locks the amount of memory specified in max server memory.
If the available physical memory is less than the value of the max server memory option or if the max server memory option has not been set, the SQL Server instance locks all available memory except 256 megabytes (MB).
Once allocated, AWE-mapped memory cannot be released until SQL Server shuts down.
Configuring Memory Options
We strongly recommend setting a value for max server memory each time you enable AWE. When awe enabled is set to 1 (and the available physical memory is greater than the user mode process space), instances of SQL Server that run on Windows 2000 lock almost all available memory (or the value of max server memory if the option has been set) when the server is started. If max server memory is not set, other applications or instances of SQL Server has less than 128 MB of physical memory available.
The AWE-mapped memory pool cannot be swapped out to the paging system files. Windows has to swap out other applications if additional physical memory is needed for use, which may hinder the performance of these other applications.
To avoid hurting the performance of other applications, configure max server memory to leave some additional memory free to allow for the varying needs of other applications and the operating system. You can determine the amount of memory you can safely allocate to instances of SQL Server by identifying how much memory is available after all other applications to be used on the computer have been started.
Note
For Windows 2000 Server, min server memory is ignored for SQL Server AWE.
Use the SQL Server Performance Monitor Total Server Memory (KB) counter to determine how much memory is allocated by the instance of SQL Server running in AWE mode, or select memory usage from sysperfinfo.
For more information, see Monitoring Memory Usage.
Running Multiple Instances of SQL Server with AWE
If your server is running Windows 2000, each instance should have a max server memory setting. Because SQL Server running on Windows 2000 operating systems does not support dynamic allocation for AWE-mapped memory, we recommend that the max server memory option be set for each instance.
The sum of the max server memory values for all instances should be less than the total physical memory on the computer. If the sum is greater than the total physical memory, some of the instances do not start or have access to less memory than specified by the max server memory setting. For example, suppose a computer has 16 gigabytes (GB) of physical memory and three installed instances of SQL Server. Also, max server memory is set to 8 GB for each instance. If you stop and restart all three instances, memory allocation is as follows:
The first instance has access to 8 GB of physical memory.
The second instance starts, but has access to slightly less than 8 GB of physical memory (up to 128 MB less).
The third instance starts in dynamic memory mode and has access to 256 MB or less physical memory.
For more information, see Managing Memory for Large Databases.
Using AWE-mapped Memory with Windows Server 2003
SQL Server supports dynamic allocation of AWE memory on Windows Server 2003. During startup, SQL Server reserves only a small portion of AWE-mapped memory. As additional AWE-mapped memory is required, the operating system dynamically allocates it to SQL Server. Similarly, if fewer resources are required, SQL Server can return AWE-mapped memory to the operating system for use by other processes or applications. For more information about the awe enabled configuration option, see awe enabled Option.
The amount of physical memory supported has increased with the introduction of the Windows Server 2003 family. The physical memory accessible by AWE depends on which operating system you are using. The following list provides the maximum physical memory accessible by each Windows Server 2003 operating system at the time of writing.
Windows Server 2003, Standard Edition supports physical memory up to 4 GB.
Windows Server 2003, Enterprise Edition supports physical memory up to 32 GB.
Windows Server 2003, Datacenter Edition supports physical memory up to 64 GB.
Configuring Memory Options
SQL Server dynamically allocates AWE-mapped memory when running with any of the Windows Server 2003 operating system editions. In other words, the buffer pool can dynamically manage AWE-mapped memory (within the constraints of the min server memory and max server memory options) to balance SQL Server memory use with the overall system requirements.
When AWE is enabled, SQL Server always attempts to use AWE-mapped memory. This applies to all memory configurations, including computers configured to provide applications with less than 3 GB of user mode address space.
- We recommend setting AWE as the default memory mode for SQL Server running under Windows Server 2003. The Hot-Add Memory feature requires AWE to be enabled during SQL Server startup. For information, see Hot Add Memory.
Note
AWE is not required and cannot be configured on 64-bit operating systems.
Since AWE-mapped memory is supported below 3 GB, you can define the min server memory and max server memory values within the physical memory range, or use the default values for both options.
You may consider setting max server memory for SQL Server to guarantee additional memory for other applications operating on the computer. Although SQL Server can dynamically release AWE-mapped memory, the current amount of allocated AWE-mapped memory cannot be swapped out to the page file.
To make AWE available to an instance of SQL Server, use sp_configure to set the awe enabled option to 1, and then restart SQL Server.
For more information about min server memory and max server memory, see Server Memory Options.
Before enabling AWE, you must configure the Lock Pages in Memory policy. For more information, see How to: Enable the Lock Pages in Memory Option (Windows).
Example
The following example shows how to activate AWE and configure a limit of 1 GB for min server memory and 6 GB for max server memory.
First, configure AWE:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
After SQL Server restarts, the following message is written to the SQL Server error log: "Address Windowing Extensions enabled."
Next, configure memory:
sp_configure 'min server memory', 1024
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
In this example, the memory settings direct the buffer pool to dynamically manage AWE-mapped memory between 1 GB and 6 GB. If other applications require additional memory, SQL Server can release the allocated AWE-mapped memory if it is not needed. In this example, the AWE-mapped memory can only be released up to 1 GB.
Dynamic AWE memory also allows SQL Server to increase memory if additional memory is added to a computer that supports Hot Add Memory. Available in Windows Server 2003 3, Enterprise and Datacenter editions, Hot Add Memory allows memory to be added while the computer is running. For example, suppose SQL Server, running under Windows Server 2003, Enterprise Edition is started on a computer with 16 GB of physical memory. The operating system is configured to limit applications to 2 GB of virtual memory address space; AWE has been activated on SQL Server. Later, the system administrator adds 16 GB of memory while the computer is running. SQL Server immediately recognizes the additional memory, and, if necessary, can take advantage of it.
For more information about using AWE, see the Windows Server 2003 documentation.
Running Multiple Instances of SQL Server with AWE
If you are running multiple instances of SQL Server on the same computer, and each instance uses AWE mapped memory, you should ensure that the instances perform as expected.
If your server is running Windows Server 2003, each instance should have a min server memory setting. Since SQL Server running on Windows Server 2003 supports dynamic AWE-mapped memory management, we recommend setting the min server memory option for each instance. Since AWE-mapped memory cannot be swapped out to the page file, the sum of the **min server memory **values for all instances should be less than the total physical memory on the computer.
The min server memory option does not force SQL Server to acquire a minimum amount of memory at startup. Memory is allocated on demand based on the database workload. However, once the min server memory threshold is reached, memory is not released by SQL Server if it would leave SQL Server with less than that amount. To ensure that each instance has allocated memory at least equal to the min server memory value, therefore, we recommend that you execute a database server load shortly after startup. During normal server activity the memory available per instance varies, but there is never less than the min server memory value available for each instance.
You can set max server memory or leave the option set to the default setting. Leaving max server memory set to the default results in SQL Server instances competing for memory.
Using Failover Clustering with AWE
If you are using SQL Server failover clustering and AWE memory, you must ensure that the sum of the max server memory settings for all the instances is less than the least physical memory 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 may fail to start or may start with less memory than they had on the original node.