Hi,
I'm building nonclustered indexes on a table, and the execution plan is showing the sort spilling to tempdb. The estimated data size going into the sort is 15GB (this is accurate, all fixed-width columns, row counts correct).
The properties of the plan show that granted memory was 21817320 (so about 20GB), but desired memory was 48515816 (about 46GB).
The sort spills with the warning: "Operator used tempdb to spill data during execution with spill level 1 and 1 spilled thread(s), Sort wrote 2001107 pages to and read 2001107 pages from tempdb with granted memory 21817128KB and used memory 21817128KB"
Does anyone know how it has determined that it needs 46GB for the sort, and why the spill is occurring, when the data to be sorted is 15GB? I've been googling and haven't found a formula anywhere for the memory requirements for the sort.