Home Forums SQL Server 2008 T-SQL (SS2K8) Query spills to tempdb with correct cardinality estimates RE: Query spills to tempdb with correct cardinality estimates

  • October 30, 2013 at 3:58 pm

    #1662875

    martin catherall (10/30/2013)

    Why can't SQL SERVER put aside enough memory for the sort and avoid the spill to disk, there seems to be an upper limit the memory that can be granted to a query.

    Hold on a minute... using TempDB is not automatically a disk function. Queries that spill to TempDB will first use memory and only resort to actual disk usage if they get too big or the optimizer thinks they'll get to big to fit into the memory allocated for the task according to optimizer estimates.

    --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)