General guidelines to use to configure the MAXDOP option

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

Additionally, the OPTION (MAXDOP) Transact-SQL query hints can override the max degree of parallelism configuration option in the sp_configure value only for the query that 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 in SQL Server 2008, the override always takes effect. In SQL Server 2008, if MAXDOP 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 configuration option are applicable when you use the MAXDOP query hint.

To understand parallelism, first read the material that the "References" section points to.

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 N equals the number of processors: MAXDOP=0 to N.
• 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.

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

The recommendations in this article are based on the following.

SQL Server 2005

For more information about SQL Server 2005, please visit the following Microsoft Developer Network (MSDN) Web site:

SQL Server 2000

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. Because 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.

The MAXDOP setting applies to each step of the plan. Each step will use either 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.