I am trying to find the best possible solution on how to set min and max server memory,
but so far I can't find the good source and breaking my head here.
So let's say I have 4 node cluster running 1 sql instance on each node.
Each sql instance has about the same workload.
Each node has 72GB of memory. Currently the values for each instance
are set to 0(min) and 64GB(max) leaving 8GB to OS.
What I would like to do is to set the values the way that if for example 3 nodes are failed and all 4 instances run on 1 node the memory configuration should be handled automatically.
Or sometimes just 1 instance is failing over to another node and we need to set the max server memory accordingly for 2 instances running on the same node.
The problem is that I can split the memory (64GB) between 4 instances and set the max setting let's say to 16GB for each instance,but that means that each instance can't use more than 16GB out of 64GB if an instance is running on its dedicated node.
The questions are:
1) What is the best option to handle this scenario?
2) Is there an advantage of modifying minimum server memory in this case from 0 to something else?
Thanks so much in advance