I was asked question by Jr. DBA that “What is AWE?”. For those who do know what is AWE or where is it located, it can be found at SQL Server Level properties. AWE is properly explained in BOL so we will just have our simple explanation.
Address Windowing Extensions API is commonly known as AWE. AWE is used by SQL Server when it has to support very large amounts of physical memory. AWE feature is only available in SQL Server Enterprise, Standard, and Developer editions with of SQL Server 32 bit version.
Microsoft Windows 2000/2003 server supports maximum of 64GB memory. If we have installed SQL Server 32 bit version which can support maximum of 3 GB memory on Windows 2000/2003, we can enable AWE feature to use available physical memory of server to improve performance of SQL Server. In simple words, AWE provides memory management functions which lets windows to allow more than 3GB memory to standard 32 bit application.
There are many other modification needs to be done before AWE option can be used. Please refer SQL Server BOL Using AWE for additional details.
Reference : Pinal Dave https://blog.sqlauthority.com/ )
11 Comments. Leave new
What has you experience been using AWE?
I find that it doesn’t use more memory at all….
Without AWE, I run a set a queries and run the process up to 1.5GB of memory, the pagefile runs up to about 2.3GB.
With AWE, I run the same set of queries and the process only uses 200 MB, but the pagefile runs up to about 10GB.
Keep in mind that it is paging to RAM and I have paging to disk disabled.
I am running 2003 Enterprise 32bit, 64 GB RAM, SQL 2005 Enterprise 32bit
My OS and SQL are 64bits, I do not need to use AWE, my Server has 250GB of memory and 6 x Octa processor.
The Problem IS:
There is some jobs running that drives SQL Server to take 240GB of memory and SQL seems to be not managing that taken memory and wants to allocate more and more then my jobs start failing due to this. Is there any idea why sql is trying to take more memory instead managing the allocated ammount?
Thanks.
@Marcos,
Have you resolved your problem with memory? It seems that we have the same problem with our SQL Server.
Actually we found SSIS issues. When the jobs using DTS packages starts, the dts process get running on the server and never closes. That was the problem. to work around it the SSIS services should be recycled from time to time. I escalated the issue to Microsoft and from that point on I could not follow up the issue, but I am retrieving that info from them. so far we had not a permanent fix.
Hi, I can see that the AWE feature is visible in a workgroup edition. is it going to work if i enable it? Thanks.
We have upgraded to SP4 on 14 Jan 13 and since then we are finding lots of table locks while using Navision. Can SP4 affect the performance
i have one doubt , AWE let allow beyond 3 GB, what is the use of \PAE ?, i strong beleave AWE with “lock page of memory” will be useful to lock pages & avoid memory pressures.
With Windows Server 2008 onward, especially using 64 Bit or R2 Version, this is no longer an issue. Vut 32 Bit Windows Server 2003 Standard allowed only 4 GB of physical memory. Datacenter and Enterprise can handle more but are by default limited to 4 GB.
These could be distributed like 1 GB Win, 3 GB all applications. In boot Options there was a /3GB option for this distribution (Default distribution would be 2GB Kernel:2 GB User). Furthermore in Boot Options you could enable “Physical Address Extension” /PAE which for Datacenter and Enterprise should make it possible to address more than 4 GB of memory. This only works for systems running 2003 and newer and certain older versions, who allow applications o use more than 2 GB if they can handle it.
With 64 Bit Systems, the OS automatically cann see all Memory and applications get up to 4 GB. 2008 and newer have different limitations, far higher than 4 GB and do not use/need this.
For AWE in SQL, this allowed to address more memory than applications would be able to handle in older systems. For 64 Bit systems it does not effect how much memory can be used, but a different API is used to allocate memory. (Something a little more complex about lock pages etc.)
Does we need to enable AWE on 64 bit Windows 2012 server?
No.
Is AWE available on sql 2016? Is it required to enable AWE in 2016 server ?