SQL Server Parallel Execution Plans

Figure 4 below shows the cost details for the two major components of each execution plan discussed above. Figure 4a is the non-parallel Clustered Index Seek and Figure 4b is the parallel plan Clustered Index Seek operation. In both cases the estimated row counts are identical, yet the parallel plan Clustered Index Seek has a cost of 47.1095, approximately one-half the cost of the non-parallel Clustered Index Seek at 94.2190.

Figure 4a. Non-parallel Clustered Index Seek cost details.

Figure 4b. Parallel Clustered Index Seek cost details.

The same pattern is observed in the non-parallel and parallel Hash Match/Aggregate component operations shown in Figure 4c and Figure 4d.

Figure 4c. Non-parallel Hash Match/Aggregate cost details.

Figure 4c. Non-parallel Hash Match/Aggregate cost details.

There is no rational reason to believe that having two or more threads separately process portions of the index seek or hash match operations reduces the total CPU-cycles in processing the entire operation, except under unusual circumstances. In fact, the parallel operation should be more expensive taking into account the cost of merging the partial results from each thread. It is reasonable, however, to expect the parallel operation to complete faster than the non-parallel operation, assuming all required resources are available. The logical conclusion is that the unit of measure for the cost in SQL Server’s execution plan is probably time and not processor utilization.

The execution plan shown seems to be generating parallel execution plans with a cost that represents the time to complete the query based on the availability of two processors, even if more than two processors are available. SQL Server Book Online, in fact, states that the actual degree of parallelism is determined at the time of execution depending on the availability and utilization of resources. If resources are tight, SQL Server may even elect to execute the plan with a single thread. Hence, a parallel plan only indicates that parallel execution is possible and displays the estimated cost, in units of time, based on the use of two processors.