SQL: Max Server Memory Allocation

I have three SQL database instances on a cloud server: A, B and C and the Server Installed Physical memory (RAM) is 32GB. The size of A (data+log file) is 44GB and B (data+log file) is 41GB. We ignore C since it has no databases in it and max memory set to 500MB.

Both instances A & B has it's memory set to the default size: 2147483647 MB. Although we have had no issues this is definitely not practical.

After some research, based on database activity, i decided that instance A memory allocation should be set to 20GB and B set to 10GB. In total 30/32 of the max physical memory in the system. Below shows the actual memory consumption. I am afraid if i set the max memory it will slow down response time. Is this configuration optimal in setting the SQL Max Server Memory for such databases and how can i simulate before applying?

A general recommendation by Jonathan (from SQLSkills)


reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM.

You can use powershell dbatools Test-DbaMaxMemory to calculate for you. The dbatools has set-DbaMaxMemory to set the max memory for you across all your servers.

I would go with Test-DbaMaxMemory to check the recommendations first and once you are comfortable, use set to set it across all your servers.

StackExchange Database Administrators Q#173013, answer score: 4