Exchange Spill: When Parallel Streams Write to tempdb

An exchange spill happens when a parallel query writes rows to tempdb because its threads got stuck waiting on each other. No sort ran out of memory, and no hash table was too big. The rows were simply on their way from one thread to another, and the buffers in between were full.

The export that got slow for no reason

A nightly export numbers every order line, sorted by customer. Most nights it takes a second. Some nights it takes ten. Memory is fine and the plan has no sort warning, yet tempdb shows writes during the run.

Those writes can come from the Parallelism operators, which move rows between threads in small packets. When the plan needs rows in a fixed order, an exchange must see the next row from every thread before it passes one on. If one thread has nothing ready, the exchange waits. The other threads fill their buffers and wait too. Now everybody waits in a circle, and SQL Server breaks it by writing the buffered rows to tempdb. That write is the exchange spill.

Build a table that spills every time

The demo uses 300 customers with 1,000 orders each, so 300,000 order rows. The notes columns are wide on purpose, because wide rows fill the buffers faster.

DROP TABLE IF EXISTS #Orders;
DROP TABLE IF EXISTS #Customers;

CREATE TABLE #Customers (CustomerId int NOT NULL PRIMARY KEY,
                         CustomerNotes char(400) NOT NULL);
CREATE TABLE #Orders (CustomerId int NOT NULL, OrderId int NOT NULL,
                      OrderNotes char(400) NOT NULL,
                      PRIMARY KEY (CustomerId, OrderId));

INSERT #Customers WITH (TABLOCK) (CustomerId, CustomerNotes)
SELECT value, 'customer' FROM GENERATE_SERIES(1, 300);

INSERT #Orders WITH (TABLOCK) (CustomerId, OrderId, OrderNotes)
SELECT c.value, o.value, 'order'
FROM GENERATE_SERIES(1, 300) AS c
CROSS JOIN GENERATE_SERIES(1, 1000) AS o;

SELECT COUNT(*) AS OrderRows FROM #Orders;

Next, a small event session that listens only for exchange_spill. The demo creates a server-level event session and removes it at the end.

IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'ExchangeSpillDemo')
    DROP EVENT SESSION ExchangeSpillDemo ON SERVER;

CREATE EVENT SESSION ExchangeSpillDemo ON SERVER
    ADD EVENT sqlserver.exchange_spill
    ADD TARGET package0.ring_buffer
    WITH (MAX_DISPATCH_LATENCY = 1 SECONDS);

ALTER EVENT SESSION ExchangeSpillDemo ON SERVER STATE = START;

Run the export query

The export numbers the rows by customer. The variables keep SSMS from drawing 300,000 rows. Two hints force a parallel merge join, a shape the optimizer would not pick for a table this small. MAXDOP 4 needs at least four cores. Give it several seconds.

DECLARE @Rows bigint, @Notes char(400);

SELECT @Rows = MAX(x.RowNum), @Notes = MAX(x.OrderNotes)
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY o.CustomerId) AS RowNum, o.OrderNotes
    FROM #Orders AS o
    INNER MERGE JOIN #Customers AS c ON c.CustomerId = o.CustomerId
) AS x
OPTION (MAXDOP 4, USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

Read the events and name the operator

Now ask the session what it saw, grouped by the node id of the operator that spilled.

WAITFOR DELAY '00:00:02';
DECLARE @Events xml =
    (SELECT CAST(t.target_data AS xml)
     FROM sys.dm_xe_sessions AS s
     JOIN sys.dm_xe_session_targets AS t ON t.event_session_address = s.address
     WHERE s.name = N'ExchangeSpillDemo');

SELECT v.NodeId, COUNT(*) AS SpillEvents, SUM(v.Pages) AS PagesWritten
FROM @Events.nodes('/RingBufferTarget/event[@name="exchange_spill"]') AS n(e)
CROSS APPLY (SELECT
    n.e.value('(data[@name="query_operation_node_id"]/value)[1]', 'int') AS NodeId,
    n.e.value('(data[@name="worktable_physical_writes"]/value)[1]', 'bigint') AS Pages) AS v
GROUP BY v.NodeId
ORDER BY v.NodeId;

On my server two nodes spilled, 3 and 5, with several hundred pages in total, sometimes over a thousand. The counts change from run to run. Now match the node ids to the plan.

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT r.value('@NodeId', 'int') AS NodeId,
       r.value('@PhysicalOp', 'varchar(30)') AS PhysicalOp,
       r.value('@LogicalOp', 'varchar(30)') AS LogicalOp,
       CASE WHEN r.exist('Parallelism/OrderBy') = 1 THEN 'yes' ELSE '' END AS KeepsOrder
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
CROSS APPLY p.query_plan.nodes('//RelOp') AS x(r)
WHERE t.text LIKE N'%INNER MERGE JOIN #Customers%'
  AND t.text NOT LIKE N'%dm_exec_query_stats%'
ORDER BY NodeId;

Node 3 is a Gather Streams and node 5 is a Repartition Streams. Both are Parallelism operators that keep order. Node 7 keeps order too, but it did not spill in my runs. So the writes came from exchanges, not from a Sort. With the actual plan turned on in SSMS, the spilling operators carry a warning.

Take away the order and the spill goes away

Now the same export with a hash join, and once more on a single thread. Then compare the three runs from the plan cache.

DECLARE @Rows bigint, @Notes char(400);

SELECT @Rows = MAX(x.RowNum), @Notes = MAX(x.OrderNotes)
FROM (SELECT ROW_NUMBER() OVER (ORDER BY o.CustomerId) AS RowNum, o.OrderNotes
      FROM #Orders AS o INNER HASH JOIN #Customers AS c ON c.CustomerId = o.CustomerId) AS x
OPTION (MAXDOP 4, USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));
GO
DECLARE @Rows bigint, @Notes char(400);

SELECT @Rows = MAX(x.RowNum), @Notes = MAX(x.OrderNotes)
FROM (SELECT ROW_NUMBER() OVER (ORDER BY o.CustomerId) AS RowNum, o.OrderNotes
      FROM #Orders AS o INNER MERGE JOIN #Customers AS c ON c.CustomerId = o.CustomerId) AS x
OPTION (MAXDOP 1);
GO
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT CASE WHEN t.text LIKE N'%HASH JOIN%' THEN 'hash join, parallel'
            WHEN t.text LIKE N'%MAXDOP 1)%' THEN 'merge join, MAXDOP 1'
            ELSE 'merge join, parallel' END AS Version,
       qs.last_dop AS LastDop, qs.last_spills AS LastSpills,
       qs.last_elapsed_time / 1000 AS LastMs,
       p.query_plan.value('count(//Parallelism/OrderBy)', 'int') AS OrderedExchanges
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
WHERE t.text LIKE N'%RowNum%' AND t.text NOT LIKE N'%dm_exec_query_stats%'
ORDER BY Version;

The parallel merge join has three exchanges that keep order, hundreds of spilled pages, and an elapsed time in seconds. The hash join plan sorts inside each thread and gathers rows freely, so no exchange keeps order. It shows zero spills, and so does MAXDOP 1, which has no exchanges at all. Both finished in well under a second on my server.

What to check on your own server

The mistake I see is chasing memory: a bigger grant, faster tempdb disks, and nothing improves. An exchange spill is a plan shape problem, usually a merge join or a stream aggregate above exchanges that keep order.

Check last_spills in sys.dm_exec_query_stats for your slow parallel queries. If it is high and the plan shows no sort or hash warnings, capture exchange_spill for a few minutes and match the node id. Then test a narrow change on that one statement: a hash join, a better index or a lower MAXDOP. Leave the server setting alone. Clean up when you are done.

IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'ExchangeSpillDemo')
    DROP EVENT SESSION ExchangeSpillDemo ON SERVER;
DROP TABLE IF EXISTS #Orders;
DROP TABLE IF EXISTS #Customers;

Next time tempdb lights up during a parallel query, find the node id before you touch any memory setting.

An exchange spill is not a memory shortage, it is threads waiting on each other.

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