We had a great thread on the SCC forums the other day I wanted to share, ambiguously titled “are we the only one?” in which community member Drew Drew (a great OpsMgr admin and MP author) describes the massive performance gains realized through a change in the max degree of parallelism in OpsMgr SQL instances.
What is the max degree of parallelism?
The Microsoft SQL Server max degree of parallelism (MAXDOP) configuration option controls the number of processors that are used for the execution of a query in a parallel plan. This option determines the computing and thread resources that are used for the query plan operators that perform the work in parallel. Depending on whether SQL Server is set up on a symmetric multiprocessing (SMP) computer, a non-uniform memory access (NUMA) computer, or hyperthreading-enabled processors, you have to configure the max degree of parallelism option appropriately.
Let’s get to what Drew found and how you can easily determine the best settings for your environment!
Drew reports: “We have found that correctly setting MAXDOP in SQL has transformed our System Center environment. There are a number of opinions out there on how to set this. I’ll use our VM SQL 2012 servers as an example; the same 2012 servers I reference in this posting.
Number of logical processors (i.e. what you see in Task Manager) = 8. It’s a VM, so NUMA value will always be 1”
What to Change and Why
Our DBA’s set MAXDOP to 8, following conventional wisdom. The system has been usable, but lots of wait states and other quirky behavior (see my blog posting for Partitioning and Grooming). By setting MAXDOP to 4, the system is transformed. Why 4? Hyperthreading. We really don’t have 8 CPU’s, we have virtual slices of processing time from the physical processors hosting ESX–using hyperthreading.
This calculator removes all of the guesswork; just halve the result for hyperthreading (if you accept the theory) and you’re set.
FAQ: Will this work for all System Center databases?
ANSWER: Your mileage will vary, but yes it should.
See also “Recommendations and guidelines for the “max degree of parallelism” configuration option in SQL Server” at