The exchange_spillxEvent reports how much data parallelism operators spill per each thread, in the event field name worktable_physical_writes. Each thread may report an exchange_spill xEvent multiple times.

The DMVs sys.dm_exec_query_stats, sys.dm_exec_procedure_stats and sys.dm_exec_trigger_stats now include the data that is spilled by parallelism operators, in the columns total_spills, last_spills, max_spills,and min_spills.

A showplan warning is reported at run time if there are parallelism spills. This warning is rendered in a showplan xml attribute (<ExchangeSpillDetails WritesToTempDb=”spill_amount” />).

There’s some other cool stuff in there too, but I’m only talking about exchange spills here.

How Do I Spill In Parallel?

The easiest way to encourage spills in a parallel plan is to force lots of order preserving operators — stuff like Merge Joins and Stream Aggregates — to occur around Exchange operators.

Exchange operators can Distribute, Redistribute, and Gather parallel streams of data. When they’re not required to keep data in order, things are a lot easier.

When they are, you run into problems.

Hoowee!

In this case, the Exchange operators on either side of the Merge Join spill.

Exchange Spill Extended Event

I set up an extended event to watch this query, which runs for a touch over 9 minutes. The results are somewhat interesting.

THIRTEEN?

Over 19 exchange events, only five of them admit to physically writing to a worktable, for a total of 13 writes.

Over 9 minutes.

Lesson learned: it doesn’t take many exchange spills to make a parallel query really slow.