I'm a bit of a newbie to configuring SQL server, and have just had to set up a new Server 2008 x64 machine with SQL 2005 x64. Everything seems to be running well, however I have a question regarding the 'Maximum server memory' option.
Should this option be configured right out of the box? The server in question has 16GB of RAM at this stage. I have opted to turn on the lock memory pages option, however there appears to be plenty of conflicting advice online about whether or not to do this.
In my mind, if memory pages are being locked, then it seems important to set the maximum server memory option in order to prevent SQL from taking too much for it's own good. However in saying that, it appears that SQL will also release memory when needed; but this mechanism allows it to decide what to release rather than the OS paging out what it thinks best.
So at this stage I am thinking I should set the maximum server memory to approximately 12GB. It should be noted that the server is not at all memory bound at this point in time.
What are the best practices here? Thanks!
we prefer to set the max and min memory settings on our database servers, reserving some memory for the OS of course. For a 16Gb box we would probably also set the max and the min to be 12Gb, but we have dedicated database servers and that is not always the case for everyone.
i believe it is best practice to set your memory as you have suggested, right out of the box after you do your install and initial configurations.