Post reply

Please Help - trying to eliminate "used tempdb to spill data"

  • July 13, 2015 at 7:26 am

    #1811956

    jghali (7/13/2015)

    Thanks for your response...

    1. the execution plans of the Row_number for:

    SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY [UPC], [LocGroup] ORDER BY PriceLvl) FROM #Master_TAB

    SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY [LocGroup], [UPC] ORDER BY PriceLvl) FROM #Master_TAB

    Tells me that the sort is invoked in the second query due to the fact that there's no index that covers [LocGroup], [UPC]...

    The first of the 2 queries doesn't invoke a sort due to the fact that [UPC], [LocGroup] is covered by the PK .

    Correct. Note however that these columns appear as part of a partition definition - and the order of the columns doesn't matter.

    2. correct me if I'm wrong or if I misunderstood, Although the execution plans of the queries are relative, For a query cost, 2 sort spills will have 2 lower costs overall, compared to 1 spill that will have 1 higher %.

    conclusion is that sometimes you can't avoid the spills but you need to manage them. right?

    This is confusing! Sorts spill when the data doesn't match the plan, which isn't all of the time. The two sorts in your query, without spills, are likely to be a similar cost because the number of rows encountered is similar. The test harness you very generously provided doesn't exhibit a spill.

    3. RE:"Thirdly, a spill occurs when the actual number of rows being sorted exceeds the estimate."

    That's one case... In my case the statistics are up to date and the estimated rows match the actual rows.

    The optimiser isn't perfect.

    Based on the articles I read and this thread, We have to try to avoid Spills but sometimes it's just not feasible and you need to minimize the number of spills...

    Deal with them on those occasions when they appear in your execution plans. If your queries are intelligently written, your indexes have been thoughtfully constructed to support usage and your statistics are up to date, then you can expect fewer spilling sorts than otherwise.

    Is this a good conclusion?

    thank you

    JG

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • July 13, 2015 at 9:59 am

    #1812060

    Thank you very much for time and effort...

    Very much appreciated.

    🙂

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

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