March 3, 2010 at 8:49 pm
I've got a server with 40GB of RAM that is going to be a dedicated SQL 2008 server.
I'm just wondering with this large amount of memory, what kind of recommendations would be for the MinMemory and MaxMemory settings? Only running the SQL Engine and SQL Agent.
March 3, 2010 at 10:16 pm
It very much depends upon whether you are going to be running things like replication and how many connections you expect to have, whether you have SSIS packages executing etc.
Working under the assumption that you are running 64bit SQL on 64bit OS, have the Enterprise Edition and have lock pages in memory enabled I would (bear in mind this is just me) set a minimum of 20GB with a maximum of 30GB and then capture perfmon data on the system and see how it looks so you can tune it from there. 10GB for the OS might seem like overkill, but it's a solid starting point and leaves you plenty of wiggle room until you have the stats to tighten it up.
March 3, 2010 at 11:32 pm
Thanks for that Nicholas, very helpful. You're right re 64 OS and SQL.
There will be mirroring occurring, but that's it in terms of advanced features. There will be no SSIS packages running.
Out of interest, what's the advantage of saying start with 20GB and go to a max of 30GB rather than start at 0 for minimum and go to a maximum of 30GB?
March 3, 2010 at 11:48 pm
since its a dedicated SQL box so you can rather you should allocate\reserve good amount of memory to SQL so that sql operates optimally & provides good performance.
otherwise SQL has to make request to OS for memory allocation which is not at all required for a dedicated box.
recommendations:-
Min Mem: atleast 20-25GB
Max Mem: 30-35
However, you can allocate upto 38GBs (depending upon other parameters and 3rd party agents running on your SQL BOX)
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
March 4, 2010 at 5:30 am
JamesNZ (3/3/2010)
Out of interest, what's the advantage of saying start with 20GB and go to a max of 30GB rather than start at 0 for minimum and go to a maximum of 30GB?
If you set a minimum amount of memory (20Gb in this example), then SQL Server will not release that 20Gb to other processes, even if there are memory pressures and the Operating System asks SQL Server to reduce the amount of memory it has earmarked. That means that the OS may be "starved" of memory, thus needing to page to disk some of SQL Server's memory. By providing a maximum memory value of 30Gb, you let the OS have 10Gb for other processes, which should reduce or eliminate the need to page SQL's memory to disk.
March 7, 2010 at 6:25 pm
Thanks for that, really helpful.
Have set MinMemory to 20000 and MaxMemory to 30000
March 8, 2010 at 6:57 am
If you're running Enterprise Edition then don't forget to set Lock Pages in Memory.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply