SQL Server Min Server Memory

Question

using SQL 2008 R2 with SP2 with a server with 64 GB of RAM. I have set max server memory to 58GB considering SQL Server is limited to only database engine related service only. I have a question related to setting min server memory

If I set min server memory to 48 GB does that mean

a) If windows sends low memory notification to SQL -- SQL server will trims all its pool and try to maintain memory usage up to 48 GB of memory?

b) In case of aggressive working set trimming by windows -- would it leave SQL server's memory usage to 48 GB setting a "floor" for SQL's memory usage.

c) Are there any recommendation on min server memory?

Searching the web -- internet has plenty of information for max server memory but only few place I read (Brent Ozar blog for configuration best practices) to set min server memory to 50% of total available RAM on the server.

Answer 1

I would suggest you to leave MIN Server memory to DEFAULT.

Min server memory controls the minimum amount of Physical memory that sql server will try to keep committed. When the SQL Server service starts, it does not acquire all the memory configured in Min Server Memory but instead starts with only the minimum required, growing as necessary. Once memory usage has increased beyond the Min Server Memory setting, SQL Server won’t release any memory below that amount.

Bob Dorr explains this settings as :

Min Server Memory

Use the min server memory setting with care. This is a floor to SQL Server. Once committed memory to reach the min server memory setting SQL Server won't release memory below the mark. If you set max server memory to 59GB and min server memory to 56GB, but the server needs to back SQL Server down to 53GB SQL Server won't drop below 56GB. When you combine this setting with locked pages in memory the memory can't be paged. This can lead to unwanted performance behaviors and allocation failures.

Searching the web -- internet has plenty of information for max server memory

This is because, this settings is least tuned (people just set it to default), instead max memory is what is generally tuned as it is the "ceiling" for the buffer pool. A good value for max memory will ensure that windows and other processes runing on the server will have enough physical memory to perform their work without forcing sql server to trim.

Answer 2

Regarding your questions about "low memory notification" and "aggressive working set trimming", the SQL Server will try to trim back to the minimum amount of memory as defined. A couple of good things to know:

1. If you have a serious problem that requires lowering or raising the minimum, you can reconfigure the setting (without a restart being needed) and it will change up or down in fairly short order.
2. The actual SQL Server memory use, if my memory serves me, can be a little higher than the minimum value, because of some OS overhead.

I concur that for most SQL Servers 50% is probably a good minimum memory setting.

Answer 3

Starting from SQL Server 2019 CTP3.1 you could set up MIN and MAX memory during setup and utilize the Recommended values:

CTP 3.1 June 2019:

Set MIN and MAX server memory values at setup

During setup, you can set server memory values. Use the default values, the calculated recommended values, or manually specify your own values once you've chosen the Recommended option Server Memory Server Configuration Options

Answer 4

According to the documentation, SQL Server cannot release memory below the minimum.

Use min server memory to guarantee a minimum amount of memory available to the buffer pool of an instance of SQL Server. SQL Server will not immediately allocate the amount of memory specified in min server memory on startup. However, after memory usage has reached this value due to client load, SQL Server cannot free memory from the allocated buffer pool unless the value of min server memory is reduced.

As for recommendations for min memory, 50% should be a good base, but it depends on the server and what sort of load it has.

Answer 5

I see setting min value only if we know that every day there is a load/activity going to happen that will require high memory so that when activity happens SQL SERVER does not have to request more memory every day. (though it has to request first time).

answer to your question, "If windows sends low memory notification to SQL -- SQL server will trims all its pool and try to maintain memory usage up to 48 GB of memory ? "

SQL server will not go below min value if it has already acquired that. (regardless if it is in use right now) so this is an indication that MIN value set was not appropriate.

"In case of aggressive working set trimming by windows -- would it leave SQL server's memory usage to 48 GB setting a "floor" for SQL's memory usage?" I’m not sure of this. but my guess is it cannot force SQL server to reallocate acquired/committed memory. so far I haven't found a way to tell sql server release any unused memory to windows. i have seen the server becomes unresponsive due to the inappropriate MAX value for SQL server and then there is nothing left for server itself to operate.

"Are there any recommendation on min server memory?" As I said before unless you know how frequent server is going to need specific amount of memory no need to set anything higher than Default.