1

When setting MAXDOP on VMWare virtual servers VMWare recommend following the Microsoft guidlines

https://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf

https://support.microsoft.com/en-gb/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi

  • Keep MAXDOP at or below # of logical processors
  • Keep MAXDOP at 8
  • Keep MAXDOP at or below # of logical processors per NUMA node
  • Keep MAXDOP at 8

A colleague argues that virtual CPUs are different, SQL Server can only access 1 CPU no matter how many you have and MAXDOP should be set to 1. Based on the above information and lots of other websites I think that is wrong, and bad for performance. I have set MAXDOP as above for years. But is there any truth in what they are saying.

Many Thanks

  • 5
    You are correct, he is wrong in saying that MAXDOP=1 would be good for all types of application and workload. Sharepoint and DynamicsAX are applications which MS suggests should have MAXDOP as 1 in database they are accessing, there may be others I am not aware about. For others you need to find out what is best for your environment. – Shanky Sep 7 '18 at 10:50
3

Your colleague is wrong. SQL Server will use parallelism whether the cores are virtual or not.

In fact the VMWare SQL Server best practices guide you link to explicitly states:

There is a fair amount of misconception and incorrect advice on the Internet regarding the values of these configurations in a virtual environment. When low performance is observed on their database, and CXPACKET is high, many DBAs decide to disable parallelism altogether by setting MAXDOP value to one (1). This is not recommended because there might be large jobs that will benefit from processing on multiple CPUs. The recommendation instead is to increase the CTFP value from five seconds to approximately 50 seconds to make sure only large queries run in parallel. Set the MAXDOP according to Microsoft’s recommendation for the number of cores in the VM’s NUMA node (no more than eight).

I will however note that the writer of the VMWare best practices guide is confused. The value is not in seconds but in "cost" (also known as query bucks)

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.