7

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

  • 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?

Improve this question

1 Answer 1

Reset to default
15

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.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.