SQL SERVER – Minimum Maximum Memory – Server Memory Options

I was recently reading about SQL Server Memory Options over here. While reading this one line really caught my attention is minimum value allowed for maximum memory options.

The default setting for min server memory is 0, and the default setting for max server memory is 2147483647. The minimum amount of memory you can specify for max server memory is 16 megabytes (MB).

This was very interesting to me as I was not familiar with this details. This was one interesting detail for me. In reality I will never set up my max server memory to 16 MB, it will be right out suicide for the server looking at current systems capabilities.SQL SERVER - Minimum Maximum Memory - Server Memory Options memsetting

If you try to reset this to lower than 16 MB, SQL Server will automatically make it 16 MB and will not take lower number.

This information was new to me. How about you?

Reference: Pinal Dave (https://blog.sqlauthority.com)


SQL Memory
Previous Post
SQL SERVER – List of all the Views from Database
Next Post
SQLAuthority News – New Banner of Blog

Related Posts

10 Comments. Leave new

  • Manohar Pattem
    November 2, 2010 6:41 pm

    Hi Pinal,
    Really worthful information from ur blog!!!

    Thank You!!!

  • ronnie peterson
    November 3, 2010 11:49 pm

    Hello, how cant I avoid table spool in a execution plan ?
    thank you

  • Is it ok, if we change the max server memory when the production server is running ?

  • Hi Pinal,

    Should I configure min and max memory setting on SQL Server 2005.

    OS : 2008 R2
    SQL Server 2005 ED

    Thanks & Regards
    Jayant Dass

  • Landed here while trying to figure out what impacts changing setting for max server memory could have.
    2147483647MB is a lot. Why is it set there?
    Wouldn’t it be better to set a limit that is below the system memory? To make sure you always have some margin and not “steal” memory?
    Or should I just ignore the fact that MS has given me the option to change it? Or is this one of those “change at your own risk” things?

    more info:

    I see the setting is important when “Running Multiple Instances of SQL Server
    But my question (above) is for a single instance.

  • Jack Whittaker
    January 23, 2013 4:38 pm

    @NNM – 2147483647MB works out at 2048Tb which I think is the theoretical maximum that can be addressed by the processor.

  • Hi Pinal,

    I need to know that what is the best method to calculate max memory setting for server??What are the pros and cons of keeping max memory setting with default values i.e min memory:-0 and max memory:-2147483647MB.

    • You need to monitor memory usage and decide. if there is only one instance of SQL, I generally leave 10%-20% for the OS and put 80%-90% as max memory. This rule may not be followed universally but it has worked for me for many customers.


Leave a Reply