Spills Week: How Exchange Spills In Parallel Query Plans Crush Query Performance

Many Have Entered, Few Have Left

For some background on Exchange Spills, check out this Great Post™ by Joe.

The root of this demo was trying to show people silly things about CTEs, how TOP can fence things off, and how TOP introduces a serial zone in plans unless it’s used inside the APPLY operator.

The result was this magnificent beast.

Long Mane

Why is this magnificent?

Because we have the trifecta. We have a spill on all three types of parallel exchanges.

SQL Server Query Plan
It’s not gonna work out

Let’s take a closer look at those beauties.

SQL Server Query Plan
*slaps hood*

Why Did That Happen?

This plan has a Merge Join, which requires ordered input.

That means the Repartition and Gather Streams operators preserve the order of the Id column in the Users table.

SQL Server Query Plan
News Of The World

They don’t actually order by that column, they just keep it in order.

But what about Distribute Streams? GREAT QUESTION!

SQL Server Query Plan
Legalize Burberry

It has the same Partition Column as Repartition Streams. They both have to respect the same order going into the Merge Join, because it’s producing ordered output to the Gather Streams operator.

In short, there’s a whole lot of buffers filling up while waiting for the next ordered value.

Were Parallel Merge Joins A Mistake?

[Probably] not, but they always make me nervous.

Especially when exchange operators are the direct parent or child of an order preserving operator. This also goes for stream aggregates.

I realize that these things are “edge cases”. It says so in the documentation.

The Exchange Spill event class indicates that communication buffers in a parallel query plan have been temporarily written to the tempdb database. This occurs rarely and only when a query plan has multiple range scans… Very rarely, multiple exchange spills can occur within the same execution plan, causing the query to execute slowly. If you notice more than five spills within the same query plan’s execution, contact your support professional.

Well, shucks. We only have three spills. It looks like we don’t qualify for a support professional.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

One thought on “Spills Week: How Exchange Spills In Parallel Query Plans Crush Query Performance

  1. I am very interested in knowing how to influence optimizer to come up with different execution plan.

Comments are closed.