INF: General Guidelines to Use to Configure the MAXDOP Option (329204)

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

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.

REFERENCES

For the definition of MAXDOP, refer to the "Max Degree of Parallelism Option" topic in the SQL Server Books Online.
To understand how parallelism works, see the "Parallel Query Processing" section under the "Query Processor Architecture" topic in SQL Server Books Online.