Microsoft® SQL Server™ detects the best degree of parallelism for each instance of a parallel query execution automatically by considering the following:
Only computers with more than one processor can take advantage of parallel queries.
SQL Server monitors its CPU usage and adjusts the degree of parallelism at the query startup time. Lower degrees of parallelism are chosen if the CPUs are already busy.
Each query requires a certain amount of memory to execute. Executing a parallel query requires more memory than a nonparallel query. The amount of memory required for executing a parallel query increases with the degree of parallelism. If the memory requirement of the parallel plan for a given degree of parallelism cannot be satisfied, SQL Server decreases the degree of parallelism automatically or completely abandons the parallel plan for the query in the given workload context and executes the serial plan.
Queries heavily consuming CPU cycles are the best candidates for a parallel query. For example, joins of large tables, substantial aggregations, and sorting of large result sets are good candidates. Simple queries, often 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 cannot, SQL Server compares the estimated cost of executing the query with the cost threshold for parallelism value. Although not recommended, users can change the default value of 5 using sp_configure.
If the optimizer determines the number of rows in a stream is too low, it does not introduce exchange operators to distribute the stream. Consequently,the operators in this stream are executed serially. Executing the operators in a serial plan avoids scenarios when the startup, distribution, and coordination cost exceeds the gains achieved by parallel operator execution.
The INSERT, UPDATE, and DELETE operators are executed serially. However, the WHERE clause of either an UPDATE or DELETE, or SELECT portion of an INSERT statement may be executed in parallel. The actual data changes are then serially applied to the database.
Static and keyset cursors can be populated by parallel execution plans. However, the behavior of dynamic cursors can be provided only by serial execution. The optimizer always generates a serial execution plan for a query that is part of a dynamic cursor.
At execution time, SQL Server determines if the current system workload and configuration information allow for parallel query execution. If parallel query execution is warranted, SQL Server determines the optimal number of threads and spreads the parallel query’s execution across those threads. When a query starts executing on multiple threads for parallel execution, the query uses the same number of threads until completion. SQL Server reexamines the optimal number of thread decisions each time a query execution plan is retrieved from the procedure cache. For example, one execution of a query can result in 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.
Use SQL Server Profiler to monitor the degree of parallelism for individual queries. The Event Sub Class column of the SQL Operators Event category indicates the degree of parallelism for each parallel query. For more information, see SQL Operators Event Category.
The showplan output for every parallel query will have at least one of these three logical operators:
| Setting Configuration Options | System Stored Procedures |
| sp_configure |