We would like to implement best practice on our SQL Server instances. All of our instances still run with the default setting of MAXDOP = 0.
Having read several pages on the recommended settings for this (including Microsoft Support and Erik Darling's posts on brentozar.com), we wanted to check that we have understood these recommendations correctly in terms of our instances. All of our instances are running on virtual machines, and many have only one socket and only a handful of virtual processors.
Example 1:
- 1 x socket 2.30 GHz
- 6 x virtual processors
- Running 1 x SQL Server instance (2016 SP1 CU8, Web Edition 64-bit)
Change MAXDOP from 0 to 6, or from 0 to 3?
Since this instance only has a single socket anyway, a change from 0 to 6 will surely make no difference? Are we likely to gain anything from changing this setting?
Example 2:
- 2 x socket 2.30 GHz
- 6 x virtual processors per socket
- Running 8 x (don't ask!) SQL Server instances (2012 SP4, Standard Edition 64-bit)
Change MAXDOP from 0 to 6? Are we likely to gain anything from changing this setting?
Best Answer
The short answer is: it depends on your workload and the cpu usage of your queries.
What is your CPU usage, are you seeing spikes on certain queries, are these queries bottlenecking other queries?
If the answer is yes
consider changing your maxdop to 2 or 4. Also, check your cost threshold for parallellism, if this is at the default of 5, consider setting this higher, but as always, monitoring your workload before and after is key.
This one is a bit tricky, since one instance could hog all the resources on your machine.
An important factor in this example is determining which instances are the most critical to always run their queries without possible bottlenecking of the other five instances.
Monitor workload before and after, but leaving it at 0 for all instances and having big queries running at 2 or more instances will cause bottlenecks.
I would consider again setting it to 2 for less important instances and 4 for more important instances to start with, and change this again based on the results. (Here also consider the cost threshold for parallellism setting).