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.
.gif) 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.