, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

SQL Server Optimization – Parallel Execution Plans

What is Parallelism in execution plans?


A parallel plan is any plan where at least a single iterator is split into multiple threads and these threads run in parallel. Please note that Not all operators are parallel aware. Meaning not everything can be execute in parallel.

This means SQL Server internally manages of the things in parallel while executing a query. Please note that there is no plan which is 100% parallel. We can have a plan which is 100% serial and we can have plan where some of the things happened in parallel. We will at least have one iterator which works in serial manner. That is the first operator. The first operator can be Select, Insert and Delete.

Now how SQL Server decides that it has go with the parallel plan or serial plan. Also note that sometimes parallel plans are better and sometimes serial plans are better because for parallelism also we have to pay for the cost of dividing the iterator task (shifting data) into multiple threads and then we have gather all the threads and make them one for the next iterator at some in time.

During query optimization, SQL Server checks queries that might benefit from parallel execution. SQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution.

An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control. The exchange operator includes the Distribute Streams, Repartition Streams, and Gather Streams logical operators as subtypes, one or more of which can appear in the Showplan output of a query plan for a parallel query.

The SQL Server query optimizer does not use a parallel execution plan for a query if any one of the following conditions is true:

  • The serial execution cost of the query is not high enough to consider an alternative, parallel execution plan.
  • A serial execution plan is considered faster than any possible parallel execution plan for the particular query.
  • The query contains scalar or relational operators that cannot be run in parallel. Certain operators can cause a section of the query plan to run in serial mode, or the whole plan to run in serial mode.

Parallel aware Iterators are

  • Clustered Index Seek
  • Clustered Index Scan
  • Parallelism ( Exchange Operators )
    • Distribute Streams
    • Repartition Streams and
    • Gather Streams

In the next post ( Parallelism – Part II ) I will write some more notes on parallelism.

Cheers!. Thanks for reading !

-Pawan Khowal