Empty Parallel Zones in SQL Server Execution Plans

An empty parallel zone is an area of the plan bounded by exchanges (or the leaf level) containing no operators.

How and why does SQL Server sometimes generate a parallel plan with an empty parallel zone?

Demo

The following query uses the public Stack Overflow 2010 database (1GB download).

USE StackOverflow2010;
GO
DECLARE 
    @UId integer,
    @PId integer;

SELECT TOP (1000)
    @UId = U.Id, 
    @PId = P.Id
FROM dbo.Users AS U
JOIN dbo.Posts AS P
    ON P.OwnerUserId = U.Id
WHERE
    U.UpVotes > 100
ORDER BY 
    U.Id DESC
OPTION (MERGE JOIN);

The execution plan shows an empty parallel zone:

The empty parallel zone is:

Explanation

SQL Server performs a number of tweaks to the raw output from the query optimizer. These static rewrites don’t depend on cost-based considerations—they are considered always good when they are applicable.

One of these tweaks involves pushing a non-sargable predicate (filter) down into a child scan or seek. This allows rows to be filtered much earlier, in the storage engine, rather than surfacing those rows to the query processor and filtering them there.

In this case, the raw parallel plan produced by the query optimizer has a separate Filter operator for the predicate U.UpVotes > 100. This operator gets pushed down into the scan by the post-optimization static rewrite, leaving a gap behind.

We can see the original Filter by disabling non-sargable predicate pushdown with undocumented and unsupported trace flag 9130:

DECLARE 
    @UId integer,
    @PId integer;

SELECT TOP (1000)
    @UId = U.Id, 
    @PId = P.Id
FROM dbo.Users AS U
JOIN dbo.Posts AS P
    ON P.OwnerUserId = U.Id
WHERE
    U.UpVotes > 100
ORDER BY 
    U.Id DESC
OPTION (MERGE JOIN, QUERYTRACEON 9130);

Zooming in:

The extra parallel zone

Pushing the filter down to the scan explains why the parallel zone ends up empty. You might still wonder why there is a separate parallel zone here at all.

Ordinarily, the child scan would be parallel, so there would be no need for a round-robin Distribute Streams exchange. The parallel scan would distribute rows among threads.

That does not happen here because the optimizer chose a backward scan of the clustered index on Users. Preserving this order through the rest of the plan satisfies the presentation ORDER BY without an explicit Sort operator.

It is a long-standing limitation of the SQL Server storage engine that backward index scans cannot be parallel. The optimizer assesses that it is worth processing the Filter in parallel, so it adds a Distribute Streams exchange to achieve that.

Final Thoughts

In an ideal world, the storage engine would be enhanced to add parallel backward index scans. Or the post-optimization filter pushing would remove any empty parallel zone created by that action. Or the two exchanges would be collapsed to a single exchange using hash repartitioning suitable for the row-mode parallel merge join.

None of those things are in the product today (SQL Server 2019 CU10, Azure SQL Database), so you might occasionally see an empty parallel zone. The above mechanism is usually the reason behind it. In practice, the performance impact is pretty minimal, but it does look odd.

Thanks for reading.