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.

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 (http://blog.SQLAuthority.com)

 

About these ads

6 thoughts on “SQL SERVER – Minimum Maximum Memory – Server Memory Options

  1. 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

    Like

  2. 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:

    http://msdn.microsoft.com/en-us/library/ms178067.aspx

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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s