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