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 (https://blog.sqlauthority.com)
10 Comments. Leave new
Hi Pinal,
Really worthful information from ur blog!!!
Thank You!!!
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:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-2017
I see the setting is important when “Running Multiple Instances of SQL Server
“.
But my question (above) is for a single instance.
@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.
worthful information, thanks a lot.
Sure. My pleasure.