Index build spilling to tempdb

• T. C.

  SSCommitted

  Points: 1550

  More actions

  September 7, 2020 at 2:40 pm

  #3786790

  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.

• Jeff Moden

  SSC Guru

  Points: 1004615

  More actions

  September 8, 2020 at 1:40 pm

  #3786984

  What size is the actual index?  I'm not talking about the memory grants, etc.  How many pages does the actual index have?

  As for as the memory grant formulas being used behind the scenes goes, I have no idea what they use nor how they determine what is needed for spills to TempDB.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)