Few days ago, we got complaint from a client that one of their SQL query is taking too long to execute. Query was long and complicated with several joins and sub queries. The indexes and joins were already optimized but still query was taking long time to execute. We did some research and found out that somebody changed the ‘Maximum Degree of Parallelism’ setting to 1. We modified MAXDOP setting and the execution time reduced from several minutes to few seconds.

What is MAXDOP (Maximum Degree of Parallelism) and how it affects query performance?

MAXDOP is the maximum number of processors which can be used by a SQL query. A query execution plan may have parallel executions within same query when we set MAXDOP setting value greater than 1. Let’s have a look at a following execution plan with Maximum Degree of Parallelism set to value of 8. You can see several parallel execution streams in the execution plan.

Now let’s see if the MAXDOP is set to 1 for the same query, there will be no parallelism as only one processor will be used by SQL query.

There are two ways to find out the current settings for MAXDOP.

• Right click on server in the Object Explorer window. Select Properties and click on Advanced. Under Parallelism section, you will find “Max Degree of Parallelism”.
• In SSMS, type       Exec dbo.sp_configure

To reset the value using SSMS, type the following

Exec dbo.sp_configure ‘max degree of parallelism’,8;
Go
RECONFIGURE;
Go

If you set MAXDOP value to 0, then all processors will be used. One should not use 0 or 1 as MAXDOP value. It is recommended to find out total number of processors on SQL Server machine and use 60% of the total processors.

If you don’t have permissions to change MAXDOP setting or if you just want to enforce a MAXDOP value for a particular SQL query, you can write query hint after your SQL statement in the following syntax.

Option (MAXDOP 32);

Reference:

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