When using 64bit SQL Server getting the memory settings right is pretty easy. Simply set the maximum to what you want and you are good to go.
Getting them right in the 32bit versions of SQL Server is a bit harder. You have to deal with the OS level of enabling the Physical Address Extensions (PAE) and the 3GB switches. You then need to enable AWE within SQL Server and then set the max memory setting.
If you are using Windows 2003 SP1 or later PAE will enable for you automatically. The /3GB switch however won’t. Since I have to add the /3GB switch I like to add the /PAE switch in there as well. My theory is why make Windows decide to do something automatically when I can simply override the logic and turn it on every time, especially when it’s something that I’m going to want enabled every time the server boots.
Now as to the max memory setting for SQL Server… There are pretty much two prevailing schools of thought.
- Give Windows between .5 and 1 Gig of memory and give SQL the rest.
- Give SQL 75% of the physical memory and leave the rest for Windows.
I’ve tried both and both seam to work fine. If you have less memory to work with you will probably want to stick to option 1. When you start working with huge amounts of RAM (64 Gigs plus) is when Option 2 starts to look more workable.
These rules obviously all start to change when you have more than one instance installed as you need to balance your max memory between the instances.
If you are using less then 2 Gigs of RAM for the instance don’t enable AWE on the instance. I’ve seen it lead to SQL Server acting strangely and performing very strangely. When setting your max memory setting for more than one instance don’t forget to add up the max memory for all the instances and make sure to leave Windows room to work with or your server will suffer.
Don’t forget about my post on setting the min server memory setting in SQL Server.
Denny