SQL Server:General guidelines to use to configure the MAXDOP option{转载}

http://support.microsoft.com/?scid=kb;en-us;329204&x=9&y=19

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