Index Rebuild/Reorg Order

Question

Index Rebuild/Reorg Order

rew-370421

Hall of Fame

Points: 3640
More actions
July 28, 2010 at 12:13 pm

#141481

Is there an order in which CLUSTERED and NON-CLUSTERED Indexes should be Rebuilt/Reorged?

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

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

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 1

If you do an ALTER INDEX ALL on the table, that will take care of it all for you. You would in that command do the clustered index first, and then that would rebuild the non-clustered indexes after.

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

jeff.mason (7/28/2010)
You would in that command do the clustered index first, and then that would rebuild the non-clustered indexes after.

On SQL 2005+ rebuilding the cluster does not rebuild nonclustered indexes. The ALTER INDEX ALL just goes through each index on the table and rebuilds it.

Rew: Any order. It doesn't matter.

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

rew-370421 Hall of Fame Points: 3640 More actions · Answer 3

Thank you, Gail and Jeff. I'm new to this and want to make sure I do it right.

Also, I noticed that one of the indexes has a FILL FACTOR of 90% and the other index has a FILL FACTOR of 0%

When I do a REBUILD or a REORG on these Indexes, will the FILL FACTOR stay the same along with all the other configurations that currently make up the Indexes?

...thanks again

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 4

GilaMonster (7/28/2010)
jeff.mason (7/28/2010)
You would in that command do the clustered index first, and then that would rebuild the non-clustered indexes after.
On SQL 2005+ rebuilding the cluster does not rebuild nonclustered indexes. The ALTER INDEX ALL just goes through each index on the table and rebuilds it.

Yeah, I know, old habits pre-2005 still die hard. I always do clustered first because of that and probably always will.

rew-370421 Hall of Fame Points: 3640 More actions · Answer 5

rew-370421 (7/28/2010)
Also, I noticed that one of the indexes has a FILL FACTOR of 90% and the other index has a FILL FACTOR of 0%
When I do a REBUILD or a REORG on these Indexes, will the FILL FACTOR stay the same along with all the other configurations that currently make up the Indexes?
...thanks again

From what I've read this looks to be TRUE.