Post reply

Re-Indexing a large table

  • August 21, 2014 at 12:18 am

    #310839

    I need to reorg/rebuild the indexes on a large table (8.7 billion rows of data), should I rebuild the clustered index first or the non-clustered indexes first, or does it not matter?

    Thanks,

  • August 21, 2014 at 1:18 am

    #1739783

    Doesn't matter at all (providing you aren't using SQL 2000)

    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

  • August 21, 2014 at 1:29 am

    #1739784

    Thanks Gail, that helps with the order of operations 🙂

    Is there some literature I can read up on that explains why it doesn't matter?

    Thanks Again.

    -Tom

  • August 21, 2014 at 1:53 am

    #1739789

    This question have been answered over here

  • August 21, 2014 at 2:00 am

    #1739790

    Excellent, thanks very much.

  • August 21, 2014 at 2:31 am

    #1739799

    ReamerXXVI (8/21/2014)

    Is there some literature I can read up on that explains why it doesn't matter?

    Order doesn't matter because they're independent operations, they don't interfere or interact with each other, hence it has no difference at all what order you do them

    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

  • August 21, 2014 at 10:42 pm

    #1740070

    ReamerXXVI (8/21/2014)

    I need to reorg/rebuild the indexes on a large table (8.7 billion rows of data), should I rebuild the clustered index first or the non-clustered indexes first, or does it not matter?

    Thanks,

    Gail answered the question that you asked.

    To simplify and shorten such reindexing of this large table, have you considered either Paritioned Views or Partitioned Tables?

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

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

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