jump to navigation

Getting SQL Server 2005 to use 32GB of RAM on Windows 2003 October 7, 2009

Posted by Sean Welsh in sql server 2005.

Had a bit of drama at work that is worth a post. We did a hardware swap of our database to some new machines. Alas, the memory had not been configured correctly so SQL Server 2005 / Windows 2003 was only using about 4GB of RAM. Not good when our 8-core servers were struggling with 16GB RAM to migrate to 12-core servers with only 4GB RAM effectively!

Definitely a Schooling of the Hard Knock variety. It took us a while to figure out exactly what was wrong.

The essence of what to do.

  1. Create a user for SQL Server to run as, say ‘sqladmin
  2. Make sqladmin a member of all SQL Server groups in Windows
  3. Configure SQL Server to run as a particular user in Services
  4. Edit boot.ini so that the /pae flag is present
  5. Give sqladmin the ability to ‘lock pages in memory’
  6. Tick the AWE flag in SQL Server Management Studio – Server – Properties – Memory
  7. Reboot
  8. Check the system will use more than more 4GB of RAM by running some monster queries or a load test. Monitor in Task Manager or Perfmon until you see the used RAM go past 4GB.

Now that our servers are on 12-core / 32GB RAM with a SAN for the disks, they are humming along nicely.



No comments yet — be the first.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: