Tuesday, December 8, 2009

SQL Server 2005/2008 Maximum Server Memory - What should it be?

While questions like this don't have black or white answers there is some simple guidance here . . .

You probably want to change it from the default configuration of 2,147,483,647 MB which is over 2 terabytes. I believe the idea here is to allow SQL Server to use as much memory as it wants. That's all well and good until it begins to starve the OS of memory which will in turn bring SQL Server to its knees.

There are implications related to OS editions, SQL Server editions, 32 versus 64 bit, AWE, etc. I won't get into that here but I will say that it's a good idea to set a maximum value and leave at least a half of a GB and if possible 1+ GB for the operating system.

For example, if you're on a budget and running 32 bit Windows Server 2003 Enterprise with 4GB of RAM (the max ram allowed on this OS) I would suggest setting this value to somewhere between 3,072 – 3,584 MB (3 - 3.5 GB).

Here are some recommendations from Glenn Berry's blog.

No comments:

Post a Comment