This article should be useful for those of you out there who are still using SQL Server 2005 32-bit like me. I know it’s backward somewhat but one is saddled with the responsibility of making the most of one’s environment while pushing for better resources.
We recently implemented Addressing Windows Extensions on the SQL Server instance host databases for a very busy application which processes electronic funds transfer transaction (ATM and POS transactions mainly).
The server in question is configured as follows:
- CPU: Intel(R) Xeon( R) CPU X5670 @2.93 GHz (24 Cores)
- RAM: 32 GB
- Operating System: Windows Server 2003 Service Pack 2
- SQL Server Version: SQL Server 2005 Build 3080
Essentially, we had 32 GB of RAM installed but SQL server could use no more than a meagre 2 GB at best (this if the Virtual Address Space limit for 32-Bit Windows).
Several articles address memory management on SQL Server as well a show to implement Physical Address Extensions (PAE) on the OS level and Addressing Windows Extensions (AWE) on the instance level. I will not dwell on this in this article because what I really want to do is to share the gains of this feature based on our own experience. However, below is a summary of the steps required:
- Open the boot.ini file as follows: My Computer>Properties>Advanced>Start Up and Recovery>Settings>System Start Up>Edit
- Add the switch /PAE at the end of the boot.ini file access like below:
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003 Enterprise x64 Edition" /noexecute=optout /fastdetect /PAE
- Reboot Windows and confirm that PAE is enabled by looking at the My Compiuter properties:
Fig. 1: Physical Address Extensions Enabled
- Grant the permission ‘Lock Pages in memory’ to the SQL Server Service account. The grant is given in the location: Control Panel > Administrative Tools > Local Security Policy >Local Policies > user Rights Assignment > Lock Pages in Memory
NB: I advise that you use a specific account to run SQL Server not a system account like Local System or a share account. The LPIM right allows the account to grab memory from the OS at will.
- Open SQL Server instance properties and under the Memory sections, check the box Use AWE to allocate memory
Fig. 2: AWE Enabled
- It is also advisable to set the Maximum Server Memory to a value less than the total Physical Memory so as to make sure there is always memory for other processes especially if you are running SQL Server on shared hardware.
- Restart SQL Server
Once done, the error log at startup will notify you that AWE is enabled. You can also use the script attached to this article which I got from a fellow DBA at SQLServerCentral to check the buffer Pool memory usage stats. You will notice that the Buffer pool target Memory exceeds the VAS limit and approaches the MAX Server memory value as more and more load is added. In my case, the following graph shows the impact of AWE on the Buffer Pool:
Fig. 3: Comparing Buffer Pool Metrics
In addition, statistics like Buffer Cache Hit Ratio, Page Life Expectancy, Memory/Pages will also show significant performance improvement. When we tested this feature earlier using simulated load in a test environment, we took perfmon counters and found the following differences:
Page Life Expectancy
Page Life Expectancy measures the number of seconds a page will stay in the buffer pool without references.
Fig. 4: Page Life Expectancy with AWE Disabled
Fig. 5: Page Life Expectancy with AWE Enabled
Buffer Cache Hit Ratio
Buffer Cache Hit Ratio is the Percentage of pages that were found in the buffer pool without having to incur a read from disk. Buffer Cache Hit Ratio approaching 100% is an indicator of good performance.
Fig. 6: Buffer Cache Hit Ratio with AWE Disabled
Fig. 7: Buffer Cache Hit Ratio with AWE Enabled
Without AWE, the Buffer Cache Hit ratio averages 99.67% till the 11th minute where the average drops to 99.48%. When AWE is enabled, the Buffer Cache Hit ratio averages 99.85%.
Pages Per Second
Pages/sec is the rate at which pages are read from or written to disk to resolve hard page faults. Hard page faults are exceptions raised when a page required by an application is not available in physical memory but needs to be retrieved from disk. Pages/second measure how often this occurs.
Fig. 8: Pages per Second with AWE Disabled
Fig. 9: Pages per Second with AWE Enabled
With AWE disabled during the query run, the Pages/sec counter averaged 10.21 Pages/sec. With AWE enabled, the value averaged 0.78 Pages/sec.
By far the most significant metric that showed us that the this setting was indeed useful was that the very next day after the change the head of the Team responsible for the service whose database we had just ‘tampered with’ came down to ask what I had done to make the server so fast.
You may find the following references on SQL Server Memory Management useful:
