0

I have 2 SQL 2017 Instances on each node in a 2 node cluster with a file share witness. 8 cores and 64GB RAM on each.

Node A acts as the primary for Instance 1 and Node B acts as the primary for Instance 2 . (Synchronous) Node B acts as the secondary for Instance 1 and Node A acts as the secondary for instance 2. (Synchronous)

I am wondering what the right balance of resources (Max DOP and Max Memory) would be in the ideal scenario? Would I be shooting myself in the foot by setting maxdop = 8 on the primaries? What is advisable for Maxdop on the secondaries in this situation?

Likewise, generally how much max memory should I set for the primary/secondary? (Obviously leave some for OS as well)

I am already working on a solution to re-balance the resources in the event of a failover, so no need to account for that in your reply. Unfortunately, this is for a Production build where I am unable to replicate the amount of typical load, so any generalities are welcome.

Thanks!!

1 Answer 1

1

I am already working on a solution to re-balance the resources in the event of a failover, so no need to account for that in your reply.

That's actually the main thing that drives the reply. If you don't sharply limit each primary instance it will acquire an unfair share of the server memory, and you may have severely degraded performance of the other instance after failover.

If you rebalance by setting max server memory for both instances in the case of a failover, you may run without limiting it on each node normally.

I would not limit the CPU affinity or maxdop on either instance. The OS allocates CPU resources very dynamically and fairly, so unless you observe one isntance being starved by excessive CPU utilization from the other instance, just leave those at the default settings.

3
  • Thanks very much for the prompt response and that all seems logical. I just want to make sure I understand you clearly: I can leave max memory value on all instances on both sides at ~54GB (Leaving 10 for OS) and only need to be concerned for the failover event to rebalance Max Memory Value? Commented Sep 2, 2020 at 21:29
  • 1
    Yes. That's what I would start with. Commented Sep 2, 2020 at 21:36
  • 2
    I see you have plan for a failover... but what about an incident on your node A that would force you to run on node B for a long period of time ? Usually, you want to do the setting just like if both instance were primary on the same node. If you are allowed to run with degraded performance in such a scenario, then you could set it differently but don't forget that secondary node does required some hardware as well (specially if you made them readable). For the MAXDOP it usually depends on your workload. How many crappy query do you want to run at the same time ? Commented Sep 3, 2020 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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