I have a physical server with 40 CPU's (hyperthreaded) with 2 NUMA nodes. On this server I have 20 SQL Server instances installed and I need to make sure each instance has some dedicated CPU assigned into.

The problem is that sometimes one or more of the instances are using all of the CPUs and then about 4 or 5 instances are out of CPU resources and failing.

What is the best way to spread CPU among all of the instances so each instance has some at the minimum?

| improve this question | | | | |
  • The best way would be to have ONE instance and use the resource governor to make sure no instance is hogging the machine. – TomTom Aug 10 '19 at 19:20
  • Thanks, agree but unfortunately i can' modify anything right now and i need to do something with the current design. – user3213459 Aug 10 '19 at 19:22

You can configure the processor affinity used by an instance of SQL Server by right clicking the instance and choosing Properties -> Processors:

Processor Affinity

Assigns processors to specific threads to eliminating processor reloads and reduce thread migration across processors. For more information, see affinity mask Server Configuration Option.

This will enable you to assign specific processors per instance. Getting this right though requires constant attention and tuning so generally I don't advise it unless you really know what you are doing.

If you do decide to proceed, then I highly recommend reading about the affinity mask Server Configuration Option before you attempt it and also you can read a quick tutorial on TechNet and some basic guidelines can be found on Microsoft Support.

| improve this answer | | | | |
  • Thank you, do you think using Resource Governor is the better solution in this case? – user3213459 Aug 10 '19 at 22:00
  • 1
    No because there is no workload management between SQL Server Instances and you would have to configure and manage it across each one you have. I think the affinity is probably your best way to go but you may have to tweak allocations until they are balanced correctly across your instances. Your other option is to configure the MAXDOP but this does not constrain SQL Server to specific CPU's it only constrains the amount that can be used for parallel queries and not everything is a parallel query. – World Wide DBA Aug 10 '19 at 23:08
  • Thank you so much!!! – user3213459 Aug 10 '19 at 23:40
  • One more question please, let's say for 1 instance i decided to assign 4 cpu's , should i setup 2 for numa1 and 2 for numa2? – user3213459 Aug 11 '19 at 0:06
  • I would recommend keeping an instance on a single NUMA node if you can. – World Wide DBA Aug 11 '19 at 8:19

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.