Introduction

Recently, 4GB of physical RAM was added to a SQL Server 2000 Enterprise edition instance I support. This brought the total physical RAM available on the machine up to 8GB. By using Windows 2000 Address Windowing Extensions (AWE), with SQL Server 2000 Enterprise or Developer Edition, on Windows 2000 Advanced Server or Windows 2000 Data Center, SQL Server can take advantage of physical memory exceeding 4GB of physical RAM. 

Although I had read a few articles about the AWE configuration process, this was my first time I had ever actually enabled this feature. After I completed the configuration, I discovered a few behaviors I had not read about, as well as configurations that could have caused issues had they not been addressed. In this article I will detail how I enabled the AWE functionality, as well as what behaviors I believe one should be aware of.

This scope of this article details the configuration of AWE for SQL Server 2000 Enterprise on a Windows 2000 Advanced Server machine. Configuring AWE on Windows 2000 Data Center, I’m assuming, is quite similar to configuring it on Windows 2000 Advanced Server, but as I have not performed such an operation, I will not address it here. Also, this article assumes you are using a single instance machine. Multiple instances and AWE settings require special planning not discussed here.

Why use AWE?

Prior to adding the additional 4GB of memory, the application running against this particular SQL Server instance was experiencing significant I/O spikes throughout the day, and was running under maximum memory conditions. The buffer cache and procedure cache utilization was always 100%, with the procedure cache often being starved for memory. 

After adding the additional memory, and enabling AWE, I saw the I/O spikes decrease significantly. The extra memory allowed both the buffer cache and procedure cache to grab a sufficient amount of memory needed for the application queries (I’ll be writing another article describing how you can monitor such information). The bigger buffer decreased the number of times that reads and write operations needed to read from disk.

Keep in mind that extra memory will not solve all I/O and memory issues. The performance outcome after configuring AWE will vary depending on your application activity, read/write ratio, network throughput, hardware components (CPU, RAID settings), and database size.

Configuration Steps

1.Assuming 8GB of physical memory, after adding the extra RAM, and prior to rebooting the server, your boot.ini should contain both the “/3GB /PAE” switches. Not having /3GB in your boot.ini will translate to 2GB of RAM reserved for the operating system, instead of 1GB remaining free with the “/3GB” switch. The “/PAE” switch is required if you want SQL Server to support more than 4GB of RAM. 

2.Make sure that the SQL Server service account has been granted “Lock Pages in Memory”) privileges. Just because your service account is a member of the administrators group does NOT mean that it has this policy setting already. I configured this setting by selecting Start | Run | and typing gpedit.msc. I selected OK to launch the Group Policy editor. I expanded Computer Configuration | expanded Windows Settings, Security Settings, Local Policies, and then clicked User Rights Assignments. In the Policy pane (on the right), I double clicked “Lock pages in memory”, and added the SQL Server service account used to run the SQL Server service. For Domain member machines, be sure that no security policies at the site, domain, or organization unit overwrite your policy change. Also, the policy change does not affect permissions of the service account until the SQL Server service is restarted. But do not restart the service yet! 

3.In Query Analyzer, connected as sysadmin for your SQL Server instance. Enable AWE by executing the following script:

sp_configure ‘show advanced options’, 1

RECONFIGURE

GO

sp_configure ‘awe enabled’, 1

RECONFIGURE

GO

This setting does not take effect until you restart the SQL Server instance – but do not do it yet – there is more!

4.Once AWE is enabled, SQL Server will no longer dynamically manage memory. SQL Server will grab all available physical memory, leaving 128MB or less for the OS and other applications to use. This underscores the importance of setting an max server memory amount that SQL Server should be allowed to consume. Determine this upper limit based on memory consumption of other applications on your server. Also note that a lower limit (min server memory) is no longer relevant in the context of AWE. 

In this example, to enable 7GB as the maximum SQL Server memory allowed to be consumed, issue the following command:

sp_configure ‘max server memory’, 7168

RECONFIGURE

GO

sp_configure ‘show advanced options’, 0

RECONFIGURE 

GO

5.NOW reboot your machine (assuming you have not rebooted since reconfiguring the boot.ini file). IF you have already rebooted after configuring the boot.ini file, you need only restart the SQL Server instance.

6.After the restart, check the SQL Log in Enterprise Manager right away. The most recent startup log should contain the words “Address Windowing Extensions enabled” early in the log. If you didn’t do it right, the log should say, “Cannot use Address Windowing Extensions because…”. The reasons for this message will be noted, such as not assigning “lock pages in memory”.

After the configuration

AWE awareness is not built in to all Windows 2000 tools, so here are a few areas you should be aware of when monitoring memory utilization…

The Windows Task Manager’s “Processes” tab tells a misleading tale about how much memory the SQLSERVR.EXE process is using. I was alarmed to see that after a few hours, the process was still just consuming 118MBs, versus the maximum 6.5GB I configured it for. For a reality check, within the Windows Task Manager, switch to the Performance tab and check out the Available physical memory. This amount should be the total memory available less the maximum amount you set for SQL Server, along with other applications running on your instance.

If you use Performance Monitor (System Monitor), keep in mind that for the SQLServer:Memory Manager object, that Target Server Memory (KB) and Total Server Memory (KB) counters will display the same number. This is because with AWE, SQL Server no longer dynamically manages the size of the memory used. It will consume the value of your ‘max server memory’. This memory will be made up of the physical RAM only, not the paging file. 

- AWE memory can be monitored in Performance Monitor (System Monitor) using the Performance object “SQLServer:Buffer Manager” several AWE related counters.

One last note on memory configuration… If you have not left enough RAM for other processes on the machine, do consider lowering the max server memory setting. Keep in mind that this change will not take effect until you restart the SQL Server service.

In a future article, I will review how to take a look at the memory utilization internals, so you can better monitor how the allocated memory is actually being used.