Like we already know, Windows-based applications can use Windows AWE (Address Windowing Extensions) APIs to allocate and to map physical memory into the process address space. AWE allow 32-bit operating systems to access large amounts of memory. Memory that is allocated by using this method is never paged out by the operating system, provided “Lock Pages In Memory” user right (LPIM) has been granted to the application Service account.
Lock Pages in memory is by default given to Local system Service account.
SQL Server supports dynamic allocation of AWE memory on Windows Server. The SQL Server 64-bit version also uses “locked pages” to prevent the process working set (committed memory) from being paged out or trimmed by the operating system. When you enable “locked pages,” it is very important to set an appropriate value for “max server memory” and for “min server memory” configuration options for each instance of SQL Server to avoid system-wide problems.
During startup, AWE reserves only a small portion of AWE-mapped memory. As additional AWE-mapped memory is required, the operating system dynamically allocates it to SQL Server. Similarly, if fewer resources are required, SQL Server can return AWE-mapped memory to the operating system for use by other processes or applications.
AWE Feature is not avaiable in SQL Server 2012.
Even though the “awe enabled” feature is not available in 32-bit SQL Server 2012, you can still use the “locked pages” feature by assigning the “lock pages in memory” user right for the SQL Server startup account.
Regards
Sarabpreet Anand
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
previously SQL grabbed some memory (MemoryToLeave aka MTL) outwith the dynamic pool (and could be changed via the -G startup switch. Is this still true for SQL2012 (that you can instruct SQL to make such additional reservation, and that this is not included in the max memory figure) ?
Hi Dick,
The virtual address space that is reserved for these allocations is determined by the memory_to_reserve configuration option. The default value that SQL Server uses is 256 MB. To override the default value, use the SQL Server -g startup parameter. This part of the virtual address space is also known as “Memory-To-Leave” or “non-Buffer Pool region.”
Because SQL Server 2012 has the new “any size” page allocator that handles allocations greater than 8 KB, thememory_to_reserve value does not include the multi-page allocations. Except for this change, everything else remains the same with this configuration option.
Hope this clears the doubt.
Hi, I’m buggered trying to get Windows Server 2008 64 bit to allow SQL to unleash. Emerging got Standard server not realizing it placed artificial limits on memory. Even in 32 bit SQL you could address large memory with PAE and AWE. We have MS SQL 2008 R2, and MA Server Standard 2008. Given SQL account memory and paging control, set AWE and not sure if its using extra memory outside OS limits like 32 bit would. Is there any way to get around artificial Windows Server Standard memory limits, since our version of SQL supports twice that much, and confirm its really working? Thank you for any help please.