Post reply

DATA SPILL

  • July 8, 2014 at 4:28 am

    #288501

    Hi experts,

    In execution plan we are getting warning

    Operator used tempdb to spill data during execution with spill level 1

    how to overcome this warning??

  • July 8, 2014 at 4:37 am

    #1727702

    What operator?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 8, 2014 at 4:44 am

    #1727706

    Thanks Gail.

    its SORT operator and the cost is 0%.

  • July 8, 2014 at 5:17 am

    #1727711

    Not really much you can do other than remove the need to sort entirely, and that may not be acceptable. Sort needs a lot of workspace memory so it tends to spill easily. Make sure cardinality estimations are right, make sure you aren't sorting unnecessarily.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 8, 2014 at 6:33 am

    #1727741

    Thanks Gail.

  • July 8, 2014 at 6:36 am

    #1727744

    And you can see if there's an index you can create that may help with the sort operation. It doesn't always work, but it's a possibility.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 8, 2014 at 7:24 am

    #1727761

    Thanks Grant..will try that..

Viewing 7 posts - 1 through 6 (of 6 total)

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