Degree of Parallelism
Microsoft® SQL Server™ 2000 detects the best degree of parallelism for each instance of a parallel query execution automatically by considering:
- Is SQL Server running on a computer with more than one microprocessor or CPU, such as a symmetric multiprocessing computer (SMP)?
Only computers with more than one CPU can use parallel queries.
- What is the number of concurrent users active on the SQL Server installation at this moment?
SQL Server monitors CPU usage and adjusts the degree of parallelism at the query startup time. Lower degrees of parallelism are chosen if CPU usage is high.
- Is there sufficient memory available for parallel query execution?
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.
- What is the type of query executed?
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 do not benefit, 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.
- Is there a sufficient amount of rows processed in the given stream?
If the query 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 query 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 execution of the parallel query 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 Profiler to monitor the degree of parallelism for individual statements. Use the Degree Of Parallelism event class in the Performance event category. For more information, see Performance Event Category.
The showplan output for every parallel query will have at least one of these logical operators:
- Distribute Streams
- Gather Streams
- Repartition Streams