0

Possible Duplicate:
Can you help me with my capacity planning?

Currently our SQL server 2008 machine has 24 gb of memory.

After analyzing some reports (PAL) and performance counters, I came to the conclusion that the server could use some more memory.

What is a good way to determine how much memory I should put in the server? Is there any rule of thumb or anything I can look at?

Why would i go to 48, or 72, or 128..

0

5 Answers 5

2

Put in as much as you can afford. Seriously. RAM is cheap.

What's the most your server can address? (You'll need to check with the manufacturer to find this, probably).

Put in the largest amount that the server can handle, and that your budget will extend to.

Without knowing more about the use-case and load of your server, how big your databases are, what the read/write load is like, and so on, it's difficult to give a more accurate answer.

1
  • The most it can address is 194 gb, after we upgrade from 2008 standard to Enterprise. But I don't think my boss will be happy if I say it should be 194, just because RAM is cheap. I know we need more than we have now, but why should i advice 64 or 128. There should be more things I can look at right? May 10, 2012 at 9:38
1

Please bear in mind that the OS might have restrictions. If you want to utilize more than 32 GB Ram you will have to switch to windows server enterprise edition.

http://msdn.microsoft.com/en-us/library/windows/desktop/aa366778%28v=vs.85%29.aspx

1
  • Good point, its a Windows Server 2008 R2 Standard right now, but we can upgrade it to Enterprise, thats not a problem. May 10, 2012 at 9:01
0

It depends on the exact make and model of your server plus what memory you have in there now, the reason is that in some cases adding memory can slow down the whole memory subsystem. So while have more memory is always very useful in certain cases you can affect this one individual factor.

So let us know this info and we'll help you out.

1
  • We can go to 194 GB. Only thing i have to say is how much we need, some other guy here will look at what type it should be etc. May 10, 2012 at 9:42
0

I think with such a server we are speaking of ECC memory, so it's not as cheap as regular RAM. How much is needed cannot be derived from the info you have given. You'd need to look into the RAM usage and if the swap file is used.

Generally one could say that the best performance is achieved when the whole database fits into the RAM. If that is not possible then the biggest and most-used tables should fit. Or those which don't have an index (which generally is bad practice and should be avoided) or are searched without usage of the index ("table scanning") row by row.

0

SQL Server will always want to take up as much memory as it needs, ideally to the point that it has the entire database cached in memory (writing to disk takes unnecessary time), so look at the size of your databases first (this might help, although you might already have taken this into account).

A rule of thumb I've always gone by is take 4gb off for the OS if RAM < 32gb, 8gb off if it's got more than 32gb, however your mileage will vary. There was an article posted on Brentozar.com a while back (I can't find the link at the mo) which had a detailed breakdown of how SQL server uses memory which might help.

If you want to splash out, put in as much as you can afford but remember to take into account the change of OS version if you want to use more than 32gb (as previously mentioned).

Not the answer you're looking for? Browse other questions tagged .