Allow SQL Server to use more RAM

On a default 32 bit Windows Server setup, 2 GB of RAM is the most that a SQL Server instance can use. Let’s say a server has 10 GB of RAM that is running nothing but the OS and SQL. The operating system will allocate 2 GB to the OS and 2 GB for SQL. That means your server is wasting 6 GB of RAM.

We can change that by using AWE. Microsoft SQL Server uses the Microsoft Windows Address Windowing Extensions (AWE) API to support very large amounts of physical memory. SQL Server can access up to 64 gigabytes (GB) of memory on Microsoft Windows 2000 Server and Microsoft Windows Server 2003.

Support for AWE is available in SQL Server Enterprise, Standard, and Developer editions and only applies to 32-bit versions of SQL Server 2005 and SQL Sever 2008.

We won’t go in details about how it works; MSDN has lots of articles about it.
Here are the four steps needed to be able to use AWE on your SQL Server.
1. Add the /PAE (Physical Address Extension) switch to boot.ini to allow Windows 2000/2003 to access more than 4 GB of memory.
2. Add /3GB switch to boot.ini if server has more than 4GB but less than or equal to 16GB RAM.
3. Grant “Lock Pages in Memory” to the user account that runs the SQL Server process.
4. Check the checkbox in the SQL Instance properties to allow AWE.

To edit the boot.ini file on a Windows 2003 server, open Control Panel, open System, then click Advanced, then settings for the Startup and Recovery, then Edit under System startup. Then you will see a file whose contents are similar to the following:

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="WindowsServer 2003, Standard" /fastdetect /NoExecute=OptOut))

The /PAE switch or /3GB switch are added directly after operating systems line. Below is an example with both the switches:

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="WindowsServer 2003, Standard" /fastdetect /NoExecute=OptOut /PAE /3GB))

Now we needed to change the “Lock Pages in Memory” registry setting to not use the swap file but use physical memory. Here are the steps :
1. On the Start menu, click Run. In the Open box, type gpedit.msc.
2. The Group Policy dialog box opens.
3. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
4. Expand Security Settings, and then expand Local Policies.
5. Select the User Rights Assignment folder.
6. The policies will be displayed in the details pane.
7. In the pane, double-click Lock pages in memory.
8. In the Local Security Policy Setting dialog box, click Add.
9. In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.

Finally to enable AWE:
1. In SQL Server Management Studio, right-click a server and select Properties.
2. Click the Memory node.
3. Under Server memory options, select Use AWE to allocate memory.

You can use the following query to check if your AWE settings are working fine or not.

SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Target Server Memory (KB)','Total Server Memory (KB)'))

This will show you the amount of memory SQL Server is aiming to use (Target Server Memory), and how much it is currently using (Total Server Memory). If AWE is not enabled, these values won’t go above 1740800 KB i.e. 1700 MB.

Advertisements

About Amit Singh

Software engineer; crazy about music, astronomy and radio controlled aircrafts. Big science/technology buff!
This entry was posted in Programming and tagged , , , , , , , , . Bookmark the permalink.

One Response to Allow SQL Server to use more RAM

  1. Pingback: Allow SQL Server to use more RAM | IISservertricks.com BY Sameer Kamble

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s