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

  • Jeff Moden

    SSC Guru

    Points: 995467

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • Fat Uncle Dan

    Valued Member

    Points: 53

    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: 995467

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • Jeff Moden

    SSC Guru

    Points: 995467

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • Eric M Russell

    SSC Guru

    Points: 125044

    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: 995467

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • as1981

    SSCrazy

    Points: 2565

    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.

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

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