SQL Server with Mr. Denny

Feb 11 2008   8:00AM GMT

Getting those memory setting in SQL Server just right.

Denny Cherry Denny Cherry Profile: Denny Cherry

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. 

  1. Give Windows between .5 and 1 Gig of memory and give SQL the rest.
  2. 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

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: