1

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?
4
  • 7
    Though any individual query will not be able to use parallelism, with additional CPU cores you will still be able to run more queries concurrently in different sessions, if that's something that applies to this server (I'm not familiar with the BizTalk workload characteristics).
    – mustaccio
    Nov 24 '20 at 16:49
  • 3
    What are the wait stats on that server? And how did you get to the conclusion that the queries are CPU bound?
    – Ronaldo
    Nov 24 '20 at 16:49
  • @Ronaldo have been using DPA to monitor wait stats. It is showing Memory/CPU as largest wait (though that is not an actual wait stat type so not sure how it calculates this). Regarding memory, the SQL Server has max mem of 12GB and commit for the SQL Server process is only about 5GB. The underlying disk is Tintri all-flash, hence looking to CPU. Also anecdotal evidence of watching the cores in perfmon. Nov 25 '20 at 10:40
  • Thanks @mustaccio. BizTalk workload is unusual and frequently confuses our operational colleagues :) Nov 25 '20 at 10:41
3

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
1
  • 1
    @SirCrispalot FYI the difference between Mustachio's comment and this answer by Josh Darnell is MAXDOP limits how parallel a specific query can execute, so increasing the number of CPUs or cores in your server instance won't improve their performance from a parallelized execution standpoint when MAXDOP = 1, but it can improve performance if currently multiple queries are executing simultaneously and fighting each other for CPU resources since more cores / CPUs allows more queries to be able to run simultaneously. In other words Josh and Mustaccio are both right, just about different scenarios.
    – J.D.
    Nov 24 '20 at 19:16

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.