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
- 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.
1. First we need to grant SQL server account "LOCK PAGE IN MEMORY" permission by editing the local group policies.
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
https://technet.microsoft.com/en-GB/library/ms190731(v=sql.105).aspx
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
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.
