Rebuild index

  • I have a table which is partitioned (Total 10). The table is 1.6 TB in size. Rebuild index on that is quite challenging, especially we have some resource constraints. I am wondering if there is a way to rebuild index, just for 1 partition. Just trying to educate myself.

  • if the index is partitioned then yes.

     

    alter index indexname on tablename rebuild partition = 3 ...

  • Chitown wrote:

    I have a table which is partitioned (Total 10). The table is 1.6 TB in size. Rebuild index on that is quite challenging, especially we have some resource constraints. I am wondering if there is a way to rebuild index, just for 1 partition. Just trying to educate myself.

    frederico has it right.

    To add to that, you mention that you have "some resource constraints".  I 'll ask you "what kind of resource constraints"?  We might be able to help a bit there.  For example, is the database using the FULL Recovery Model and can you temporarily change it to the BULK LOGGED Recovery Model without blowing up some form of replication/AG/etc?  Also, which Edition of SQL Server are you using?  Standard or Enterprise?  And, finally, are you allowed any time for the data in the table to not be accessible for index maintenance?

     

    --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)
    Intro to Tally Tables and Functions

  • Enterprise edition. DB is in simple recovery even though it is prod and we are aware of the data loss if there are any issues. 200GB of memory with 56 cores. 400GB of temp DB which isn't enough. I have run into some space issues, that's why thinking about rebuilding one of the partition.

  • At $7K per core for such a license and the number of cores you have, you should spend a relatively tiny bit more and get yourself up to 512GB of RAM.

    According to what you say, your 1.6 TB Clustered Index is partitioned by 10 and so your partitions should be about 106GB so, even before getting more RAM, rebuilding one partition at a time should do it, especially since the rebuild should be doing Minimal Logging because you're in the Simple Recovery model.  The rebuild should also be able to "go parallel" because you have the "Expensive Edition".  That is, unless you set the system max dop to 1.

    I'm not sure why TempDB should have blown out so much unless you're using the SORT_IN_TEMPDB option.

     

    --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)
    Intro to Tally Tables and Functions

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

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