Rebuild index

Question

Post reply

Rebuild index

Chitown

Hall of Fame

Points: 3239
More actions
September 16, 2021 at 4:19 pm

#3930213

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.

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

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

if the index is partitioned then yes.

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

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)

Chitown Hall of Fame Points: 3239 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

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)