TIP: Setting SQL Max Degree of Parallelism Achieve Big System Center SQL Performance Gains

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!

Findings

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.

http://blogs.msdn.com/b/sqlsakthi/p/maxdop-calculator-sqlserver.aspx

FAQ: Will this work for all System Center databases?

ANSWER: Your mileage will vary, but yes it should.

image

Additional Resources

See also “Recommendations and guidelines for the “max degree of parallelism” configuration option in SQL Server” at

http://support.microsoft.com/kb/2806535

4 thoughts on “TIP: Setting SQL Max Degree of Parallelism Achieve Big System Center SQL Performance Gains

  1. Profile photo of curtmcgirtcurtmcgirt

    the blog post for comments

    http://blogs.msdn.com/b/sqlsakthi/archive/2012/05/24/wow-we-have-maxdop-calculator-for-sql-server-it-makes-my-job-easier.aspx

    has a lot of unanswered questions over a year old… including one I share:

    “when I run the powershell script for the Number of processor cores, I don’t get one integer. I get four rows of the number 2.” what am I supposed to do with these four 2s in the nifty excel calculator that only accepts one integer?”

     

     

  2. Profile photo of DrewDrew

    Pete, etc. thank you all for taking the time to make those Dr. Gonzo blog posts coherent. Often I write them late night as I transition from one troubleshooting/scripting deep dive to another.

  3. Profile photo of DrewDrew

    Hey Curt, here’s how we did it for the physical SQL boxes hosting SCCM databases:

    Each server has two processor sockets, which matched NUMA count = 2 (from the query). Powershell returned two rows with 6 each. Just add them up and divide by your NUMA result.

    Here’s the Powershell (assumes v2 in case OS is 2008 R2) I gave our DBA’s:

    Run this if NUMA count = 1

    powershell.exe -command “& {$procs = Get-WmiObject -namespace “root\CIMV2″ -class Win32_Processor -Property NumberOfCores | select NumberOfCores; $total = $procs.numberofcores}”

    Run this if NUMA count > 1

    powershell.exe -command “& {$procs = Get-WmiObject -namespace “root\CIMV2” -class Win32_Processor -Property NumberOfCores; foreach ($proc in $procs){$proc.numberofcores};$total = ($procs.count * $proc.numberofcores)}”

  4. Pingback: SQL Server MAXDOP and effect on ConfigMgr | Steve Thompson [MVP]

Leave a Reply