I have a 2016 Standard edition server with two instances. The server has 8 logical processors and 40GB memory (it's a testing server on hyper-v so the spec isn't high!) and it doesn't have any existing workload to profile.

Does the advice around configuration of MAXDOP and TempDb files still stand true on a multiple instance server?

My thoughts were to split the server resources and apply the configuration based on those.

e.g. each instance would have

  • 16GB MAX memory
  • MAXDOP 4
  • 4 TempDB files
  • if you use MAXDOP 4 as far as I'm aware there's no way to ensure that 8 cores won't be used - MAXDOP applies on a per-operator per-plan basis. Processor affinity might be more suitable. – George.Palacios Mar 27 '19 at 10:22
  • 1
    Instances don't talk to each other, so any config in one would be unknown by the others. If you want to split the resources, use resource governor on each instance to get the most even level of sharing, although that still might not turn out very good. – Sean says Remove Sara Chipps Mar 27 '19 at 10:42

Resource governor is your best best if you really need to restrict resources for each instance, although if that is your major concern, I would just avoid stacking multiple instances.

If splitting resources isn't a big concern, I would configure total memory no more then 36GB, so 16GB per instance is fine. I would still configure 8 tempdb files for each instance, and would probably set max dop to around 6, to be tested against the work load.

This makes sure neither instance, or the OS is unable to get the memory it needs, but by configuring the maxdop higher then half the cores, it will allow processes to use more then 4 cores if available, while still not allowing a single process to dominate the entire machine.

I would still allocate 8 tempdb files to the instance, because without something in place to provide a hard restriction, sql server will still be able to access all 8 cores, and could potentially generate 8 simultaneous tempdb requests.

One other thing I would verify, is that if this is running on sql server standard edition, that your VM is configured with no more then 4 sockets regardless of the number of cores. Sql server will restrict the cpu usuage to 4 sockets, and if you use single core cpus, you could wind up with both instances trying to share the first 4 CPU's, while the other 4 are left idle.

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.