Memory configurations for SQL 2005 and SQL 2008

A lot of times I have seen SQL configurations at customer sites for which SQL Server could not use all available memory. For instance servers which uses only 2 GB of the available 8 GB of memory. This is something we need to avoid. In this blog I will advice which edition of SQL Server to buy of you need a new SQL Server.
 
SQL Server 2005\2008 can be used in different configurations.

1. SQL 2005\2008 X86 edition on X86 (32 bits) operating system  
2. SQL 2005\2008 X64 or IA 64 edition on X64 or IA 64 (64 bits) operating system
3. SQL 2005\2008 X86 edition on X64 (64 bits) operating system

Wrong memory configurations always occur with the SQL 2005\2008 X86 versions. Memory configurations with SQL 2005\2008 X86 versions needed more configuration than SQL 2005\2008 X64 or IA64 versions.

The 32 bit versions of Windows Server are limited to 4 GB. 2 GB is reserved for the operating system and 2 GB is reserved for applications like SQL Server. With a standard installation of the operating system SQL can only uses 2 GB of memory.

Image credit: teclasorg


How to configure more than 2 GB memory for SQL Server on X86 (32 bits) version:

• Add the /3GB switch to the BOOT.INI to force the operating system to use only 1 GB, while applications can use the remaining 3 GB. This switch is supported in Windows Server 2003 Standard, Enterprise and Datacenter editions.
• For servers with more than 4 GB of memory add the /PAE switch to the BOOT.INI and enable AWE. It allows the operating system to access physical memory beyond 4 GB. This switch is supported in Windows Server 2003 Enterprise and Datacenter editions.
• For servers with more than 16 GB you should add the /PAE switch to the BOOT.INI and enable AWE. Do not use the /3GB switch because the operating system need more than 1 GB of memory to handle and manage the allocation of 16 GB and above.

Before enabling AWE you must grant Lock Pages in Memory rights to the SQL Server account and set the 'max server memory' configuration option.
For more information about how to configure /3GB /PAE and to enable AWE see: Large memory support in Windows Server 2003 (X86 32 bits editions)

How to configure memory for SQL Server on X64 or IA 64 operating systems?
Windows Server 2003/2008 X64 support up to 32 GB of memory in Web and Standard editions.
Windows Server 2003/2008 X64 support up to 2 TB of memory in Enterprise and Datacenter editions.
Windows Server 2003 IA64 support up to 1 TB of memory.
Windows Server 2008 IA64 support up to 2 TB of memory.

All of these operating systems (X64 or IA64) use the available memory in the server without the need of /3GB, /PAE or AWE enabling. You only need to grant Lock Pages in Memory rights to the SQL Server account and set the 'max server memory' configuration option. Be aware that the AWE option is available in the X64 or IA 64 version but it will do nothing with it. It is a useless option on X64 and IA64.


How to configure memory for SQL Server X86 editions on X64 operating system?
It is possible but I do not know why you should do it. It has only disadvantages in comparison with a X64 edition of SQL Server. It will behave like a 32 bits system and need to map memory pages. This has a negative impact on performance. A 32 bits version of SQL Server on X64 operating system is the same as on an X86 operating system. You need to enable AWE and grant Lock Pages in Memory rights the SQL server account and set the 'max server memory' configuration option.

Conclusion:
64 bits versions of SQL Server on 64 bits operating systems are easier to configure, more scalable and performs better. Memory allocation on 64 bits system can be done directly without the need to map memory pages which is done in 32 bits operating systems. Today, all new hardware supports X64 installations. Therefore I have a simple advice when you need to invest in a new SQL Server:

For servers with more than 4 GB memory install the X64 or IA 64 version of SQL Server on a X64 or IA 64 operating system.