SQL Server AWE Property

Previous SQL Server Memory Startup Switches	Group Policy Management Next

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. The AWE property only affectes the SQL Server database engine. Other components (SSAS, SSRS, etc) cannot use AWE and must share memory below the 4GB line.

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 below:

• Limitations of 32-bit Programs
• Using Memory above the 4GB Line
• Hardware for Extending 32-bit Addressing
• Operating System API for Extending 32-bit Addressing
• Program 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 use 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.

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.

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:

1. 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.

2. 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.

3. 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.

1. 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.

2. 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.

3. 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.

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.

1. 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.

2. 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.

   The Database Buffer Pool was chosen because increasing the size of the buffer pool normally results in improved performance for SQL Queries. Additionally, the database buffer pool pages get changed very seldom compared to other memory used by SQL Server.

3. 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 therefore 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.

1. 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.

2. 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 as shown in the table below. SQL FineBuild will automatically enable the SQL Server AWE property in line with these recommendations.

SQL Server Version	SQL Server Edition	Enable AWE
All	Express Edition	No
All	Workgroup Edition	No
SQL Server 2005	All except Express and Workgroup	Yes
SQL Server 2008, 2008R2 32-bit	All except Express and Workgroup	Yes
SQL Server 2008 and above 64-bit	All Editions	No
SQL Server 2012 and above 32-bit	All Editions	No

Additionally as already noted, AWE facilities are not available on Windows 2012 and above.

Copyright FineBuild Team © 2013 - 2021. License and Acknowledgements

Previous SQL Server Memory Startup Switches	Top	Group Policy Management Next