Home


Professional Services











Subscribe To This Site




Enable AWE on SQL Server

For those running SQL Server 2005 and SQL Server 2008 on a 32 bit infrastructure this tip talks you through enabling AWE on your SQL Server to allow your SQL Server to access more than the default amount RAM of 2GB.

This article assumes some knowledge of AWE and SQL Server memory management, if not please read this article on SQL Server 2005 memory management Enable AWE section first.

Using the GUI

You can enable AWE through the GUI in SQL Server Management Studio

Right click on the SQL Server instance and select �properties� Enable AWE through the GUI
On the properties screen, select the �Memory� tab. Under the �server memory options� section select the �use AWE to allocate memory� check box.

***It is important to remember to set the maximum amount of memory you want to allocate to SQL Server, ensuring that you leave enough memory available for the operating system. In this case my server has 10GB of RAM so I have allocated 8GB (8129MB) of RAM to SQL Server leaving 2GB for the OS.

Set the maximum server memory (in MB) box to the value chosen. enable awe using the gui
Click �OK�

Now you may think that is that and your done, but no, if you go back to the server properties box and view the memory tab, select the �Running values� checkbox you will see that SQL Server is not running with any of these values set.
The final step in this process is re-starting the SQL Server service. I assume if you are configuring these memory options then stopping and restarting the SQL Service is not something you need assistance with�But you need to stop and restart the SQL Service for the changes to take effect, you will get the following in the SQL Server log when the service restarts: "Address Windowing Extensions enabled."

Enable AWE with T-SQL

The following T-SQL script achieves the same result as the above GUI task:

/*SQL Sever 2005 and SQL Server 2008 code*/

USE master
GO
EXEC sys.sp_configure N'show advanced options', N'1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'8192'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'
RECONFIGURE WITH OVERRIDE
GO

/* After this has been run you need to restart the SQL Server*/


/*SQL Server 2000 code*/


USE master
GO
EXEC sp_configure N'show advanced options', N'1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure N'max server memory (MB)', N'8192'
GO
EXEC sp_configure N'awe enabled', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure N'show advanced options', N'0'
RECONFIGURE WITH OVERRIDE
GO
/* After this has been run you need to restart the SQL Server*/

With the advent of 64 bit technology, the need to enable AWE to allow SQL Server to access more than 2GB RAM has diminished but if you are still using the 32 bit versions of the technology then this could be useful to you.

***Words of caution

If you are enabling AWE on a SQL instance that shares its physical hardware with one or more instances you need to ensure that the max memory is set correctly to ensure that the other instances are not memory starved. For example, if you have a physical server with 16GB of RAM and two instances running on the box and you set AWE to on for both instances and set the max memory for each instance to 8GB, you have not left any RAM for the OS�which could cause you a problem. Make sure you when setting these values you take into account all instances running on the physical hardware. In this case I would probably allocate max memory to 7GB for both instances leaving 2GB for the OS

Enter your E-mail Address
Enter your First Name (optional)
Then

Don't worry -- your e-mail address is totally secure.
I promise to use it only to send you GRE Newsletter.


footer for Enable AWE page