patternsqlModerate
SQL Server: Max memory same as Min memory?
Viewed 0 times
samesqlminmaxmemoryserver
Problem
I am currently skimming through Pro SQL Server 2019 Administration: A Guide for the Modern DBA and I have found one thing that somewhat confuses me.
In Chapter 5: CONFIGURING THE INSTANCE, the section on Min and Max Server Memory (pages 139-140) says:
In many environments, it is likely that you will want to provide the same value for both Min and Max Server Memory. This will avoid the overhead of SQL Server dynamically managing the amount of memory it has reserved.
If you have multiple instances, however, then dynamic memory management may be beneficial so that the instance with the heaviest workload at any given time can consume the most resources.
...Assuming that you have one instance and no other applications, such as SSIS packages, running on the server, you would normally set both the min and max memory setting to be the lowest value from the
However, the advice to "provide the same value for both Min and Max Server Memory" is in contradiction with the documentation, which says:
It isn't recommended to set max server memory (MB) and min server memory (MB) to be the same value, or near the same values.
In what situations would it be good idea to set Max Server Memory and Min Server Memory to be the same(ish) value?
Or am I misunderstanding something about the advice?
In Chapter 5: CONFIGURING THE INSTANCE, the section on Min and Max Server Memory (pages 139-140) says:
In many environments, it is likely that you will want to provide the same value for both Min and Max Server Memory. This will avoid the overhead of SQL Server dynamically managing the amount of memory it has reserved.
If you have multiple instances, however, then dynamic memory management may be beneficial so that the instance with the heaviest workload at any given time can consume the most resources.
...Assuming that you have one instance and no other applications, such as SSIS packages, running on the server, you would normally set both the min and max memory setting to be the lowest value from the
- RAM - 2 GB
- (RAM / 8 ) * 7
However, the advice to "provide the same value for both Min and Max Server Memory" is in contradiction with the documentation, which says:
It isn't recommended to set max server memory (MB) and min server memory (MB) to be the same value, or near the same values.
In what situations would it be good idea to set Max Server Memory and Min Server Memory to be the same(ish) value?
Or am I misunderstanding something about the advice?
Solution
this is stupid advice
That book might better serve you by keeping a coffee table level or something. When you set min server memory equal to max server memory, SQL Server can no longer use memory dynamically.
See: Dynamic memory management:
If the same value is specified for both min server memory and max
server memory (MB), then once the memory allocated to the SQL Server
Database Engine reaches that value, the SQL Server Database Engine
stops dynamically freeing and acquiring memory for the buffer pool.
It was a (bad) recommendation for VMs before advice around guest memory reservations and enabling Lock Pages In Memory were more commonly followed. Lock Pages In Memory has a similar overall effect, while still allowing for dynamic use of memory within the SQL Server process.
The buffer pool doesn't free any of the acquired memory until it
reaches the amount specified in min server memory. Once min server
memory is reached, the buffer pool then uses the standard algorithm to
acquire and free memory as needed. The only difference is that the
buffer pool never drops its memory allocation below the level
specified in min server memory, and never acquires more memory than
the level specified in max server memory (MB).
In other words, if you set both to the same value, memory consumers outside of the buffer pool may have quite a hard time. You don't want that, because other consumers do important things, like manage locks, and give queries memory to run.
That book might better serve you by keeping a coffee table level or something. When you set min server memory equal to max server memory, SQL Server can no longer use memory dynamically.
See: Dynamic memory management:
If the same value is specified for both min server memory and max
server memory (MB), then once the memory allocated to the SQL Server
Database Engine reaches that value, the SQL Server Database Engine
stops dynamically freeing and acquiring memory for the buffer pool.
It was a (bad) recommendation for VMs before advice around guest memory reservations and enabling Lock Pages In Memory were more commonly followed. Lock Pages In Memory has a similar overall effect, while still allowing for dynamic use of memory within the SQL Server process.
The buffer pool doesn't free any of the acquired memory until it
reaches the amount specified in min server memory. Once min server
memory is reached, the buffer pool then uses the standard algorithm to
acquire and free memory as needed. The only difference is that the
buffer pool never drops its memory allocation below the level
specified in min server memory, and never acquires more memory than
the level specified in max server memory (MB).
In other words, if you set both to the same value, memory consumers outside of the buffer pool may have quite a hard time. You don't want that, because other consumers do important things, like manage locks, and give queries memory to run.
Context
StackExchange Database Administrators Q#335895, answer score: 15
Revisions (0)
No revisions yet.