SQL Server Parallel Execution Plans

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.

Continues…

Leave a Reply

Your email address will not be published. Required fields are marked *