Multi-Instance Windows Failover Cluster - Min / Max Memory Allocation Dilemma

Question

We run numerous 2 node SQL Server 2008 R2 Failover Clusters in Windows Server 2008 R2 on a VMWare eSXi virtual platform. Each cluster hosts 3-4 SQL instances with varying degrees of memory requirement.

At present we use Max memory settings in SQL to allow for all 3 or 4 instances (with the greater of 10% or 4GB overhead for the OS) to run on a single node at any one time without impacting performance. I believe this is the widely accepted method for memory allocation on cluster if you're not using Aaron Bertrand's handy script for dynamic memory allocation, but does inevitably lead to wasted resource.

I recently got around to reading an Idera Whitepaper "Top 5 SQL Server Cluster Setup Mistakes" written by Kendra Little, who I massively respect, which implies that this approach may not actually be optimal. The whitepaper references a Microsoft article: https://msdn.microsoft.com/en-us/library/ms178067(v=sql.105).aspx. Specifically to this scenario:

Use min server memory to control memory usage. Establish minimum settings for each instance, so that the sum of these minimums is 1-2 GB less than the total physical memory on your machine. Again, you may establish these minimums proportionately to the expected load of that instance. This approach has the advantage that if not all instances are running at the same time, the ones that are running can use the remaining free memory. This approach is also useful when there is another memory-intensive process on the computer, since it would insure that SQL Server would at least get a reasonable amount of memory. The drawback is that when a new instance (or any other process) starts, it may take some time for the running instances to release memory, especially if they must write modified pages back to their databases to do so. You may also need to increase the size of your paging file significantly.

So, my question really, does anyone have any experience of this? I feel it might be a better idea to optimize memory usage in my environment as a minimum per node memory setting that would safely allow for all instance plus OS would then allow SQL more access to the otherwise unallocated RAM on the VM.

An example of what I mean:

Current Config: 2 x 32GB RAM VMs

VM1:

SQL Instance A - 0 Min Mem / 6GB Max Mem

SQL Instance B - 0 Min Mem / 8GB Max Mem

VM2:

SQL Instance A - 0 Min Mem / 6GB Max Mem

SQL Instance B - 0 Min Mem / 8GB Max Mem

Total Cluster Usage: 28GB Max SQL Memory

Suggested Config:

VM1:

SQL Instance A - 6GB Min Mem / Default Max Mem

SQL Instance B - 8GB Min Mem / Default Max Mem

VM2:

SQL Instance A - 6GB Min Mem / Default Max Mem

SQL Instance B - 8GB Min Mem / Default Max Mem

Total Cluster Usage: 28GB Min SQL Memory

Any thoughts and comments would be gratefully received.

• Kin Shah: from the excerpt - This approach has the advantage that if not all instances are running at the same time. I would suggest you to leave MIN MEMORY as default unless you are really encountering issues as once acquired, the buffer pool will not drop the memory below the level specified in the min server memory .. See my answer here. You should monitor the sys.dm_os_ring_buffers - system health session for memory pressure.
• James Anderson: It does seem like wasted memory when everything is running well but if a server dies, do you really want your users complaining that the performance is not as good as normal while you are dealing with the down server?
• Darren: Sorry Kin, I should have pre-faced this with the fact I'm not encountering any memory pressure in the environment. I'm simply trying to maximise the use of the allocated memory.
• Darren: James - I'm not sure that the performance will be impacted in that scenario. By having everything running on the same cluster node under min memory settings equivalent to the existing max settings then at most I potentially lose some of the non-buffer pool memory allocation, right?
• Sean Gallardy: It comes down to SLAs and how much you want to be engaged on the issue. If you want to maximize your NON-FAILURE environment then go ahead and set the min very high and the max allow it to use whatever it wants. If you want to have a smoother FAILURE experience, set your min to an acceptable level and either cap your max to play nicely or don't cap the max - depending on how much extra room you want to play with. However, one thing I wouldn't let happen is setting the mins to 0 and having competing workloads thrashing your buffer pool.