Some notes on memory configuration management for SQL server 2005 (32bit and 64bit). These are guidelines, always perform an exhaustive analysis prior to making configure changes.

32 BIT
Switches can be added to the Boot.ini file - first, some terminology.

/PAE - this switch allows OS to access physical memory beyond 4GB. On Windows Enterprise and Datacenter Editions..

/3 GB - Forces the OS to reserve only 1GB and allow applications (e.g SQL Server ) to use the remainder

Some guidelines on what switches to use, & under what circumstances

1)If memory <= 4GB - there is nothing to configure (except /3GB switch in boot.ini).
2)If there is 4Gb - 16 GB physical memory - use /PAE and /3GB switch
3)Quite often a SQL server will co exist with other applications, as well as SQL Server. In those circumstances:
a) don't enable /3GB switch or
b)limit the SQL Server max Memory

An example via command line is found below. Although you can make the same change via the SQL server Management Studio (SSMS) GUI

SP_CONFIGURE 'max server memory', 4096 RECONFIGURE GO

4)If the version is SQL Server 2005 Standard\Enterprise and physical memory is greater than 4GB - enable AWE. This needs to be associated with Lock Pages in Memory & Max server

memory.

5)Don't enable AWE for for servers with less than 4GB

6)If there is greater 16 GB - use /PAE only . Beacuse OS needs more than 1GB to manage allocation greater than 16GB

Also worth mentioning SQL Server 2000 Enterprise Edition, as there are still a significant amount of 2000 version SQL Servers in Production
4GB physical memory: /3GB (don't use AWE)
8GB physical memory: /3GB /PAE
16GB physical memory: /3GB /PAE
16GB + physical memory: /PAE

64 bit
1)/3GB and /PAE switch is not required

2)AWE not required on 64 bit

3)Lock Pages in Memory.yes or no? This will depend on different scenarios. The main benefit of turning it on is that the buffer pool buffer doesn't get paged out.

4)Set max server memory (leave 2GB for OS).Also consider other apps: Backup, antivirus

5) SQL Server 2005 Standard or Enterprise Editions - support for use of all available physical memory.Be careful! consider OS and other applications
SQL Server IA64 - using Windows Server 2003 or 2008 - supports up to 1TB phsical memory
SQL Server IA64 - using Windows Enterprise or DataCenter 2003 or 2008 - supports up to 2TB physical memory
SQL Server x64 - using Windows Server Standard or Web 2003/2008 - supports up to 32GB physical memory
SQL Server x64 - using Windows Enterprise or DataCenter 2003/2008 - supports up to 2TB physical memory

Republished from http://www.sqlserver-dba.com.