Degree of Parallelism
SQL Server automatically detects the best degree of parallelism for each instance of a parallel query execution or index data definition language (DDL) operation. It does this based on the following criteria:
Whether SQL Server is running on a computer that has more than one microprocessor or CPU, such as a symmetric multiprocessing computer (SMP).
Only computers that have more than one CPU can use parallel queries.
Whether sufficient threads are available.
Each query or index operation requires a certain number of threads to execute. Executing a parallel plan requires more threads than a serial plan , and the number of required threads increases with the degree of parallelism. When the thread requirement of the parallel plan for a specific degree of parallelism cannot be satisfied, the Database Engine decreases the degree of parallelism automatically or completely abandons the parallel plan in the specified workload context. It then executes the serial plan (one thread).
The type of query or index operation executed.
Index operations that create or rebuild an index, or drop a clustered index and queries that use CPU cycles heavily are the best candidates for a parallel plan. For example, joins of large tables, large aggregations, and sorting of large result sets are good candidates. Simple queries, frequently found in transaction processing applications, find the additional coordination required to execute a query in parallel outweigh the potential performance boost. To distinguish between queries that benefit from parallelism and those that do not benefit, The Database Engine compares the estimated cost of executing the query or index operation with the cost threshold for parallelism value. Although not recommended, users can change the default value of 5 using sp_configure.
Whether there are a sufficient number of rows to process.
If the query optimizer determines that the number of rows is too low, it does not introduce exchange operators to distribute the rows. Consequently, the operators are executed serially. Executing the operators in a serial plan avoids scenarios when the startup, distribution, and coordination costs exceed the gains achieved by parallel operator execution.
Whether current distribution statistics are available.
If the highest degree of parallelism is not possible, lower degrees are considered before the parallel plan is abandoned.
For example, when you create a clustered index on a view, distribution statistics cannot be evaluated, because the clustered index does not yet exist. In this case, the Database Engine cannot provide the highest degree of parallelism for the index operation. However, some operators, such as sorting and scanning, can still benefit from parallel execution.
Note
Parallel index operations are only available in SQL Server Enterprise, Developer, and Evaluation editions.
At execution time, the Database Engine determines whether the current system workload and configuration information previously described allow for parallel execution. If parallel execution is warranted, the Database Engine determines the optimal number of threads and spreads the execution of the parallel plan across those threads. When a query or index operation starts executing on multiple threads for parallel execution, the same number of threads is used until the operation is completed. The Database Engine re-examines the optimal number of thread decisions every time an execution plan is retrieved from the procedure cache. For example, one execution of a query can result in the use of a serial plan, a later execution of the same query can result in a parallel plan using three threads, and a third execution can result in a parallel plan using four threads.
In a parallel query execution plan, the insert, update, and delete operators are executed serially. However, the WHERE clause of an UPDATE or a DELETE statement, or the SELECT part of an INSERT statement may be executed in parallel. The actual data changes are then serially applied to the database.
Static and keyset-driven cursors can be populated by parallel execution plans. However, the behavior of dynamic cursors can be provided only by serial execution. The query optimizer always generates a serial execution plan for a query that is part of a dynamic cursor.
Overriding Degrees of Parallelism
You can use the max degree of parallelism server configuration option to limit the number of processors to use in parallel plan execution. The max degree of parallelism option can be overridden for individual query and index operation statements by specifying the MAXDOP query hint or MAXDOP index option. MAXDOP provides more control over individual queries and index operations. For example, you can use the MAXDOP option to control, by extending or reducing, the number of processors dedicated to an online index operation. In this way, you can balance the resources used by an index operation with those of the concurrent users. Setting the max degree of parallelism option to 0 allows SQL Server to use all available processors upt to a maximum of 64 processors in a parallel plan execution. Setting MAXDOP to 0 for queries and indexes allows SQL Server to use all available processors up to a maximum of 64 processors for the given queries or indexes in a parallel plan execution.