Article ID: 2806535 - View products that this article applies to.
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:
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
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:
SQL Server 2000 and later versionsIf 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 InformationFor more information about parallel queries, go to the following Microsoft Developer Network (MSDN) website:
Degree of ParallelismFor 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 CPUsFor instances of SQL Server that are running on hyperthreading-enabled processors, follow the recommendations in the following article in the Microsoft Knowledge Base:
322385Note This recommendation may not apply to newer processor chips such as those in the Intel Nehalem family.
(http://support.microsoft.com/kb/322385/ )SQL Server support in a hyper-threaded environment
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
Article ID: 2806535 - Last Review: February 6, 2013 - Revision: 2.0
Contact us for more help
Connect with Answer Desk for expert help.