Memory settings for multi-instance failover clustering

Question

When using multi-instance failover clustering (used to be called active-active) in SQL Server 2005 and 2008, I'd like to know what you configure the min and max server memory to.

For a two-node multi-instance cluster (with two instances, one on each node), the best-practice is to configure min server memory to 40-45% on each instance so that when the failover occurs the two instances don't end up with radically unbalanced amounts of memory, because the new instance may apply undue memory pressure to the already-running instance.

Although this is a best practice, I'd like to hear from people who have this situation - what do you configure the memory settings for the various instances? Do you make them equal or do you prioritize by the workload running on each instance? Do you make use of WSRM? Do you dynamically alter the memory settings based on post-failover cluster configuration?

Thanks!

PS I don't need info on how memory management works, or why the best practices exist - I know all that - I'm interested in what people are doing for real.

PPS Note, I'm not interested in SQL 2000 - it's memory manager didn't respond to that kind of memory pressure so best practice was to set max server memory to 50% each instance. Yuk.

Answer 1

Just did this for a client. They had 16 Gigs on each node, with each instance using 8 Gigs. One instance was starving for more RAM, so we upped it to 12, with alerting setup to watch for a SQL restart and send out a message. In the event of a failover due to hardware failing they'd manually lower it until the hardware was fixed.

Answer 2

We have a few multi-instance clusters in our environment and it is different for us depending upon the purpose of the instance. In our prod-prod clusters we have 32Gb on each node. We reserve 4 for non-SQL (O/S, tape backup processes, anti-virus, etc.) and then halve the RAM that is left (32-4 = 28/2 = 14Gb per node.) These are the max, we set a min of 8Gb for each.

In our prod-test clusters we bias towards prod (obviously). Prod receives a max setting of 20Gb, Test has 8Gb. Mins remain the same at 8Gb.

These values may be adjusted if we are testing actively and want to ensure our setting match between test and prod. We schedule out such changes in advance.

We are also sure to grant lock pages in memory, though do not enable awe as these are 64-bit boxes all around.

Answer 3

Tim, so you're saying you've built in a step into the clustering failover process that automatically resets the max the memory settings per instance during a failover situation?

I seem to recall that the way 64-bit was sold to me, was that memory would be dynamically released in this situation provided that you don't have locked pages in memory set.

As such am about to build a active/active W2K8, SQL2K8 enterprise cluster and I've been toying with testing this approach...comments?

Answer 4

I would also like to know what other people are doing. We have a 2 node Server 2008 R2 x64 cluster running two instances SQL Server 2008 R2 x64 standard edition, one instance on each node. Each instance has max memory set to 59 GB of the total 64 GB in each node. Locked pages is not set. For normal operation each instance hums along just fine. In a failover situation we have had issues. I have observed that with both instances running on one node, the system will page out the other instance's memory to the page file, and the two instances will start competing, usually filling up the 64 GB page file. In the situations where this has happened I had to manually go in and alter the max memory setting for each instance (down to 29 GB for each) so that they stop stepping on each other.

I was always under the impression that even with locked pages set, that SQL Server will still respond to requests from the OS to release memory so that it can be used elsewhere. I am considering setting this so that in a fail over situation the existing instance wont have its memory paged out and the new instance should slowly start getting memory as the other instance releases it.

I have also considered writing a small agent job that runs every couple minutes and detects which nodes the instances are running on. If it detects another instance running, it will automatically lower the max memory, and if it detects is the only one, it will turn it back up. This seems a little extreme though.