Post reply

Index build spilling to tempdb

  • 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.

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply