A parallel plan is any plan for which the Optimizer has chosen to split an applicable operator into multiple threads that are run in parallel.
Not all operators are suitable to be used in a parallel plan. The Optimizer will only choose a parallel plan if:
- the server has multiple processors,
- the maximum degree of parallelism setting allows parallel plans, and
- the cost threshold for parallelism sql server configuration option is set to a value lower than the lowest cost estimate for the current plan. Note that the value set here is the time in seconds estimated to run the serial plan on a specific hardware configuration chosen by the Query Optimizer team.
- The cost of the parallel plan is cheaper than the serial plan.
If all these criteria are met, then the Optimizer will choose to parallelize the operation.
An example that illustrates how this works is trying to count all the values in a table that match particular search criteria. If the set of rows in the table is large enough, the cost of the query is high enough, and the other criteria are met, then the Optimizer might parallelize the operation by dividing the total set of rows in the table into equal chunks, one for each processor core. The operation is then executed in parallel, with each processor core executing one thread, and dealing with one/number of cores of the total set of rows. This enables the operation to complete in a lot less time than using a single thread to scan the whole table.
One thing to be aware of when dealing with parallel plans is that SQL Server doesn’t always do a great job of distributing the data between threads, and so your parallel plan may well end up with one or two of the parallel threads taking considerably longer to complete.
Leave a Reply