What could be the issue if we set up SQL Server instance minimum and maximum memory range equal .

Question

Hi ,

On one of our database server, application vendor is suggesting setting up SQL instance minimum and maximum memory value as equal. We have a SQL server where its RAM is 128GB we have set maximum memory as 80% of OS memory. can I have expert advice whether we can set that and what could be the disadvantage if we set up as equal.

Answer 1

Setting both the minimum and maximum memory for a SQL Server instance to the same value can lead to several disadvantages. When you set the min server memory (MB) and max server memory (MB) to be equal, SQL Server will not dynamically adjust its memory allocation based on workload demands. This means that once the SQL Server instance reaches that memory limit, it will not free up any memory even if the workload decreases, which can lead to inefficient memory usage and potential performance issues.

Additionally, if the memory allocation is set too low, it can prevent SQL Server from handling peak workloads effectively, leading to performance degradation. Conversely, if the memory allocation is set too high, it can cause SQL Server to compete for memory with other applications on the server, which may lead to memory pressure and further performance issues.

In your case, with a server RAM of 128GB and a maximum memory setting of 80% of the OS memory, setting the minimum and maximum memory to the same value could restrict SQL Server's ability to adapt to varying workloads, potentially resulting in performance bottlenecks.

It is generally recommended to set max server memory (MB) to a value that allows SQL Server to use a significant portion of available memory while still leaving enough for the operating system and other applications. Setting min server memory (MB) can help ensure that SQL Server retains a minimum amount of memory, but it should not be the same as max server memory (MB) to allow for dynamic memory management.

---

References:

• Server memory configuration options
• Memory management architecture guide

Answer 2

That is not very good advice from your vendor. Setting max server memory can be a good idea to give the operating system space to breathe and leave room for any other applications, for instance file-copy operations.

On the other hand, I rarely touch min server memory. It many case, it does not matter that lot, because once SQL Server has allocated a certain amount of memory, it will stick to it, until the OS signals memory pressure, which might be a rare event on a server which is dedicated to SQL Server.

But if there actually is memory pressure, and min server memory is set too high, it can be a very unpleasant experience. (Which I have actually experienced. On a server with 64 GB of RAM, there were two instances that both had max and min memory set to 62 GB. This was a test server, and the intention was that the two instance would not be running simultaneously. But on one occasion it happen, and, boy, were things slow!)