What value to set for MAX and MIN memory of SQL Server hosted on Bare Metal [duplicate]

Question

Within AWS, Many of our Database servers are being migrated from EC2 Instance to Bare Metal. (Correct me, If I am wrong) In Bare metal, there are two memory limits available for the VM hosted on Bare metal.

1. Current total memory that is assigned to the VM hosted on Bare metal. (105 as per below screenshot)
2. Max memory that can be assigned to VM by the Bare metal host in case more memory is needed by VM (Max Memory Available). (128 GB as per below screenshot)

I have couple of questions in my mind:

1. What MAX and MIN memory should be assigned to SQL Server? what things should be considered before setting the values?
2. Will there be any other behavior change in SQL server because of bare metal?
3. Will the dynamic memory allocation happen inside SQL Server as well? I mean that if the scenario occurs where SQL Server no longer need memory, will it release it back to VM and eventually back to host?? As we all know that SQL Server is a memory hog, it does not give memory back to OS unless there is memory pressure on the OS.

Any help/inputs would be appreciated.

Answer 1

1. There are recommended settings for MIN and MAX memory. Refer to this documentation: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15

Be aware that the min and max memory settings are for the buffer pool only. SQL Server uses more memory then for the buffer pool alone. This can help: https://www.sqlshack.com/monitoring-memory-clerk-and-buffer-pool-allocations-in-sql-server/

We have some automation that uses dbotools.io CMDlets to determine this for our SQL Servers, so we don't have to do the thinking. :-P

2. No, there will not, unless you use dynamic memory for a VM. And that can pack some really dark, rainy, thundering clouds over your SQL Server. My preference is to get as less rain and thunder on my SQL Servers.

3. SQL Server dynamically manages it's buffer pool memory and can give memory back to the OS. For example if you set the max mem to unlimited and SQL has taken every bit of mem it can take and you log into the box, then you will see a memory drop if you look at the memory counters for SQL. SQL gives memory back to the OS if it needs it. This is why i never log in on a SQL server unless i really have to.

You can prevent that behaviour by the way with a feature called 'Lock Pages in Memory', but that is no recommended in most cases, because it can cause problems where your OS runs out of memory. SQL relies heavily on memory for performance so you don't want something external to mess with your memory. SQL Server is very sensitive to that.

I found another article here on on dba.stackexchange btw: Hyper-V Dynamic Memory and SQL Server