Question :
When setting MAXDOP on VMWare virtual servers VMWare recommend following the Microsoft guidlines
- 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
Answer :
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)