Parallel Plans: Reading Gather Streams and Repartition Streams

Rows move across several workers before returning through one result stream. Gather Streams is one of the operators coordinating that movement. Reading the exchanges explains parallel work that a single arrow hides.

Find Gather Streams and the Parallel Region First

I look for exchange operators before reading a parallel plan's row counts. The exchanges mark where rows move between worker streams. They give the plan's parallel region a readable shape.

A parallel operator doesn't mean every worker receives equal work. Data distribution determines how rows reach workers. Scheduling and downstream demand affect progress too.

The visible exchange operators include Gather Streams, Repartition Streams and Distribute Streams. Each coordinates a different movement. The surrounding operators explain why that movement is needed.

Read the plan in its data-flow direction. Rows normally flow from access operators toward the final result. The graphical placement isn't a claim that the leftmost icon starts first.

Parallel execution also has overhead. More workers don't automatically reduce elapsed time for a small query. Compare the actual workload before treating a parallel icon as an improvement.

Understand What Gather Streams Does

Gather Streams combines several incoming streams into one output stream. It commonly appears near the end of a parallel region. It lets the consumer receive rows through a single stream.

Some exchanges preserve a required ordering under specified conditions. Other gathers simply collect available rows. Inspect the order-related properties rather than assuming every gather sorts.

Gather Streams doesn't by itself create the business ORDER BY requirement. The plan must provide that ordering through the appropriate operators and properties. An unordered SELECT remains unordered at the result contract.

The consumer can also become the pace setter. Slow client consumption changes how quickly rows leave the final stream. That affects upstream workers without proving they are inefficient.

I inspect the gather alongside the next consumer. An exchange is a coordination point, not necessarily the root cause of a wait. The slower participant can live on either side.

Follow Repartitioned Rows to Their Workers

Repartition Streams redistributes rows across workers according to a partitioning rule. Hash partitioning commonly routes equal join or grouping keys together. Other partitioning modes serve other operator requirements.

That coordination lets each worker process its assigned portion consistently. It also creates a place for skew. A heavily repeated key can route a disproportionate population to one worker.

Distribute Streams sends rows from one input stream into several output streams. It starts a parallel region from a serial input. Its partitioning behavior should be read from its own properties.

Don't confuse the workers with table partitions. Execution stream partitioning is an internal work distribution. A nonpartitioned table can still participate in these exchanges.

The source distribution matters more than the number of arrows. Count repeated keys when one worker carries most rows. Raising MAXDOP doesn't divide every hot key evenly.

Produce a Sample Plan to Look for Gather Streams

The following temporary sample creates a grouping workload. Run it with the actual execution plan enabled. Its rows are generated test inputs, sized so the estimated cost lands just above the default cost threshold for parallelism.

MAXDOP caps the allowed parallelism for this statement. It doesn't force the optimizer to select a parallel plan. Table size, estimates and server settings still influence the choice.

My test server uses a higher cost threshold, and there the sample stayed serial. If the sample remains serial for you too, inspect a representative application query that already runs in parallel. Don't change a production server's cost threshold to force this demonstration. The goal is to read a real exchange.

The skewed grouping key deliberately gives you a distribution to examine. Confirm it through the data query below. No claim is made about the plan your server will select.

Save the plan with its parameters and server context. A later execution with another plan is another observation. Keep those observations separate when comparing worker behavior.

CREATE TABLE #ParallelSalesDemo
(SaleId int NOT NULL, CustomerId int NOT NULL, Amount decimal(12,2) NOT NULL);
INSERT #ParallelSalesDemo
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id),
       CASE WHEN a.object_id % 10 = 0 THEN a.object_id ELSE 1 END, 10.00
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
SELECT CustomerId, COUNT_BIG(*) AS GroupRows
FROM #ParallelSalesDemo GROUP BY CustomerId;
SELECT CustomerId, SUM(Amount) AS CustomerAmount
FROM #ParallelSalesDemo GROUP BY CustomerId
ORDER BY CustomerAmount DESC OPTION (MAXDOP 4);

Inspect Actual Rows Per Thread

Open the actual properties or saved Showplan XML for the parallel operators. RunTimeCountersPerThread provides thread-level execution evidence where collected. Compare ActualRows across the participating threads.

One worker with most rows indicates uneven distribution at that operator. A worker with fewer rows isn't automatically idle throughout the statement. Inspect the surrounding region before generalizing.

Aggregations change row counts, so compare the appropriate side of the exchange. Output groups don't equal input rows. A worker can consume many rows and produce one group.

Check actual executions as well as row counts. Repeated work changes the interpretation of an operator's totals. Also keep elapsed time and CPU evidence at their proper scopes.

What key routes the rows at this repartition? Compare its frequency distribution with the thread imbalance. That connects the plan symptom with a concrete data property.

Investigate Exchange Spill Evidence

Exchanges buffer rows while producers and consumers progress. Under relevant pressure or coordination conditions, an exchange can spill buffered work to tempdb. That creates additional work beyond the in-memory movement.

A sort or hash spill elsewhere in the plan isn't automatically an exchange spill. Read the warning's operator and event context. Different spill types need different investigations.

The exchange_spill Extended Event provides a supported collection point where available. Check event availability on the actual version. A bounded test session is preferable to broad production collection.

The query below inspects metadata for that event. It doesn't create an event session or claim a spill occurred. Use observed events and actual plan warnings to establish the event.

I review estimates, worker skew and downstream bottlenecks together when a spill appears. Increasing memory alone isn't a universal explanation. The exchange coordinates work whose imbalance can start elsewhere.

SELECT name, description FROM sys.dm_xe_objects
WHERE object_type = N'event' AND name = N'exchange_spill';

Relate the Waits to the Same Interval

CXPACKET and CXCONSUMER describe aspects of parallel exchange coordination. Some waiting is normal when workers progress at different rates. A wait total alone doesn't identify a defective query.

Inspect these waits during the relevant workload interval. Instance totals include other parallel statements and accumulate across time. Take matching samples if you need interval differences.

Recent versions expose additional parallel coordination waits. Avoid treating one historic wait name as the complete picture. Read the actual version's wait descriptions and the query evidence.

The following query is an inspection starting point. It doesn't assign its totals to the sample statement. Use session or workload-specific observation to make that connection.

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN (N'CXPACKET', N'CXCONSUMER');

The final gathering exchange explains the merge of worker output. The other exchanges explain distribution and redistribution. Read their rows, waits and consumers before changing parallelism settings.

I tune the demonstrated imbalance rather than disabling parallelism from one wait chart. A serial plan can hide the exchange and still perform more work. Fewer arrows aren't a performance guarantee.

Related reading on this blog: Is Stream Aggregate is Same as Gather Streams of Parallelism? and NonParallelPlanReason: Why a Query Refuses to Go Parallel.

An exchange is not wasted work by definition, it is coordination whose balance needs inspection.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.