SQL Server MAXDOP and effect on ConfigMgr

What is MAXDOP?

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.

Sounds cool, what does that mean?

It means that SQL Server allows you to control the number of processors that are available to process a single statement. Statements include things such as: queries, DDL (data definition language) operations, bulk loads using parallel inserts, and a few other scenarios.

As a consultant, I’ve used and tuned MAXDOP setting for large data warehouse data loading operations. The improvement in tuning the parallelism can be quite dramatic.

By default, MAXDOP is set to 0. This means that all available processors are available to be deployed to process statements. That is good, right? Not necessarily. It depends.

Here are the current (somewhat generic) selected recommendations:

· To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value. Setting maximum degree of parallelism to 0 allows SQL Server to use all the available processors up to 64 processors. To suppress parallel plan generation, set max degree of parallelism to 1. Set the value to a number from 1 to 32,767 to specify the maximum number of processor cores that can be used by a single query execution. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored.

· Index operations that create or rebuild an index, or that drop a clustered index, can be resource intensive. You can override the max degree of parallelism value for index operations by specifying the MAXDOP index option in the index statement. The MAXDOP value is applied to the statement at execution time and is not stored in the index metadata.

The effect on ConfigMgr?

For smaller sites, you may not need to change the default setting. When you scale out too much larger environments, particularly with the dreaded CAS in place, it is worth setting MAXDOP and monitoring.

Recently, I asked for feedback from the community on those that have set MAXDOP for ConfigMgr environments. Here is what I received (slightly edited for clarity):

Very large site: With a CAS, MAXDOP with a higher value makes collection queries faster, lower setting makes inbox processing faster, not a lot of room for compromise. We use 2.

Large site: With a CAS scenario with MaxDOP of 6 we’ve seen replication blocking itself. Set it to one and it hasn’t reoccurred. (Note: MAXDOP was disabled)

Summary

MAXDOP can be useful for certain scenarios. Modify and monitor for performance gains, or possible performance penalties. Adjust as needed.

References:

Configure the max degree of parallelism Server Configuration Option

https://msdn.microsoft.com/en-us/library/ms189094.aspx

How It Works: Maximizing Max Degree Of Parallelism (MAXDOP)

From <https://blogs.msdn.microsoft.com/psssql/2013/09/27/how-it-works-maximizing-max-degree-of-parallelism-maxdop/>

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

(note: interesting, as always your mileage may vary)

From <http://www.systemcentercentral.com/tip-setting-sql-max-degree-of-parallelism-achieve-big-database-performance-gains/>

Advertisements
This entry was posted in ConfigMgr, SQL, SQL Server 2016. Bookmark the permalink.

One Response to SQL Server MAXDOP and effect on ConfigMgr

  1. Pingback: SQL Server MAXDOP and effect on ConfigMgr | Skatterbrainz Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s