Enable AWE memory for SQL Server

This function will be deleted in the next version of Microsoft SQL Server. Do not use this function in new development work, and modify the application that is currently using this function as soon as possible.

Address Window Extensions (AWE) Allow 32-bit operating systems to access a large amount of memory. AWE is made public by the operating system, and its implementation methods in Microsoft Windows 2000 Server and Windows Server 2003 are slightly different. AWE is enabled with the awe enabled option.

Use AWE ing memory in Windows 2000 Server

The Microsoft SQL Server instance running on Windows 2000 allocates memory according to the following sequence of events during SQL Server startup:

AWE cannot be activated if the available physical memory is smaller than the virtual address space in user mode. In this case, SQL Server runs in non-AWE mode regardless of the settings of the awe enabled option.

If the available physical memory is larger than the virtual address space in user mode, 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 specified memory volume 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 is not set, the SQL Server instance will only leave 256 MB, and lock all other available memory.

Once allocated, the AWE ing memory cannot be released unless SQL Server is disabled.

Configure memory options

We strongly recommend that you set a value for max server memory each time you enable AWE. If awe enabled is set to 1 and the available physical memory is greater than the user mode process space), after the server is started, the SQL Server instance running on Windows 2000 locks almost all available memory or the memory specified by the max server memory value. If this option is set ). If max server memory is not set, the available physical memory of other applications or SQL Server instances will be less than 128 MB.

The AWE ing Memory Pool cannot be swapped out to paging system files. If you need to use additional physical memory, Windows must switch out other applications, which may affect the performance of those applications.

If you do not want to affect the performance of other applications, configure max server memory to set aside additional available memory for the occasional needs of other applications and operating systems. You can determine the amount of memory that can be safely allocated to the SQL Server instance by learning how much memory is available after all other applications on the computer start.

Note:

In Windows 2000 Server, SQL Server AWE ignores min server memory.

Use the SQL Server performance monitor Total Server Memory (KB) counter to determine the amount of Memory allocated to the SQL Server instance running in AWE mode, you can also select the memory usage from sysperfinfo to determine the amount of memory allocated by the instance.

For more information, see monitor memory usage.

Use AWE to run multiple SQL Server instances

If the server runs Windows 2000, each instance should have the max server memory settings. Since SQL Server running on Windows 2000 does not support dynamic allocation of AWE ing memory, we recommend that you set the max server memory option for each instance.

The total max server memory value of all instances should be less than the total physical memory of the computer. If the sum is greater than the total physical memory, some instances may not be started, or the memory used is smaller than the memory size specified by the max server memory settings. For example, assume that a computer has 16 GB of physical memory and three SQL Server instances are installed on the computer. In addition, the max server memory of each instance is set to 8 GB. If you stop and restart all three instances, the memory allocation is as follows:

The first instance uses 8 GB physical memory.

The second instance will be started, but the physical memory used will be slightly less than 8 GB, which can be reduced by up to 128 MB ).

The third instance starts in dynamic memory mode and uses 256 MB physical memory or less physical memory.

For more information, see manage memory for large databases.

Use AWE ing memory in Windows Server 2003

In Windows Server 2003, SQL Server supports dynamic allocation of AWE memory. During startup, SQL Server only retains a small part of AWE ing memory. When an additional AWE ing memory is required, the operating system dynamically allocates it to SQL Server. Similarly, if less resources are required, SQL Server can return the AWE ing memory to the operating system for other processes or applications. For more information about awe enabled configuration options, see awe enabled options.

Windows Server 2003 series supports an increase in the amount of physical memory. The physical memory available for AWE depends on the operating system you are using. The following list lists the maximum physical memory that can be used by each Windows Server 2003 operating system during write operations.

Windows Server 2003 Standard Edition supports up to 4 GB of physical memory.

Windows Server 2003 Enterprise Edition supports up to 32 GB physical memory.

Windows Server 2003 Datacenter Edition supports up to 64 GB physical memory.

Configure memory options

When running SQL Server on any Windows Server 2003 operating system version, AWE ing memory is dynamically allocated. In other words, the buffer pool can dynamically manage the constraints of min server memory and max server memory options in the AWE ing to balance the use of SQL Server memory according to the overall system requirements.

After AWE is enabled, SQL Server always tries to use the AWE ing memory. This applies to all memory configurations, including configurations to computers that are provided to an application in address space less than 3 GB.

We recommend that you set AWE to the default memory mode of SQL Server running on Windows Server 2003. The hot memory addition function requires that AWE be enabled during SQL Server startup. For more information, see add hot memory.

Note:

You do not need to configure AWE on a 64-bit operating system.

Because the supported AWE ing memory capacity is less than 3 GB, you can define min server memory and max server memory values within the physical memory range, or use the default values of these two options.

You can consider setting the max Server memory of SQL server to ensure that other memory can be used for other applications running on the computer. Although SQL Server can dynamically release the AWE ing memory, the amount of allocated AWE ing memory cannot be swapped out of page files.

To enable the SQL Server instance to use AWE, use sp_configure to set the awe enabled option to 1 and 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 Memory Page" policy. For more information, see How to enable the "Lock Memory Page" option (Windows ).

Example

The following example shows how to activate AWE and configure a 1 GB limit for min server memory and a 6 GB limit for max server memory.

First, configure AWE:

 
 

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

After you restart SQL Server, the following messages are written to the SQL Server Error Log: "address window extension enabled ."

Then, configure the memory:

 
 

  
  
sp_configure 'min server memory', 1024  
  
  
RECONFIGURE  
  
  
GO  
  
  
 
  
  
sp_configure 'max server memory', 6144  
  
  
RECONFIGURE  
  
  
GO 
 
 

In this example, the memory sets the boot buffer pool to dynamically manage the AWE ing memory between 1 GB and 6 GB. If other applications require additional memory, SQL Server can release allocated AWE ing memory that is no longer needed. In this example, the AWE ing memory can be released at most 1 GB.

If you add additional memory to a computer that supports hot memory addition, SQL Server can also use dynamic AWE memory to increase the memory. Windows Server 2003 Enterprise Edition and Datacenter Edition provide hot memory addition, allowing you to increase the memory when your computer is running. For example, assume that the SQL Server running on Windows Server 2003 Enterprise Edition is started on a computer with 16 GB physical memory. Configure the operating system to restrict applications from using 2 GB of virtual memory address space, and activate AWE on SQL Server. Later, the system administrator needs to add 16 GB of memory when the computer is running. SQL Server immediately recognizes the added memory and can use it if necessary.

For more information about how to use AWE, see the Windows Server 2003 documentation.

Use AWE to run multiple SQL Server instances

If you run multiple SQL Server instances on the same computer and each instance uses AWE ing memory, ensure that these instances are executed as expected.

If the Server runs Windows server 2003, set min Server memory for each instance. Since SQL Server running on Windows Server 2003 supports dynamic AWE ing memory management, we recommend that you set the min server memory option for each instance. As the AWE ing memory cannot be used to swap out page files, the total min server memory values of all instances should be less than the total physical memory on the computer.

At startup, the min server memory option does not force SQL Server to obtain the minimum amount of memory. Allocate memory on demand based on database workload. However, after the min server memory threshold is reached, if the memory reserved by SQL Server for itself is smaller than this amount, SQL Server will not release the memory. Therefore, to ensure that the memory allocated to each instance is at least equal to the min server memory value, we recommend that you load the database server immediately after startup. When the server runs normally, the available memory of each instance changes at any time, but it is never smaller than the min server memory value.

You can set max server memory or retain this option as the default setting. Keeping max server memory as the default value causes the SQL Server instance to compete for memory.

Use AWE and Failover clusters together

If the SQL Server failover cluster and AWE memory are used, make sure that the sum of max server memory settings for all instances is smaller than the minimum physical memory available on any Server in the Failover cluster. If the physical memory of the Failover node is smaller than the memory on the original node, the SQL Server instance may fail to start, or it may start when the memory is smaller than the memory on the original node.

Original article address

View more articles

Edit recommendations]