awe enabled Option
In Microsoft SQL Server, you can use the Address Windowing Extensions (AWE) API to provide access to physical memory in excess of the limits set on configured virtual memory. The specific amount of memory you can use depends on hardware configuration and operating system support. The amount of physical memory supported has increased with the introduction of Microsoft Windows Server 2003 operating systems. The physical memory accessible by AWE therefore depends on which operating system you are using.
Windows Server 2003, Standard Edition supports physical memory up to 4 gigabytes (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.
AWE is not needed and cannot be configured on 64-bit operating systems.
Before enabling AWE, consider the following operating system-specific guidelines:
If the available physical memory is less than the configured limit set on virtual memory address space, AWE mapped memory cannot be activated. In this case, memory is dynamically allocated, and SQL Server runs in non-AWE mode, regardless of the setting of awe enabled.
If the available physical memory is greater than the accessible virtual memory, AWE mapped memory can be activated. In this case, the following two conditions apply:
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 128 MB.
AWE mapped memory is not released until SQL Server is shut down. Microsoft strongly recommends that each time you enable AWE, you set a value for the max server memory option, that takes into account the memory requirements of other applications running on the server.
Windows Server 2003
SQL Server supports dynamic allocation of AWE mapped memory on Windows Server 2003. AWE extends the capabilities of applications running on 32-bit operating systems by allowing access to available physical memory in excess of the limits set on their configured virtual memory address space.
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. This balancing between SQL Server and the operating system is subject to the constraints of the min server memory and max server memory parameters.
When running SQL Server with Windows Server 2003, SQL Server responds to changes in the max server memory and min server memory configuration options without requiring a restart of the SQL Server instance. For more information about min server memory and max server memory, see Server Memory Options.
Under Windows Server 2003, dynamic AWE mapped memory is supported on servers with less physical memory than the configured limit on virtual memory address space. There is no performance impact when using AWE under this condition, but when physical memory is added over the virtual memory address limit, it can be used without rebooting the server.
For both Windows 2000 and Windows Server 2003, AWE memory cannot be swapped out to the page files. Therefore, you should determine how much 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.
Use the SQL Server Total Server Memory (KB) counter in System Monitor to determine how much memory is allocated by the instance of SQL Server running in AWE mode. Configure the max server memory option to leave some additional memory free for the needs of other applications, Windows 2000 and Windows Server 2003. For more information, see Monitoring Memory Usage.
Using awe enabled and max server memory can affect the performance of other applications or SQL Server running in a multi-instance or cluster environment. For more information about using AWE mapped memory, see Managing Memory for Large Databases.
To enable AWE, set awe enabled to 1. For Windows 2000, unless a value has been specified for max server memory, SQL Server reserves almost all available memory during startup, leaving 128 megabytes (MB) or less physical memory for other applications. (When AWE is not enabled the amount of physical memory left for other applications can be controlled by max server memory.) For Windows Server 2003, the AWE mapped memory management is dynamic, so that only a small portion of the total available physical memory is allocated during startup.
If the option has been successfully enabled, the message "Address Windowing Extensions enabled" is written to the SQL Server error log when the instance of SQL Server is started.
The LOCK PAGE IN MEMORY permission must be granted to the SQL Server account before enabling AWE. For more information, see How to: Enable the Lock Pages in Memory Option (Windows).
The awe enabled option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change awe enabled only when show advanced options is set to 1. You must restart the instance of SQL Server for AWE to take effect.
The following example shows how to enable AWE and to configure the min server memory to 1 GB and the max server memory to 6 GB:
First, enable AWE:
sp_configure 'show advanced options', 1 RECONFIGURE GO sp_configure 'awe enabled', 1 RECONFIGURE GO
After SQL Server restarts, the following message should appear in 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 the preceding example for SQL Server and Windows Server 2003, the memory settings direct the buffer pool to dynamically manage AWE mapped memory between 1 GB and 6 GB. (This dynamic AWE example does not apply to SQL Server 2000.) If other applications require additional memory, SQL Server can release the allocated AWE mapped memory if it is not needed. In the example, the AWE mapped memory can only be released up to 1 GB, the min server memory limit.
Note that setting the min server memory option to 1 GB does not automatically force SQL Server to acquire 1 GB of memory. Memory will be allocated on demand, based on current database server load.
To disable AWE, set awe enabled to 0 and execute the RECONFIGURE statement. AWE memory is disabled by default. The SQL Server error log records the change to the awe enabled option. After reboot, SQL Server operates in a normal dynamic memory allocation mode, and the available memory is limited to virtual address space or physical memory, whichever is smaller.