'TempDB spills when using an UNION ALL

TempDB spills when sorting the overall data of two select statements with UNION ALL.

No spills when executing the individual select statements.

Please can you suggest a way of getting rid of tempDB spills.

SELECT tbl.protx,tbl.b 
FROM 
(
   SELECT sc.protx,be.b             -- No tempDB spills here
   FROM brandexclude be
   INNER join shipcountries sc ON sc.excluderegion=be.region

   UNION ALL

   SELECT sc.protx,bec.b            -- No tempDB spills here
   FROM brandexcludecountry bec
   INNER JOIN shipcountries sc ON sc.country=bec.country
) tbl  
GROUP BY tbl.protx,tbl.b 
ORDER BY tbl.b,tbl.protx            -- This is causing tempDB spills

I have already tried creating indexes and updating statistics, yet no luck!

Link for the actual execution plan https://www.brentozar.com/pastetheplan/?id=rkvAxmqm5

sqlsql-server


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions