SCCM Guru Webcast Q&A #1–MAXDOP

This is the first in the series where I finish answering all the questions that came up during my SCCM Guru Webcast session.

QUESTION:
Can you explain what “Max Degree of Parallelism” is, and how I should configure that?

ANSWER:
“Max degree of parallelism”, also known as the “MAXDOP” option in SQL, is the number of processors employed to run a single SQL statement.

DETAILS:
SQL may choose to split up the work of a query into multiple parallel operations to make it go faster.  The maximum number of parts it will be split into is determined by MAXDOP option in SQL.

NOTE:  MAXDOP does NOT set the number of CPUs that SQL is allowed to use, that is the Processor Affinity, instead MAXDOP sets the number of processors each SQL STATEMENT can use.   In other words, if you have 8 processors on your server and you set MAXDOP to 2, that does not mean SQL will only use 2 processors and leave the other 6 idle.  MAXDOP of 2 will mean that each SQL statement will use at most 2 processors, but all 8 processors will be used by all SQL statements and operations (assuming your affinity is set to use all processors).

 

HOW SHOULD MAXDOP BE SET:
It depends :)

 In general you should set MAXDOP to the maximum number of PHYSICAL processors present on the server up to a maximum of 8.  Of course, as with everything, test the settings to make sure that number is right for you.

 For machines that have NUMA configured, MAXDOP should be configured to the number of CPUs per NUMA node

 IMPORTANT:  Understand the difference between physical cores and logical cores!   Physical cores are actual full CPUs. Logical cores (as in Hyperthreaded CPUs) are not.  A machine with 2 CPUs that has hyperthreading turned on will show up as 4 CPUs in Task manager, but only 2 of them are real, physical CPUs.  Use the value 2, not the value 4 for MAXDOP.

 Examples:

Server MAXDOP Value
1 physical processor 1
4 physical processors 4
4 physical processors with hyperthreading
(which shows 8 processors in Task Manager)
4
2 “Quad core” Processors
(which is 2 processors with 4 cores each)
8
64 physical CPUs 8


HOW TO SET MAXDOP:

1.  At the server level with SQL Management Studio

clip_image001[4]

2.  At the server level with SP_CONFIGURE

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure ‘max degree of parallelism’, 8;
GO
RECONFIGURE WITH OVERRIDE;
GO

3.  At the statement level using OPTION(MAXDOP)

SELECT
*
FROM
  dbo.v_R_System_Valid
OPTION(MAXDOP 1)

4.  At the resource pool level using Resource Governor
http://msdn.microsoft.com/en-us/library/bb933866.aspx 

NOTE:  The MAXDOP setting may be changed on the fly.  You do NOT need to restart SQL when you change this!

EXTERNAL LINKS:

 Max degree of parallelism option
http://msdn.microsoft.com/en-us/library/ms181007.aspx
General guidelines to use to configure the MAXDOP option
http://support.microsoft.com/kb/329204


Managing SQL Server Workloads with Resource Governor
http://msdn.microsoft.com/en-us/library/bb933866.aspx

 

Number2 (John Nelson)
MyITForum – Forum Posts
MyITForum – Blog
Add to Google

email

Written by , Posted .