Andy Brownsword notes a major performance risk in Integration Services:
One particular performance issue with SSIS data flows can fly under the radar – spilling to disk. This isn’t clearly visible through regular debugging or execution so can go unnoticed. And it hurts.
Paging to disk is bad for performance. Disks are much slower to access than memory, so we want to keep our data away when possible.
Andy calls out two reasons why we might find spilling to disk, as well as how to track if this is happening.