articles >> performance tuning >> SQL Server Parallel Execution Plans

SQL Server Parallel Execution Plans

By : Joe Chang Feb 02, 2005

Microsoft SQL Server introduced parallel query processing capability in version 7.0. The purpose of parallel query execution is to complete a query involving a large amount of data more quickly than possible with a single thread on computers with more than one processor. Books Online and various Microsoft documents describe the principles of parallel execution and the use of configuration setting that affect parallel execution. However, there is very little explanation on the matter of interpreting execution plans when parallelism is involved. A comparison of several queries with parallel execution plans, to the execution plan with parallelism disabled, provide a means of explaining some of the characteristics of parallel execution plans, including the meaning the of the estimated cost. An examination of actual queries within and without parallel execution plans provides additional guidance on circumstances where parallel execution is beneficial and when it should be disabled.

Parallel Query Processing

Figure 1 below shows a portion of the execution plans for a query with and without parallel operations. The query is a simple SELECT with a WHERE clause search argument (SARG), aggregates in the SELECT list, and a GROUP BY clause. The non-parallel execution plan is forced with the OPTION (MAXDOP 1) clause.

Figure 1. Non-parallel and parallel portions of an execution plan.

Note that the symbols for the common SQL operations--such as Index Seek, Hash Match, and Compute Scalar--have a yellow circle with arrows in the lower right corner when parallel execution is involved for that step. The Parallelism/Gather Streams symbol in Figure 1 is specific to parallel plans. Other symbols specific to parallel plans include Parallelism/Broadcast, Parallelism/Distribute Streams, and Parallelism Repartition Streams.

Figure 2 below shows the left side portion of the two execution plans from Figure 1. The top query with cost 66.65% relative to the batch (Query 1 and Query 2) is the non-parallel execution plan and the bottom query with cost 33.35% is the parallel execution plan.

Figure 2. Non-parallel (top) and parallel (bottom) execution plan relative cost.

Figure 3 below show the detail boxes for the SELECT symbols on the non-parallel (left) and parallel (right) execution plans. The non-parallel plan has a total estimated cost of 121 and the parallel plan has a total estimated cost of 60.7, from which the 66.65% and 33.35% relative costs in Figure 2 are derived.

 

Figure 3. Non-parallel (left) and parallel (right) plan cost detail.

Nowhere in the public Microsoft documentation for SQL Server is the unit of measure for the execution plan cost explained. In fact, all Microsoft SQL Server documentation on this subject seems to be deliberately vague. Is the execution plan cost some measure of time or processor utilization? If it were processor utilization, is it relative to one processor or all processors? An examination of non-parallel and parallel execution plans provide a hint that the unit of measure is most probably time, and that lower plan costs represents lower execution time, but makes no representation of CPU resources.