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?
Best Answer
In my opinion you're not leaving near enough for the operating system: on a 32GB system, I'd probably leave 4GB or so. Remember that local logins, SQL jobs and SSIS packages, and 3rd-party backup software and the like all use memory outside the allocated space for the SQL engine itself. If I know there are memory-hogging SSIS packages run regularly, I'll leave even more memory for the OS.
How you split the remaining between the 3 instances is up to you. There is no (serious) risk of impact by changing these on the fly, they don't even require a SQL restart to take effect.
Don't worry about a minor difference in your planned max size vs what you see in your graph there. SQL is a hog, and will use as much memory as you give it. If you reduce the max, it will have to release some memory, but it's just giving up a portion of its local data cache. It may have to go back to disk slightly sooner for some queries, but I doubt you'll notice a difference.
EDIT: After some discussion in the comments, it doesn't appear a SQL restart is necessary, even when the MaxMem is reduced below the currently used amount.