This is something we had to do again recently and its improved our performance and query speeds on one particular system. This information is readily available from various sources and blogs, I just wanted to re-blog and ensure we’re all aware of the benefits gained from using AWE !
Enabling Memory Support for over 4GB of Physical Memory
To enable Address Windowing Extensions (AWE) for Microsoft SQL Server, you must run the SQL Server Database Engine under a Microsoft Windows account that has been assigned the Lock Pages in Memory option and use sp_configure to set the awe enabled option to 1.
Note:
Support for AWE is available only in the SQL Server Enterprise, Standard, and Developer editions and only applies to 32-bit operating systems. Analysis Services cannot take advantage of AWE mapped memory. If the available physical memory is less than the user mode virtual address space, AWE cannot be enabled.
Lock Pages in Memory
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. The Lock Pages in Memory option is set to OFF by default in SQL Server. If you have system administrator permissions, you can enable the option manually by using the Windows Group Policy tool (gpedit.msc) and assign this permission to the account that SQL Server is running.
Although it is not required, we recommend locking pages in memory when using 64-bit operating systems. For 32-bit operating systems, Lock pages in memory permission must be granted before AWE is configured for SQL Server.
The awe enabled Option
To enable AWE for an instance of SQL Server, use sp_configure to set the awe enabled option to 1, and then restart SQL Server. Because AWE is enabled during SQL Server startup and lasts until SQL Server shutdown, SQL Server will notify users when awe enabled is in use by sending an “Address Windowing Extensions enabled” message to the SQL Server error log. For more information about the awe enabled configuration option, see awe enabled Option.
Maximize Data Throughput for Network Application
To optimize system memory use for SQL Server, you should limit the amount of memory that is used by the system for file caching. To limit the file system cache, make sure that Maximize data throughput for file sharing is not selected.
To check the current setting on your operating system
In Control Panel, double-click Network Connections, and then double-click Local Area Connection.
On the General tab, click Properties, select File and Printer Sharing Microsoft Networks, and then click Properties.
Select Maximize data throughput for network applications, click OK, and then close the rest of the dialog boxes.
For more information about using AWE, see the official documentation.
