Microsoft KB Archive/329204

= General guidelines to use to configure the MAXDOP option =

Article ID: 329204

Article Last Modified on 11/14/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Workgroup Edition
 * Microsoft SQL Server 2000 Service Pack 3a
 * Microsoft SQL Server 2000 Service Pack 3
 * Microsoft SQL Server 2000 Service Pack 2
 * Microsoft SQL Server 2000 Service Pack 1
 * Microsoft SQL Server 7.0 Service Pack 4
 * Microsoft SQL Server 7.0 Service Pack 3
 * Microsoft SQL Server 7.0 Service Pack 2
 * Microsoft SQL Server 7.0 Service Pack 1
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q329204



SUMMARY
This article discusses the general guidelines that you use to configure the max degree of parallelism (MAXDOP) option for SQL Server when you use the sp_configure system stored procedure.

Additionally, the OPTION (MAXDOP 8) Transact-SQL query hints can override the max degree of parallelism configuration option in sp_configure only for the query that specifies this option. All semantic rules that are used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint.

To understand parallelism, first read the material that the &quot;References&quot; section points to.



MORE INFORMATION
Note The MAXDOP option does not limit the number of processors that SQL Server uses. Use the affinity mask configuration option to configure the number of processors that SQL Server uses.

Use the following guidelines when you configure the MAXDOP value:
 * For servers that use more than eight processors, use the following configuration: MAXDOP=8.
 * For servers that have eight or less processors, use the following configuration where  equals the number of processors: MAXDOP=0 to.
 * 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 hyper-threading enabled, the MAXDOP value should not exceed the number of physical processors.

These are general guidelines only. There may be some exceptions to these guidelines. For example, do not use the guidelines under the following conditions:
 * If the server is not an online transaction processing (OLTP)-based server.
 * If the server is running large batch processing when there is no or minimal user load.
 * If the server is running resource-intensive operations. For example: CREATE INDEX statements on large tables.

Note Test the configuration before you implement the changes on a production server.



The recommendations in this article are based on the following:

If SQL Server chooses to use a serial plan, it will only use one (1) processor. However, if SQL Server chooses to use 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 seven processors might perform the job more efficiently as compared to a serial plan that only uses one processor. As a result of this all or nothing behavior, if SQL Server chooses the parallel plan and you do not restrict the MAXDOP query hint to a maximum value of 8, the time that it takes SQL Server to coordinate all the processors on a high-end server outweighs the advantages of using a parallel plan.

