What is AWE (Address Windowing Extensions) in SQL Server?

“What is AWE?”. For those who do know what is AWE or where is it located, it can be found at SQL Server Level properties. AWE is properly explained in BOL so we will just have our simple explanation.

Address Windowing Extensions API is commonly known as AWE.  AWE is used by SQL Server when it has to support very large amounts of physical memory. AWE feature is only available in SQL Server Enterprise, Standard, and Developer editions with of SQL Server 32 bit version.

Microsoft Windows 2000/2003 server supports maximum of 64GB memory. If we have installed SQL Server 32 bit version which can support maximum of 3 GB memory on Windows 2000/2003, we can enable AWE feature to use available physical memory of server to improve performance of SQL Server. In simple words, AWE provides memory management functions which lets windows to allow more than 3GB memory to standard 32 bit application.

In SQL Server, you can use Address Windowing Extensions (AWE) to provide access to physical memory in excess of the limits set on configured virtual memory.

If the option has been enabled, the message "Address Windowing Extensions enabled" is written to the SQL Server error log when the instance of SQL Server is started
.
Before you use AWE, you must first configure the Microsoft Windows Lock Pages in Memory policy

To support more than 4 GB of physical memory on 32-bit operating systems, you must add the /pae parameter to the boot.ini file and reboot the computer. See your Windows documentation for details.

To configure the AWE enabled option

1. In Object Explorer, right-click a server and select Properties.
2. Click the Memory node.
3. Under Server memory options, select Use AWE to allocate memory.

he Windows policy Lock Pages in Memory option is disabled by default. This privilege must be enabled to configure Address Windowing Extensions (AWE). This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Locking pages in memory is not required on 64-bit operating systems.
Use the Windows Group Policy tool (gpedit.msc) to enable this policy for the account used by SQL Server. You must be a system administrator to change this policy.
For a table that lists the maximum server memory values, see Memory Architecture.

To enable the lock pages in memory option

1. On the Start menu, click Run. In the Open box, type gpedit.msc.
   The Group Policy dialog box opens.
2. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
3. Expand Security Settings, and then expand Local Policies.
4. Select the User Rights Assignment folder.
   The policies will be displayed in the details pane.
5. In the pane, double-click Lock pages in memory.
6. In the Local Security Policy Setting dialog box, click Add.
7. In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.