SQL Server AWE Property
The SQL Server property Use AWE to allocate memory
can significantly improve performance for a 32-bit version of SQL Server. It can also give a minor improvement in performance for 64-bit versions of SQL Server 2005.
In order to understand how the use of AWE benefits SQL Server, it is important to know what is happening 'behind the scenes' when this option is used.
The details of what happens for 32-bit versions of SQL Server are explained under the following headings:Limitations of 32-bit ProgramsUsing memory above the 4GB lineHardware for extending 32-bit addressingOperating System API for extending 32-bit addressingProgram API for extending 32-bit addressing
The details of what happens for a 64-bit version of SQL Server 2005 are explained below:SQL Server 2005 64-bit use of AWE
Recommendations for all editions of SQL Server are given at Recommendations for use of AWE
Limitations of 32-bit Programs
For any 32-bit program, the highest memory location that can be accessed is 4GB. This applies both to 32-bit operating systems and 32-bit programs running in a 64-bit operating system.
This is because a Windows uses a component called a Register
to hold a memory address, and 32-bit operating systems and 32-bit programs uses registers that are 32 bits long.
A memory address that is 1 byte above the 4GB line has an address that is 33 bits long. When you want to access somewhere between 8GB and 16GB the address of that memory is 34 bits long. These addresses simply cannot fit into a 32-bit register, so at first sight a 32-bit program cannot use them.
When 32-bit operating systems were first launched back in the 1970s it was rare to see a computer with even 4MB of memory, and a 32-bit register to hold a memory address seemed very generous. However, memory usage increased very quickly as computers increased in power, and within 10 years workloads were being restricted by the 4GB memory limit.
This problem was reached and solved on mainframe computers many years before a 32-bit edition of Windows existed, and when the 4GB memory limit became a problem within Windows, Microsoft used the same solutions that had been proven to work on mainframes.Top
Using memory above the 4GB line
The best approach to using memory above the 4GB line is to increase the size of the memory address register. This may seem trivial, but to use a 64-bit address register requires a very significant re-write of both the operating system and programs. For example, managing 64GB of memory requires a totally different approach to managing 500MB of memory.
It took Microsoft many years to upgrade Windows to work as a 64-bit operating system, but in the meantime they needed to make 32-bit programs work on machines with more than 4GB memory.
The industry standard approach to making a 32-bit operating system use more than 4GB memory is called Extended Memory
. This is what Microsoft used with 32-bit editions of Windows.
The use of Extended Memory requires special hardware in the CPU chip, and special software within both the operating system and programs to use it.Top
Hardware for extending 32-bit addressing
The special hardware used to access more than 4GB memory from a 32-bit operating system is only used by a 32-bit operating system. A 64-bit operating system can simulate this hardware for 32-bit programs, but does not itself need to use this hardware.
This special hardware provides the following functions:
- a) All memory, including that above the 4GB line is divided into 4KB pages. A list is made of the 4KB pages, known as the Page Frame Number (PFN) database, and this list is kept below the 4GB line. The PFN list uses 1MB of memory for every 146MB of server memory, and therefore the more memory that exists the larger this list will be. For example, for a server with 32GB of memory, the PFN list will use about 224MB of memory within the Windows system address space.
- b) Special CPU instructions are provided that can copy the contents of a given 4KB page from its location above the 4GB line to a special 4KB location below the 4GB line. After the memory contents have been copied, they can be updated by a 32-bit program.
- c) Special CPU instructions are provided that can copy the contents of a given 4KB page from the special 4KB location below the 4GB line to its location above the 4GB line. After the memory has been copied to above the 4GB line, it can not be read or written by a 32-bit program.
Operating System API for extending 32-bit addressing
It should by now be possible to work out what special software is needed in the operating system to make the hardware use memory above the 4GB line.
An Application Programming Interface (API) is provided to allow programs to take advantage of the hardware that allows use of extended memory. Another API available only to the operating system allows Windows to keep track of which extended memory pages are in use and the programs that own them.
In Windows, the changes needed to use memory above the 4GB line was originally called Program Addressing Extensions
and activated by including the /PAE
switch in the Windows boot process. The /PAE switch was introduced in Windows 2000, and from Windows 2008 R2 the /PAE functionality is turned on by default.
The next generation of CPU chips added new CPU instructions to make specified memory locations read-only. The memory protection features are activated by using the /NOEXECUTE
switch in the Windows boot process.
The operating system API to use extended memory is available in 32-bit and 64-bit versions of Windows 2003, Windows 2008, and Windows 2008 R2.
The API is not
available in client operating systems or in Windows 2012 and above, and 32-bit programs running on these systems can only use a maximum of 4GB memory.Top
Program API for extending 32-bit addressing
The final part of using extended memory is special code in the program to use the PAE API.
The code to use extended memory in SQL Server was written by a different team who wrote the code in Windows. This has resulted the SQL Server using the term Address Window Extensions
(AWE) while Windows uses the term PAE.
The SQL Server database engine has code that uses the PAE API, and can therefore make use of extended memory above the 4GB line. Other SQL Server components, such as Analysis Services, Reporting Services, and Integration Services, do not have code that uses the PAE API and are therefore limited to using memory below the 4GB line.
Because a page of extended memory can only be read or written after it has been copied below the 4GB line, SQL Server only uses extended memory for the database buffer pool. This is because database buffer pool pages get changed very seldom compared to other memory used by SQL Server.
When SQL Server requests that memory is reserved for AWE use, it will make this request only once at SQL Server Startup time. If there is not enough memory available to meet the request, then SQL Server will continue to run without using AWE facilities. SQL Server also needs the Windows Lock Pages in Memory
right to be enabled to use AWE.
If the request to use AWE cannot be granted, then a 32-bit version of SQL Server will be limited to using memory below the 4GB line.
A simplified way of looking at the key elements of this process is a shop with a stock room. Customers can see and buy items that are on a display unit in the shop, but they are not allowed to go into the stock room. If the shop staff take items from the display unit and put them in the stock room, customers can not buy these items. If a display unit in the shop is empty, customers cannot buy the items even if the stock room has lots of them. The shop staff need to get more items out of the stock room and put them on display so customers can buy them. The process of moving 4KB pages in and out of extended storage is similar to the process of the shop and the stock room.
As can be seen, the process of managing extended memory is time-consuming, and none of this work is needed on a 64-bit edition of Windows. Therefore, it is always better to use a 64-bit edition of SQL Server if the computer contains more than 4GB memory.Top
SQL Server 2005 64-bit use of AWE
AWE facilities are used by SQL Server 2005 64-bit edition to give a minor improvement to performance.
If SQL Server 2005 64-bit edition requests the use of AWE and it has the Lock Pages in Memory
Right, then the amount of memory being requested for AWE use will be configured by Windows as a Private Work Set
Memory in a Private Work Set cannot be moved to the Windows Page File, and is the last memory to be reclaimed if Windows has a shortage of memory. It can therefore provide a small performance improvement over other types of memory allocation.
From SQL Server 2008 onwards, 64-bit versions of SQL Server do not need to use the AWE feature to have locked memory configured as a Private Work Set.Top
Recommendations for use of AWE
The SQL Server AWE property should be used on all installations of SQL Server except where noted below. SQL FineBuild will automatically enable the SQL Server AWE property in line with these recommendations.
There are some exceptions where AWE can not be used. SQL FineBuild will not attempt to use AWE in these situations.
The AWE facilities are not available to the following SQL Server editions:
| SQL Server Version || SQL Server Edition |
| All || Express Edition |
| All || Workgroup Edition |
| SQL Server 2012 and above || All Editions |
Additionally, as already noted, AWE facilities are not available on Windows 2012 and above.
Copyright © 2013 - 2014 Edward Vassie. License and Acknowledgements