Select the product you need help with

Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server

Article ID: 2806535 - View products that this article applies to.

Summary

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. This article discusses the general guidelines that you can use to configure the max degree of parallelism option for SQL Server when you use the sp_configure system stored procedure.

The OPTION (MAXDOP) Transact-SQL query hints can override the max degree of parallelism option in the sp_configure value if the query specifies this option. In SQL Server 2000, the override takes effect only if the value that is specified in the hint is less than or equal to the sp_configure value. In SQL Server 2005 and later versions, the override always takes effect. In SQL Server 2008 and later versions, if the MAXDOP value exceeds the value that was configured by using the Resource Governor, the database engine uses the Resource Governor MAXDOP value. All semantic rules that are used with the max degree of parallelism option are applicable when you use the MAXDOP query hint. The other two options that can override or affect the MAXDOP setting are as follows:

Soft NUMA
Parallel index

Back to the top | Give Feedback

More information

Note The max degree of parallelism configuration option does not limit the number of processors that SQL Server uses. To configure the number of processors that SQL Server uses, use the affinity mask configuration option.

Use the following guidelines when you configure the MAXDOP value.

SQL Server 2005 and later versions

For servers that use more than eight processors, use the following configuration:
MAXDOP=8
For servers that use eight or fewer processors, use the following configuration:
MAXDOP=0 to N

Note In this configuration, N represents the number of processors.
For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node.
For servers that have hyperthreading enabled, the MAXDOP value should not exceed the number of physical processors.
For servers that have NUMA configured and hyperthreading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node.

Note Use these same guidelines when you set the max degree of parallelism option for Resource Governor workload groups.

Also, the maximum value of 8 that is mentioned in these guidelines is applicable for typical SQL Server activity and the overhead for the exchange operators that are used in parallel query plans. You can vary this maximum value, depending on your specific application patterns and the concurrent activity on the instance of SQL Server. For example, consider the following situations:

If you have very small number of queries that are executing at the same time compared with the number of processors, you can set the MAXDOP value to a larger value. For example, you can set the MAXDOP value to 16.
If you a have very large number of queries that are executing at the same time compared with the number of processors, you can set the MAXDOP value to a smaller value. For example, you can set the MAXDOP value to 4.

Note Any value that you consider using should be thoroughly tested against the specific application activity or pattern of queries before you implement that value on a production server.

SQL Server 2000 and later versions

If SQL Server uses a serial plan, it will use only one processor. However, if SQL Server uses parallelism, it must use all the configured processors (as determined by the MAXDOP query hint configuration) for the execution of a parallel plan. For example, if you use MAXDOP=0 on a 32-way server, SQL Server tries to use all 32 processors even if 7 processors might perform the job more efficiently compared with a serial plan that uses only one processor. Because of this all-or-nothing behavior, if SQL Server uses the parallel plan, and if you do not restrict the MAXDOP query hint to a maximum value of 8, the time that is required by SQL Server to coordinate all the processors on a high-end server outweighs the advantages of using a parallel plan.

The MAXDOP setting applies to each step of the plan. Each step will use one CPU or the number of CPUs that is specified by MAXDOP and never anything in between. If steps execute in parallel, the number of threads that are used by the query may exceed the MAXDOP setting.

For the definition of MAXDOP, refer to the "Max Degree of Parallelism Option" topic or the "Degree of Parallelism" topic in SQL Server.

To understand how parallelism works, see the "Parallel Query Processing" section under the "Query Processor Architecture" topic in SQL Server Books Online.

Additional Information

For more information about parallel queries, go to the following Microsoft Developer Network (MSDN) website:

Degree of Parallelism

(http://msdn.microsoft.com/en-us/library/ms188611.aspx )

For best practices information when you are running an instance of SQL Server on computers that have more than 64 CPUs, see the following SQL Server Books Online topic:

Best Practices for Running SQL Server on Computers That Have More Than 64 CPUs

(http://msdn.microsoft.com/en-us/library/ee210547(SQL.105).aspx)

For instances of SQL Server that are running on hyperthreading-enabled processors, follow the recommendations in the following article in the Microsoft Knowledge Base:

322385

(http://support.microsoft.com/kb/322385/ )

SQL Server support in a hyper-threaded environment

Note This recommendation may not apply to newer processor chips such as those in the Intel Nehalem family.

For information about the number of processors that are used by SQL Server and the number of NUMA nodes that are recognized by SQL Server, use the Dynamic Management views sys.dm_os_sys_info and sys.dm_os_nodes. For more information about the MAXDOP setting that is enforced by using Resource Governor, review the information in the Dynamic Management view sys.dm_resource_governor_workload_groups.

For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:

Collapse this tableExpand this table

Rule software	Rule title	Rule description	Product versions against which the rule is evaluated
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)	Degree of parallelism is not set to recommended value	The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect these conditions where the appropriate value for the max degree of parallelism option is not configured together with the number of CPUs in a NUMA node and also the Resource Governor workload configuration for MAXDOP. The SQL Server 2008 R2 BPA supports both SQL Server 2008 and SQL Server 2008 R2. If you run the BPA tool and encounter a "Database Engine - degree of parallelism is not set to recommended value" warning, compare the max degree of parallelism option value and the Resource Governor workload MAXDOP value with the recommended values that are specified in the "Summary" and "More Information" sections.	SQL Server 2008, SQL Server 2008 R2
SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)	Degree of parallelism is not set to recommended value	The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect these conditions where the appropriate value for max degree of parallelism option is not configured together with the number of CPUs in a NUMA node and also the Resource Governor workload configuration for MAXDOP. If you run the BPA tool and encounter a "Database Engine - degree of parallelism is not set to recommended value" warning, compare the max degree of parallelism option value and the Resource Governor workload MAXDOP value with the recommended values that are specified in the "Summary" and "More Information" sections.	SQL Server 2012