How much will it cost or save to rebuild that index? (SQL Oolie)

  • Jeff Moden

    SSC Guru

    Points: 996837

    Comments posted to this topic are about the item How much will it cost or save to rebuild that index? (SQL Oolie)

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Fat Uncle Dan

    Valued Member

    Points: 54

    Thanks for a great article. I am not a performance tuning expert, but I have learnt enough here to be dangerous.

    I don't want to be "that" guy and it doesn't bother me when others do it, but I know when I do it, it irritates me to no end. I assume "justification will be to identity how much disk space" should read "justification will be to identify how much disk space".

  • Jeff Moden

    SSC Guru

    Points: 996837

    Fat Uncle Dan wrote:

    Thanks for a great article. I am not a performance tuning expert, but I have learnt enough here to be dangerous.

    I don't want to be "that" guy and it doesn't bother me when others do it, but I know when I do it, it irritates me to no end. I assume "justification will be to identity how much disk space" should read "justification will be to identify how much disk space".

    Your correction is correct.  Thanks for the feedback both on the correction and on the article.  Much appreciated.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996837

    Interesting...  At the time of this post, there were 3 votes with an average of 3.67.  That might mean two 5's and a 1 or two 4's and a 3.  It would really be nice if someone told me why they rated the article as a "1" (if that's what happened) so that I can improve either the subject matter or the methods used in writing the article and proving the findings in future articles.

    If it turns out to be two 4's and a 3, that's not so bad but it still leaves me wondering what I may have done wrong.  Was it the subject, the proofs, too long for the subject, or ???

    Thanks for for any additional feedback on any problems observed, whomever/whoever you are.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Eric M Russell

    SSC Guru

    Points: 125094

    I just now rated the article 3 out of 5, because while it's well written, no discussion of very large table index maintenance would be complete without mentioning the PARTITION = {partition_number} option. When rebuilding or reorganizing a partitioned table, it's essential to do this incrementally, or even better, do it selectively based on which specific partitions exceed the threshold for fragmentation.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Jeff Moden

    SSC Guru

    Points: 996837

    Eric M Russell wrote:

    I just now rated the article 3 out of 5, because while it's well written, no discussion of very large table index maintenance would be complete without mentioning the PARTITION = {partition_number} option. When rebuilding or reorganizing a partitioned table, it's essential to do this incrementally, or even better, do it selectively based on which specific partitions exceed the threshold for fragmentation.

    Great point.  Thanks for the feedback, Eric.  I'll add that to the article and resubmit it tonight.  Thanks for your time.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • as1981

    SSCrazy

    Points: 2745

    Thanks for a useful article.

    A related question (please advise if it would be better as a separate topic) would be if it's possible and, if it is, how to work out how long a reorganise or rebuild will take on a particular system.

    I/O time might be possible to work out by using the page count and historical disk performance from performance monitor on a Windows system (I'm not sure of the equivalent on Linux - maybe the top command?)? I am not sure about CPU time though?

    I'm just thinking about the user/management question - "OK, so you want downtime, how long for?".

    Sorry if this is the wrong place for the question or the information is in your article and I missed it.

  • Jeff Moden

    SSC Guru

    Points: 996837

    @as1981 ,

    Apologies for missing your question on this.

    Yes, it's possible to work it out from historical information as you've said but it's still pretty much of a SWAG simply because you can't control what else is happening on your server.  For example, a rebuild of a given index might normally take X minutes but, if someone has a lock on the index for some reason, the rebuild won't even start until that lock clears up.  Even if you rebuild the index "ONLINE", the activity in the table may impact the index rebuild in that it needs to update that "update" table when such online actions occur.  Even if you're the only one using the entire database, if something like a backup occurs, that will impact the rebuild.

    So, the bottom line is that you can make a guess based on things like history, column widths, column types, etc but there is so much that could affect the amount of time to do a REBUILD or REORGANIZE that the guess could be wildly different that what actually happens even if the index isn't a part of what else is happening.

     

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • as1981

    SSCrazy

    Points: 2745

    No problem and thanks for your advice

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

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