SQL Server – How to Provision More CPU from VMware When MAXDOP is Set to 1

cpumaxdopsql servervmware

We have a BizTalk Server group hosted over seven servers, all hosted in VMware. This group comprises four BizTalk application servers and three SQL Server instances.

As per Microsoft's out of the box configuration of BizTalk, one of the SQL Server instances in the group has its MAXDOP intentionally set to 1. According to Microsoft, this setting must not be changed.

We believe that some of the queries running on this SQL instance may be CPU-bound, and would like to consider whether we can boost the CPU resources of this particular virtual machine.

My understanding is that adding additional vCPU cores would be pointless: MAXDOP is set to 1, so any queries constrained by CPU would not be able to make use of any additional CPU cores because they may not run parallel operations.

Is it possible to do anything in this situation? My assumption is that the clock speed of the vCPU in the virtual machine is the same as the clock speed of the physical CPU in the VMware host, so there is no way of increasing that.

  • Are all of my assumptions above correct?
  • If so, does that mean there is no way to boost the CPU available to these queries?
  • Is there anything else we can do?

Best Answer

Are all of my assumptions above correct?

Yeah, you're correct on all counts as far as I can tell.

If so, does that mean there is no way to boost the CPU available to these queries?

Given the constraints you've described, the only way to "boost the CPU" for those queries would be to move the VM to a host with a CPU that has a faster clock speed.

Is there anything else we can do?

Your best bet would probably be to identify the problematic queries and set up plan guides to add MAXDOP hints to those specific queries. This will override the server level setting, so those specific queries would go parallel, while the rest would still be at DOP 1.

Some other options, that are less ideal / more likely to cause problems:

  • Increase server level MAXDOP from 1 - this goes against the config guidance of course, but it is an available option to test and see if it benefits these problematic queries without causing much harm to other queries
  • Add more CPUs - this doesn't help individual queries that are CPU-bound, but it will allow more queries to run at once potentially. This could help or hurt depending on the details of the workload. For example, running more queries at once might just lead to contention (locks or tempdb or something else) rather than increased throughput
Related Question