About SQL Server's Max degree of parallelism Parameter

Max degree of parallelism Description: http://msdn.microsoft.com/zh-cn/library/ms181007.aspx

 

When SQL server runs on a computer with multiple microprocessors or CPUs, it executes the check for the optimum degree of parallelism (that is, the number of processors used to run a statement) for each parallel plan ). You can useMax degree of ParallelismOption to limit the number of processors used for parallel execution. If the default value is 0, all available processors are used. SetMax degree of ParallelismSet to 1 to cancel the parallel plan generation. Set this value to a number greater than 1 (up to 64) to limit the maximum number of processors used to execute a single query. If the specified value is greater than the number of available processors, the actual number of processors is used. If the computer has only one processorMax degree of ParallelismValue.

 

Experience from Microsoft engineers:

 

For the max degree of parallelism option, the default value 0 is usually used. In this case, the SQL statement determines the maximum concurrency of the query based on the query complexity. However, in some OLTP (online transaction processing) systems, we will find a large number of concurrent queries in SQL, which may affect SQL Performance in two aspects:

1. If the concurrency of a single query is too high, the query will spend a lot of time waiting for concurrent synchronization, thus affecting the query performance.
2. Too many concurrent queries will occupy a large amount of CPU resources, thus reducing the chance of obtaining CPU time from other queries. This will slow down other queries.

In general, if we see that SQL server has a large number of cxpacket wait types, or many queries are in the runnable state for a long time (indicating that the query is waiting for CPU time slices ), we recommend that you reduce the concurrency to further troubleshoot the problem.