Parallel query processing first appeared in SQL Server 7.0, giving the engine a way to split a large query into multiple threads and run them on more than one CPU core. The idea is simple: if a single CPU can process a scan or join in one minute, two CPUs might finish in half that time. On modern servers with several cores or with hyper‑threading enabled, the performance difference can be substantial for data‑intensive operations such as large scans, complex aggregations, or multi‑table joins.
SQL Server manages parallelism through a combination of global configuration options and per‑statement hints. The most important settings are max degree of parallelism (MAXDOP) and cost threshold for parallelism. MAXDOP limits the maximum number of worker threads that a single statement can spawn. Setting it to 1 forces the query optimizer to produce a strictly serial plan, regardless of how many CPUs are available. A higher value allows the optimizer to consider splitting the work. The cost threshold determines the estimated cost that a query must exceed before the optimizer will generate a parallel plan; a lower threshold leads to more parallel plans, a higher one suppresses them.
When the optimizer decides to use parallelism, it creates special operators in the execution plan. These are marked by a yellow circle with arrows in the lower‑right corner of the icon, and by the prefix Parallelism in the operator name. The most common operators are Parallelism/Gather Streams, Parallelism/Distribute Streams, Parallelism/Repartition Streams, and Parallelism/Broadcast. They are the plumbing that moves data between threads, aggregates partial results, or splits input ranges.
Parallelism is not a magic bullet. The optimizer estimates the benefit of using multiple threads by looking at the cost of the operation, the number of rows, and the CPU and I/O characteristics. The estimated cost is expressed in arbitrary units that the engine translates into an expected wall‑clock time, not a CPU‑seconds measure. This design decision makes sense because the primary goal of parallelism is to reduce the elapsed time a user waits for a result.
One consequence of this approach is that the cost shown in a parallel plan is often lower than the cost of a serial plan for the same query. The engine assumes that each thread runs on a separate CPU, so the same work is completed in less real time. However, this estimate ignores the overhead of merging partial results and the extra context switching that can occur when threads share memory or I/O devices.
In practice, a well‑tuned parallel plan can shave seconds, minutes, or even hours off a large query. But on workloads that are I/O bound or that have a very low row count, the overhead can outweigh the benefit, making a serial plan faster. Understanding how the optimizer builds and evaluates these plans is essential for performance tuning and for making informed decisions about when to enable or disable parallelism.
Reading and Interpreting Parallel Execution Plans
When you examine a query plan that contains parallel operators, the first visual cue is the yellow arrows on the operator icons. Those arrows show the data flowing between the parallel worker threads. Each thread executes the same operation on a different portion of the input. The operator that collects the results - Parallelism/Gather Streams - collects the partial outputs into a single result set. This is the main point where the engine reassembles the final data.
Below is a typical pair of plans: one generated with OPTION (MAXDOP 1) and the other with the default MAXDOP value. The top plan in the image is the serial version, and the bottom is the parallel version. Notice that the serial plan has a single Hash Match operator, while the parallel plan has two parallel hash match streams feeding into a gather stream.
No comments yet. Be the first to comment!