How to enable more than 2gb of memory with MSSQL

  • By default, Microsoft SQL Server sees only 2 Gb of RAM, even if you have installed more memory.
  • First Microsoft SQL Server relies on the memory supplied by the OS, Windows Server 2000 or Windows Server 2003.
  • By default, 32 bit OS can only see 4Gb of RAM. This is a limitation of the 32 bit environment.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008
  • Microsoft Windows Server 2003, Standard Edition only supports up to 4Gb of physical memory.
  • Microsoft Windows Server 2003, Enterprise Edition only supports up to 32Gb of physical memory.
  • Microsoft Windows Server 2003, Datacenter Edition only supports up to 64Gb of physical memory.
  • None of this applies to the 64 versions.

Enabling all the memory with the Windows Server

  • For the OS to see more than 4 Gb of RAM, you will need to enable the PAE extensions on Windows.
  • It must done on BOOT.INI
  1. [boot loader]
  2. timeout=30
  3. DEFAULT=multi(0)DISK(0)rdisk(0)partition(2)\WINDOWS
  4. [operating systems]
  5. multi(0)DISK(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE
  • The CPU must support the PAE extensions (all modern CPUs do).
  • With Microsoft Windows Server 2003, PAE is automatically enabled only if the server is using hot-add memory devices.
  • PAE changes the memory addressability limitation of 32-bit system from 4Gb to 64Gb.

Enabling the memory with the Microsoft SQL Server

You will need to enable AWE on the SQL Server with:

  1. SP_CONFIGURE 'show advanced options', 1
  2. RECONFIGURE
  3. GO
  4. SP_CONFIGURE 'awe enabled', 1
  5. RECONFIGURE
  6. GO
  7. SP_CONFIGURE 'max server memory', 7168
  8. RECONFIGURE
  9. GO
  • This allocates up to 7Gb of RAM for SQL Server.