Home / server / Questions / 363134
In Process
Tom Gullen
Asked: 2012-02-24 11:33:16 +0800 CST

SQL Server 2008, should I set minimum server memory?

  • 772

We move from this server:

SQL Server Express R2 (has a 1CPU/1GB ram limit as far as I'm aware)
2GB Ram
2 core 2.1ghz

To this server:

SQL Server R2 Enterprise
8GB Ram
4 core 3.2ghz

Everything seems to be working fine. Our MDF and LDF files for the database combined are around 600mb (we only run 1 database).

I've looked at some memory options for the server and see that our database has a minimum server memory set to 0MB, and a maximum of 2147483647MB. Use AWE to allocate memory is off.

Would we benefit by adjusting these values at all? Out of the 8GB ram, we have 5.5gb free. We still have to install a web app on the server but that doesn't use much at all.

windows-server-2008 sql-server sql-server-2008

3 Answers

  1. I agree with leaving it alone for now. SQL will try to load as much into memory as it can, up to the amount defined in MAX SERVER MEMORY. Your 600MB db will obviously fit into memory in your current config. Your server should have plenty of memory leftover without having to tweak SQL memory settings. Watch it as your database grows, though. (I'm not sure what the velocity of your db growth is)

    Something I like to do is set SQL's max memory to (Physical Memory)-1.5GB just in case something in the instance wigs out. That way you still have some memory for the OS to use while you troubleshoot.

    SQL's min memory setting I leave alone. I personally have never had a reason to change it.

    As Mark said, if this is a dedicated server SQL box then that's good. If you run additional app level services you'll want to lower that max memory so that they'll play nicely together.

    • 5

  2. If this is a dedicated SQL server, then leave those settings alone.

    The only time I've ever had to change those settings was when a client was in an... unfortunate... situation of having a single server that needed to run a very heavy SQL instance along side another application, both of which were memory hungry. Then we set a max limit on SQL Server's RAM; if we didn't then the other app would just crash every other day or so.

    • 4

  3. I'd recommend setting a max server memory of about 5-6 Gigs. Min server memory shouldn't need to be set. If you are using a 64bit version of Windows and SQL then you don't need AWE. If you are using a 32bit version of Windows and SQL then you'll need AWE enabled.

    • 2