Getting more memory for SQL server

If you´re stuck on a 32Bit system and need more memory recources for your database there´s the option to use either the /3GB switch in boot.ini or /PAE together with AWE. Here´s the procedure to make this work.

First, here are the memory limitations for a 32Bit windows server 2003:

• Windows Server 2003, Standard Edition supports physical memory up to 4 GB.
• Windows Server 2003, Enterprise Edition supports physical memory up to 32 GB.
• Windows Server 2003, Datacenter Edition supports physical memory up to 64 GB.

If you use windows server 2003 standard edition and have 4GB in your box you can use the /3GB switch to get 1GB extra for sqlserver.

1. Edit the boot.ini file and add /3GB to the following string: multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Standard" /fastdetect /3GB /NoExecute=OptOut
2. Open up sqlserver enterprise manager and rightclick the server and click properties.
3. Go to the memory tab and tick the checkbox "lock pages in memory" and then click ok.
4. After this you need to allow the user that is running sqlserver.exe to lock pages into the memory. To do it do the following:
5. On the Start menu, click Run. In the Open box, type gpedit.msc.
   The Group Policy dialog box opens.
   On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
   Expand Security Settings, and then expand Local Policies.
   Select the User Rights Assignment folder.
   The policies will be displayed in the details pane.
   In the pane, double-click Lock pages in memory.
   In the Local Security Policy Setting dialog box, click Add.
   In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.
6. When you have done this reboot the server and enjoy the extra 1Gb you got for sqlserver.

If you´re using Windows Server 2003, Enterprise Edition or Datacenter Edition you can take advantage of AWE, and for activating on a box with 8GB memory you do the following:

1. Instead of adding the /3GB switch you add /PAE to boot.ini
2. Open up sqlserver enterprise manager and rightclick the server and click properties.
   Go to the memory tab and tick the checkbox "lock pages in memory" and then click ok.
   
3. After this you need to allow the user that is running sqlserver.exe to lock pages into the memory. To do it do the following:
   On the Start menu, click Run. In the Open box, type gpedit.msc. The Group Policy dialog box opens. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.Expand Security Settings, and then expand Local Policies.Select the User Rights Assignment folder. The policies will be displayed in the details pane. In the pane, double-click Lock pages in memory.In the Local Security Policy Setting dialog box, click Add.In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.
4. Now we will configure the AWE with the following code which you run from a query window in enterprise manager:

First we show the advanced options

sp_configure 'show advanced options', 1
RECONFIGURE
GO

Then we enable AWE

sp_configure 'awe enabled', 1
RECONFIGURE
GO

Then we set the min and max values so we leave some memory for the operating system.

sp_configure 'min server memory', 1024
RECONFIGURE
GO
sp_configure 'max server memory', 7168
RECONFIGURE
GO

After this you reboot the server and enjoy the extra memory and hopefully your sqlserver will run a lot smoother with the extra memory available.

Note. you should read the following articles before making any changes to your production environment and not take my word for it. Be careful!

Regards
Stefan

Using AWE
Enabling AWE Memory for SQL Server
How to: Enable the Lock Pages in Memory Option (Windows)
Enabling Memory Support for Over 4 GB of Physical Memory