locked
Min/Max Memory Settings RRS feed

  • Question

  • What will be the advantages of having Min and Max memory setting same? We have 512GB of RAM and Max and Min memory
    setting are same as 450GB.We have not set the Lock Pages in Memory yet.

    There will be few lightweight applications installed in the same box.We dont want SQL Server to go down in any case.
    We can compromise on other applications which can do down. As per my understanding, till the point SQL Server doesnt
    reach the Min Server memory,Min memory setting doesnt make sense. Am I right?

    Will it be better if we set Lock Pages in Memory with same min/max memory settings? Or, will it be better to set min/max
    memory settings different and Lock Pages in Memory?

    Friday, July 1, 2011 2:12 PM

Answers

  • Well, you should lock pages in memory however you configure, in order to avoid flush to disc.

    The advantage of Min memory is that it will garuntee a minimum amount of memory will be available to the buffer pool, even though it will not all be allocated on start up, once reached, it will not drop below.

    When considering max mem, remember that this is just the amount for the buffer cache, and SQL can use more than this in total, because of other caches, etc.

    You would normally set them both to same value if you want a fixed amount of resource for SQL, but if you are managing other apps on the server (as you are) then you will often set min and max values differently, to give your apps more mem when SQL not using it.

    <edit>You are right about min memory not taking effect until this level is reached.</edit>

    There is a good article about this on MSDN http://msdn.microsoft.com/en-us/library/ms178067.aspx

    Hope this helps.


    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    Friday, July 1, 2011 2:30 PM