Memory Fundamentals for SQL Server - AWE ( Address Windowing Extensions), /PAE Switch (Physical Address Extension)

In the previous blog we saw the difference between Virtual Address Space between 32-bit and 64-bit systems. We also saw how virtual memory works and how /3GB switch can be used to increase the user access mode limit.

http://sqlandmorewithkruti.blogspot.in/2016/01/memory-fundamentals-for-sql-server.html

In this module we shall see what is AWE and /PAE switch for 32-bit systems.

Address Windows Extensions

As we have seen previously that the addresses are limited to 4GB virtual address space in 32-bit Operating System. However machines may have more than 4GB of RAM installed.Now Virtual Memory comprises of RAM and Paging file. So how then these user processes have access to an address which falls beyond the 4GB addressing scheme. This is facilitated by Windows through a mechanism called AWE (Address Window Extension).

It enables a process to reserve an address in the VAS which is again mapped to a different physical address range in RAM. This process is called windowing and is illustrated by the below diagram.

A user process must have the "Lock Pages in Memory" permission to leverage the benefits of AWE mechanism. AWE exists on all editions of Windows 2000 and later.In order for SQL Server to use the AWE APIs, you need to enable AWE. Once enabled, the next time SQL Server is started it will attempt to lock memory pages so that it can use AWE.

There are a few considerations to be made when using AWE

•  Primarily it's obvious that AWE is to support memory more than 4GB. If one were enable this option where the RAM that is less than 3GB it will be completely ignored. Application will be allocated memory as per Virtual Memory.
• An advantage of AWE is that it is never swapped to disk so it has reasonably faster access. AWE memory management differs from Virtual Memory Management. Its purely physical memory and its mappings.
• The design ensures that any memory allocated using the APIs cannot be shared with any other process and is made nonpageable, or locked.
• Only data cache pages are stored in AWE mapped memory. Data cache is a component of the SQL Server Buffer Pool area which will be seen in the later sections of this blog. 
• The  virtual memory window used to buffer the physical memory provided by AWE requires readwrite access.

How to Enable AWE

1. First we need to grant SQL server account "LOCK PAGE IN MEMORY" permission by editing the local group policies.

2. Enabling AWE through GUI

3. Enabling AWE through script

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO

You may go through the below links for technet articles on the same.

https://technet.microsoft.com/en-us/library/ms190673(v=sql.105).aspx

https://technet.microsoft.com/en-GB/library/ms190731(v=sql.105).aspx

AWE option can also be enabled for 64-bit systems but for the purpose of this blog it shall

 be covered later.

Physical Address Extension /PAE

      We've covered how to increase memory for user mode access by using the /3GB switch. We have also seen how to utilize the available physical memory more than 4GB using AWE. There is yet another switch called the Physical Address Extension /PAE switch which allows access for upto 128GB of RAM. Its a hardware related enhancement where the address bus is 36-bit. So now the number of addresses supported is (2^26).In terms of memory management there still exists page directories and page table entries for virtual memory mapping. In addition another level of page directory pointer table is added. The PTE is 4KB instead of 8KB. With this switch the Page Directory Table and Page Table Entries are 64-bit long so more number of addresses can be mapped. Thus unlike AWE there is a change in the memory structures and maps.

How to Enable /PAE Switch
Similar to /3GB switch you add /PAE switch in the BOOT.INI file.

Microsoft recommends the thumb rule as follows
If your system has < 4 GB - use only /3GB
If your system has > 4 GB and < 16 GB - use /3GB + /PAE + AWE
If your system has > 16 GB - use /PAE + AWE

Technet link for considerations of all the 3 options are as below

http://blogs.technet.com/b/marcelofartura/archive/2006/09/14/3gb-pae-awe-what-basic.aspx
http://blogs.technet.com/b/beatrice/archive/2008/08/29/3gb-pae-and-awe-taking-away-some-confusion.aspx
http://blogs.technet.com/b/perfguru/archive/2007/12/05/scenarios-using-3gb-and-pae-switch-and-appropriate-uses-of-these-switches.aspx
http://blogs.msdn.com/b/chadboyd/archive/2007/03/24/pae-and-3gb-and-awe-oh-my.aspx

Now that the basics are clear in the coming blogs we shall see how memory and CPU execute a user process like SQL server.