Index rebuild completion on a partitioned table

Question

Post reply

Index rebuild completion on a partitioned table

U J

SSCrazy

Points: 2423
More actions
March 16, 2016 at 9:52 pm

#305737

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?

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

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

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.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

U J SSCrazy Points: 2423 More actions · Answer 3

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