Memory management options for SQL Server performance
Memory management is an important part of tuning SQL Server performance. Various options are provided for your review and consideration. However, using 64-bit SQL and configuring SQL to use all of the memory that is provided is recommended for optimal Workflow performance.
Table: SQL Server memory configuration options
This 32-bit Windows boot option limits the operating system to 1 GB of RAM, reserving 3 GB for applications.
Maximum server memory
This SQL setting limits the memory that SQL can consume.
This 32-bit Windows boot option allows SQL Server to use more than 4GB of RAM.
This SQL option allows SQL Server to use more than 2 GB of RAM.
If the server has more than 2 GB of physical memory, enable AWE memory in SQL Server. This memory mode is recommended.
When AWE is enabled, SQL Server always attempts to use AWE-mapped memory. It uses wrapped memory or all memory configurations, including computers that provide applications with less than 3 GB of user mode address space.
If AWE memory is enabled in SQL, make sure that the SQL Server account has the correct Lock Pages in Memory setting. Both AWE and the Lock Pages in Memory setting can benefit 64-bit SQL Servers as well as 32-bit SQL Servers.
Windows memory usage
Set Windows memory usage to favor Programs over System Cache. SQL Server does its own data caching to improve performance.
If you use a 32-bit OS, make sure that PAE is enabled at the hardware level.
Enabling PAE lets SQL Server use AWE to map physical memory addresses higher than 4GB.
(Recommended SQL configuration)
This option eliminates the memory limitations that are associated with 32-bit systems.
By using a 64-bit operating system (Windows Server 2008 RS SP1) and 64-bit SQL, you do not need to use PAE or AWE.
SQL Server 2008 x64 is recommended for dedicated SQL Servers with more than 4 GB of physical memory.