SQL Server - To AWE or not to AWE? That is the question!!
Address Windows Extension has always been a debate and I always get the question of whether to enable AWE on SQL Server or not. Now to be honest this is not a straight forward answer there are multiple options here that we need to look at and we cannot discuss AWE without talking about Lock Pages in Memory option.
To summarize you have the following options
1) 32-bit SQL Server on 32-bit OS --> not much can you do here unless you enable /PAE where the OS can access up to 64 Gigs of memory then Enable AWE and Lock pages in memory
2) 32-bit SQL Server on 64-bit OS --> Enable AWE and Lock Pages in Memory
3) 64-bit SQL Server on 64-bit OS -->AWE Option is ignored, its best to enable Lock Pages in Memory
4) 64-bit SQL Server on 32-bit OS --> :P just messing with you guys
So that is the short answer however to actually understand why these options are the recommended options that is the tricky part. So we will start by explaining why there is a 4 Gigs limits on 32-bit architecture. Followed by what AWE does to the OS and memory, we will also touch the Lock Pages in Memory. Then we will wrap up at this point of time not really sure of what since I wrapped up at the beginning but when we reach there we will figure something out.
So why can’t 32-bit architecture support more than 4 Gigs?
It’s simple a 32-bit processors means that it has 32 bit address lines – address lines are the lines connecting the processors to the Memory since it all boils down to 0 and 1 (wires can either have electricity or not) each address lines specifies a single byte in the address putting 32 address combinations of 0’s and 1’s you get only 232-1 different options and that is about 4 gigs of memory (minus 1 byte). That basically means that each pointers used by your application are of 32 bit.
So how are these 4 gigs mapped after all any application needs to use the OS (Kernel) and should be managed by the OS, well for simplicity, the 4 gigs are divided as follows Address (0x80000000 to 0xFFFFFFFF) are used by the OS, that is about 2 gigs of memory and the remaining 2 gigs (from address 0x00000000 to 0x7FFFFFFF) are used by your application this is shown in the figure below.
Okay so that answers the 32 bit 4 gig byte address issue we won’t get into PAE and /3gb its sort of outside the scope of this article however you can check the links below for details on this.
/3GB: http://msdn.microsoft.com/en-us/library/windows/hardware/ff556232(v=vs.85).aspx
PAE: http://msdn.microsoft.com/en-us/library/windows/desktop/aa366796(v=vs.85).aspx
Okay so this is all 32-bit on 32-bit but what happens to the address space of 32-bit process on a 64 bit OS. Does it use 2 Gigs for the process? Does it use 4 gigs for the process? How much memory is used for the OS?
The answers are as follows
àThe 32- bit is allocated the 4 gigs of memory
àThe OS still uses 2 gigs it turns out it only needs 2 gigs.
Not only that the Process still uses the top 4 gigs and 0x0000000 - 0x80000000 and the OS uses the bottom space. All the memory in between is left free to ensure that the Process Address space does not override the OS an additional security mechanism – Cool!
Okay so what is AWE?
Address Windowing Extensions (AWE) allow 32-bit process to access large amounts of memory, i.e over 4 Gigs as explained earlier
Now the trick is all in how AWE works.
Now a 32-bit application can only see a maximum of 4 gigs on a 64-bit OS however the OS can see more than 4 gigs, but what if we need more than 4 gigs of memory what if we are loading more than 4 gigs we have 2 options here
1) Paging data from and to the hard disk (really slow because of the mechanical movement required to allocate the required data.
2) If it’s a 64-bit OS load all the required data for example SQL Server data file into the memory and instead of paging to the hard disk we can page to the memory (not fully accurate but will do for explanation purposes.)
And this is what AWE is all about it creates an address window in the 4 gigs address space and allows you to move data to and from the 4 gigs address space but this data is not loaded from hard disk it is loaded from the memory.
So how does windows do that well its simple uses a set of API’s that are used to load memory to and from the 4 gigs of memory.
AWE actually needs to place a few restrictions on the memory that is being used. I have copied these restrictions from the following link (http://msdn.microsoft.com/en-us/library/windows/desktop/aa366527(v=vs.85).aspx)
- Virtual address ranges allocated for the AWE are not sharable with other processes (and therefore not inheritable). In fact, two different AWE virtual addresses within the same process are not allowed to map the same physical page. These restrictions provide fast remapping and cleanup when memory is freed.
- The physical pages that can be allocated for an AWE region are limited by the number of physical pages present in the machine, since this memory is never paged – it is locked down until the application explicitly frees it or exits. The physical pages allocated for a given process can be mapped into any AWE virtual region within the same process. Applications that use AWE must be careful not to take so much physical memory that they cause other applications to page excessively or prevent creation of new processes or threads due to lack of resources. Use the GlobalMemoryStatusEx function to monitor physical memory use.
- AWE virtual addresses are always read/write and cannot be protected via calls to VirtualProtect (that is, no read-only memory, noaccess memory, guard pages, and the like can be specified).
- AWE address ranges cannot be used to buffer data for graphics or video calls.
- An AWE memory range cannot be split, nor can pieces of it be deleted. Instead, the entire virtual address range must be deleted as a unit when deletion is required. This means you must specify MEM_RELEASE when calling VirtualFree.
- Applications can map multiple regions simultaneously, provided they do not overlap.
- Applications that use AWE are not supported in emulation mode. That is, an x86 application that uses AWE functions must be recompiled to run on another processor, whereas most applications can run without recompiling under an emulator on other platforms.
In order to use AWE your SQL Service account has to be able to lock the pages in memory. That is to prevent the paging out of the extended memory space as to not cause two processes to access the same address space and corrupt each other’s address space.
Now how does SQL Server do AWE. Well on 32 bit systems and I stole this algorithm from CSS SQL Server Engineers blog in the following link. (http://blogs.msdn.com/b/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx)
1) If AWE is not enabled and there is enough memory à print a message in the errorlog guiding the user to use AWE
2) If the lock pages in memory is not set correctly that is insufficient privileges or not set at all nothing is printed in the errorlog nothing is printed at all it uses the AdjustTokenPrivileges() API (http://msdn.microsoft.com/en-us/library/windows/desktop/aa375202(v=vs.85).aspx)
3) If both are good it prints a message informing to the user is printed indicating AWE is Enabled,
So what happens in a 64-bit SQL Server well the option of AWE is ignored since the address space does not have the 4 gigs limit however lock paging in memory does enhance the performance of SQL Server cause it allows SQL Server to prevent the OS from paging information back to the hard disk and hence enhances the performance of SQL Server.
But how is that done.
Well take a look at the code sample below of an example of using AWE. You can find this sample in (http://msdn.microsoft.com/en-us/library/windows/desktop/aa366531(v=vs.85).aspx)
#include <windows.h>
#include <stdio.h>
#include <tchar.h>
#define MEMORY_REQUESTED 1024*1024 // request a megabyte
BOOL
LoggedSetLockPagesPrivilege ( HANDLE hProcess,
BOOL bEnable);
void _cdecl main()
{
BOOL bResult; // generic Boolean value
ULONG_PTR NumberOfPages; // number of pages to request
ULONG_PTR NumberOfPagesInitial; // initial number of pages requested
ULONG_PTR *aPFNs; // page info; holds opaque data
PVOID lpMemReserved; // AWE window
SYSTEM_INFO sSysInfo; // useful system information
int PFNArraySize; // memory to request for PFN array
GetSystemInfo(&sSysInfo); // fill the system information structure
_tprintf(_T("This computer has page size %d.\n"), sSysInfo.dwPageSize);
// Calculate the number of pages of memory to request.
NumberOfPages = MEMORY_REQUESTED/sSysInfo.dwPageSize;
_tprintf (_T("Requesting %d pages of memory.\n"), NumberOfPages);
// Calculate the size of the user PFN array.
PFNArraySize = NumberOfPages * sizeof (ULONG_PTR);
_tprintf (_T("Requesting a PFN array of %d bytes.\n"), PFNArraySize);
aPFNs = (ULONG_PTR *) HeapAlloc(GetProcessHeap(), 0, PFNArraySize);
if (aPFNs == NULL)
{
_tprintf (_T("Failed to allocate on heap.\n"));
return;
}
// Enable the privilege.
if( ! LoggedSetLockPagesPrivilege( GetCurrentProcess(), TRUE ) )
{
return;
}
// Allocate the physical memory.
NumberOfPagesInitial = NumberOfPages;
bResult = AllocateUserPhysicalPages( GetCurrentProcess(),
&NumberOfPages,
aPFNs );
if( bResult != TRUE )
{
_tprintf(_T("Cannot allocate physical pages (%u)\n"), GetLastError() );
return;
}
if( NumberOfPagesInitial != NumberOfPages )
{
_tprintf(_T("Allocated only %p pages.\n"), NumberOfPages );
return;
}
// Reserve the virtual memory.
lpMemReserved = VirtualAlloc( NULL,
MEMORY_REQUESTED,
MEM_RESERVE | MEM_PHYSICAL,
PAGE_READWRITE );
if( lpMemReserved == NULL )
{
_tprintf(_T("Cannot reserve memory.\n"));
return;
}
// Map the physical memory into the window.
bResult = MapUserPhysicalPages( lpMemReserved,
NumberOfPages,
aPFNs );
if( bResult != TRUE )
{
_tprintf(_T("MapUserPhysicalPages failed (%u)\n"), GetLastError() );
return;
}
// unmap
bResult = MapUserPhysicalPages( lpMemReserved,
NumberOfPages,
NULL );
if( bResult != TRUE )
{
_tprintf(_T("MapUserPhysicalPages failed (%u)\n"), GetLastError() );
return;
}
// Free the physical pages.
bResult = FreeUserPhysicalPages( GetCurrentProcess(),
&NumberOfPages,
aPFNs );
if( bResult != TRUE )
{
_tprintf(_T("Cannot free physical pages, error %u.\n"), GetLastError());
return;
}
// Free virtual memory.
bResult = VirtualFree( lpMemReserved,
0,
MEM_RELEASE );
// Release the aPFNs array.
bResult = HeapFree(GetProcessHeap(), 0, aPFNs);
if( bResult != TRUE )
{
_tprintf(_T("Call to HeapFree has failed (%u)\n"), GetLastError() );
}
}
/*****************************************************************
LoggedSetLockPagesPrivilege: a function to obtain or
release the privilege of locking physical pages.
Inputs:
HANDLE hProcess: Handle for the process for which the
privilege is needed
BOOL bEnable: Enable (TRUE) or disable?
Return value: TRUE indicates success, FALSE failure.
*****************************************************************/
BOOL
LoggedSetLockPagesPrivilege ( HANDLE hProcess,
BOOL bEnable)
{
struct {
DWORD Count;
LUID_AND_ATTRIBUTES Privilege [1];
} Info;
HANDLE Token;
BOOL Result;
// Open the token.
Result = OpenProcessToken ( hProcess,
TOKEN_ADJUST_PRIVILEGES,
& Token);
if( Result != TRUE )
{
_tprintf( _T("Cannot open process token.\n") );
return FALSE;
}
// Enable or disable?
Info.Count = 1;
if( bEnable )
{
Info.Privilege[0].Attributes = SE_PRIVILEGE_ENABLED;
}
else
{
Info.Privilege[0].Attributes = 0;
}
// Get the LUID.
Result = LookupPrivilegeValue ( NULL,
SE_LOCK_MEMORY_NAME,
&(Info.Privilege[0].Luid));
if( Result != TRUE )
{
_tprintf( _T("Cannot get privilege for %s.\n"), SE_LOCK_MEMORY_NAME );
return FALSE;
}
// Adjust the privilege.
Result = AdjustTokenPrivileges ( Token, FALSE,
(PTOKEN_PRIVILEGES) &Info,
0, NULL, NULL);
// Check the result.
if( Result != TRUE )
{
_tprintf (_T("Cannot adjust token privileges (%u)\n"), GetLastError() );
return FALSE;
}
else
{
if( GetLastError() != ERROR_SUCCESS )
{
_tprintf (_T("Cannot enable the SE_LOCK_MEMORY_NAME privilege; "));
_tprintf (_T("please check the local policy.\n"));
return FALSE;
}
}
CloseHandle( Token );
return TRUE;
}
The Three functions highlighted in yellow show that we first allocate the memory in the HEAP with the size of the data that needs to be loaded. Memory in the HEAP is not movable. (HeapAlloc)
Then we reserve in the VirtualAddressSpace to load the data from the heap into that address space being reserved. (VirtualAlloc)
Then we map this memory from the Heap to the VirtualAddressSpace specified.
That pretty much explains how the AWE is used in 32-bit SQL Server.
As for 64 it was mentioned earlier that lock pages in memory is the best option and AWE is ignored.
Hope this explains the options you have with AWE.
Sources:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/446fddf9-72aa-4d58-8382-a3ef931d2be9/awe-using-sql-server-2005-32bit-on-a-64-bit-operating-system?forum=sqldatabaseengine
http://sqlblog.com/blogs/argenis_fernandez/archive/2012/12/30/the-myth-around-32-bit-sql-server-instances-on-64-bit-operating-systems-and-awe.aspx
http://msdn.microsoft.com/en-us/library/aa366796(VS.85).aspx
http://msdn.microsoft.com/en-us/library/ms191481.aspx
http://msdn.microsoft.com/en-us/library/windows/desktop/aa366892(v=vs.85).aspx
http://msdn.microsoft.com/en-us/library/windows/desktop/aa375202(v=vs.85).aspx
and most of the blogs mentioned in the case