Index rebuild completion on a partitioned table

  • We have a huge database of 2TB and one of the table with size of around 600GB.For simplicity say its partitioned across 12 partitions(per month)

    If we are running an index rebuild without specifying to rebuild based on partition number and offline (without online rebuild option) and the command just keeps running for few hours so,

    Is there a way/script to cheek how much rebuild is completed with respect to each partition? And how many partitions are remaining to rebuilt?

  • Apologies for the observation but with all the alphabet soup after your name, you don't know?

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • If you specify the rebuild without specifying the partition, then you're getting a full index rebuild. It's not done one partition at a time, it's done as one operation on the entire index in one go. If you want to rebuild one partition at time, you need to specify the index rebuilds one partition at a time.

    Typically tables that are partitioned across months have static data in the older partitions. If that's the case here, why are you wasting time by rebuilding the entire thing rather than just rebuilding the partitions that need it.

    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
  • Hi Gail,

    Many thanks for your time and help..!

    We accidentally ran index rebuild without mentioning the partition number and it was just not finishing..and not letting us to decide whether we should wait for it to complete or kill it.

    So was just wondering if we have any DMV to check the status partition wise..

  • Apologies Jeff...but being certified with these doesnt mean one should know everything about SQL Server .. :ermm:

  • Thanks

    SQL database is best option for Utah Motivational Speaker websites.

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

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