1

I have looked around in the dba stack exchange and have not being able to find an answer to this one, so here it goes my concern. Hopefully somebody has done some research on it and can provide an authoritative answer.

We have an Active/Active SQL Server cluster running on 2 nodes. SQLA is the default node for one instance with the other (SQLB) as a fail-over and viceversa (SQLB default node for the other instance with SQLA as a fail-over). These servers are running on physical boxes with 64GB of RAM

We have to configure "Max Server Memory" and there are 2 theories around here

  • First Theory: Follow Jonathan Kehayias pretty much universally accepted approach that, in a nutshell, goes like this "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." This will leave each box with a Max Server Memory setting of 53 GB
  • Second Theory: We need to cap Max Server Memory to a value that will allow for the secondary node to take care of the other instance if a failover occurs. This limits each instance to much smaller value (around 30 GB so you have 30 GB x 2 + 4GB for the OS = 64GB)

On a first look, the first theory would be desireable as it gives each instance a way larger memory space, but some people (me included) are not sure what happens if you fail over and then you have 2 instances on the same box, each with a Max Server Memory of 53 GB of RAM.

Do you know what is the way to go with this scenario? is any of these two approaches right? or do you have valid reasons that a third approach should be used instead? do you know of any serious blog-post or white-paper to back it up with evidence?

Thank you all!

  • I would go with the second theory since you should always plan for a worst case. One alternative that I've heard used is to have a watchdog service on each box and when it detects that both instances are on the same machine, it issues the TSQL that changes their max memory respectively. And then back when it detects that is no longer the case. – Jonathan Fite Mar 16 '17 at 15:32
  • 1
    There's not going to be a one size fits all approach, here. Is one instance 'more important' than the other? How much data is currently in RAM on each? How much data are you storing on each? In a scenario where both instances are on one server, do you expect normal performance? It could be that this was poorly planned and you need more RAM to accommodate. – Erik Darling Mar 16 '17 at 15:35
1

Please note this is called multi instance cluster not active active actually. Your concern is correct about what would happen when both nodes are on same node, in that case you need a dynamic script would identify a failover and adjust SQL Server max server memory accordingly.

Let us focus on how to set max server memory correctly when both nodes are running on respective nodes for that please see my answer on SE thread What is Sensible way to calculate max server memory.

Now for scenario when failover happens and both instances are on same node. You have to use scripts from follwoing resources.

Now when both instances are on same node you would have to evenly distribute memory leaving enough for OS. In that scenario 29 G for each instance and 6 G for OS is what I would say would be good value. Because you have 2 instances running leaving few more gigs for OS would actually help.

Also note you should immediately come out of this situation as both SQL Server would be extremely slow.

  • Thanks for your help Shanky and sorry for taking so long to tag your response as "approved answer" as I was pretty busy around here with some other SQL Server DBA chores :) – Martin Surasky Apr 18 '17 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.