The query processor in SQL Server 7.0 and 2000 includes parallel query processinga new execution strategy that can improve the performance of complex queries on computers with more than one processor.
SQL Server inserts exchange operators into each parallel query to build and manage the query execution plan. The exchange operator is responsible for providing process management, data redistribution, and flow control. It is possible that a parallel query execution plan can use more than one thread, whereas a serial execution plan, used by a nonparallel query, uses only a single thread for its execution. Prior to query execution time, SQL Server determines whether the current system state and configuration allow for parallel query execution. If parallel query execution is justified, SQL Server determines the optimal number of threads, called the degree of parallelism, and distributes the query workload execution across those threads. The parallel query uses the same number of threads until the query completes. SQL Server re-examines the optimal degree of parallelism each time a query execution plan is retrieved from the procedure cache. Individual instances of the same query could be assigned a different degree of parallelism.
SQL Server calculates the degree of parallelism for each instance of a parallel query execution using the following criteria:
Two server configuration optionsthe maximum degree of parallelism and cost threshold for parallelismaffect the consideration for a parallel query. Although it is not recommended, you can change the default settings for each.
The maximum degree of parallelism option limits the number of threads to use in a parallel plan execution. The range of possible values is 0 to 32. This value is automatically configured to , which uses the actual number of CPUs. If you want to suppress parallel processing, set the value to 1 .
You can affect the query optimizer's choice to use a parallel execution plan by changing the values for the maximum degree of parallelism and the cost threshold for parallelism server configuration options using either the sp_configure system stored procedure or the Enterprise Manager program. It is strongly recommended that you do not change this value on symmetric multiprocessor (SMP) computers. For single processor machines, these values are ignored.
To set the maximum degree of parallelism option, you can use the following:
The cost threshold for parallelism option establishes a ceiling value the query optimizer uses to consider parallel query execution plans. If the calculated value to execute a serial plan is greater than the value set for the cost threshold for parallelism, a parallel plan is generated. This value is defined by the estimated time in seconds to execute the serial plan. The range of values for this setting is 0 to 32767. The default value is 5 . If the maximum degree of parallelism is set to 1 , or the computer has a single processor, the cost threshold for parallelism value is ignored.
You can configure this option using either the sp_configure system stored procedure or the Enterprise Manager:
You can identify when a parallel execution plan is being chosen using the SQL Server Query Analyzer. Two formats are available for viewing the execution plan: graphical and tabular. The graphical execution plan uses icons to represent the execution of specific statements and queries in SQL Server. The tabular representation is produced by the SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT statements. The showplan output for every parallel query will have at least one of these three logical operators:
Figure 35.19 provides an example of a query plan using parallel query techniquesboth repartition streams and gather streams.
Figure 35.19. Graphical execution plan of a query using parallel query techniques.