SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cluster Re-Index


Cluster Re-Index

Author
Message
andriancruz
andriancruz
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 69
Hi Everyone,

I just one want to ask, let say, if you re-index the cluster index in one table, it means all the non-clustered index also included?

Your comment is highly appreciated.

Thank you & regards
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86484 Visits: 45239
No.

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


andriancruz
andriancruz
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 69
Thanks for the reply. Is there any article, or even in the BOL that proves if you do re-index the clustered index the non-clustered indexed is not included?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86484 Visits: 45239
You could simply test it and prove it to yourself.

It's stated here: http://msdn.microsoft.com/en-us/library/ms189858.aspx (table half way down titled "Rebuilding an Index") and here: http://msdn.microsoft.com/en-us/library/ms188388%28SQL.90%29.aspx (under the section REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]) and likely in several other places too.

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


NJ-DBA
NJ-DBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1465 Visits: 1546
IF you drop and recreate a clustered index, nonclustered indexes are also recreated. This article explains it well: http://www.sql-server-performance.com/faq/rebuild_clustered_index_p1.aspx

Typically, I do this: Script all indexes, drop all nonclustered indexes, drop clustered index, create clustered index, create nonclustered indexes....

seems to work pretty fast.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86484 Visits: 45239
NJ-DBA (9/3/2010)
IF you drop and recreate a clustered index, nonclustered indexes are also recreated.


If you drop and recreate a clustered index, all nonclustered indexes are recreated twice. Once when the cluster is dropped, once when it's recreated

Typically, I do this: Script all indexes, drop all nonclustered indexes, drop clustered index, create clustered index, create nonclustered indexes....


Fine if you're doing batch loads or data and don't want the overhead of the indexes during the load process. Utterly silly if all you're trying to do is defragment indexes.

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


NJ-DBA
NJ-DBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1465 Visits: 1546
Hi Gail, you've posted help for me in the past, so I assume you are right and I am wrong, but let me make sure I understand what you are saying. If I recreate the clustered index, I'm recreating the nonclustered indexes twice... agreed not optimal. So instead I drop nonclustered first, then do the clustered, then the non clustered.... what is so silly about this? Are you saying better to just rebuild the clustered index? I have found through experience, especially when there are lots of large nonclustered indexes, that it actually goes faster if I do the method I explained.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86484 Visits: 45239
If your goal is to rebuild indexes (remove fragmentation) just rebuild them. The rebuild has several algorithms it can use, including in most cases reading the old index to create the new one, something that isn't possible if you drop the index.

Dropping and later recreating indexes is for when you want to bulk-load data and get it in fast without worrying about the overhead of the indexes.

For removing fragmentation, use ALTER INDEX ... REBUILD or REORGANISE, depending on the fragmentation and size of the indexes. If you want to just rebuild everything - ALTER INDEX ALL ON <Table> REBUILD.
If you drop then create there are downsides. Firstly you're possibly allowing duplicate data in while the unique indexes are not present. You're also increasing the logging impact. Finally if the definition of the index is changed and you forget to change your 'rebuild' script, those changes will be lost the next time the index is rebuilt.

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


andriancruz
andriancruz
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 69
Thanks guys for the link its very informative.
rivermorrison22
rivermorrison22
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
I was wondering about this too. If you have a clustered index on the table, the data is stored in the root level of the b-tree right? So when you re-index, you move the data around. That would make the non-clustered indexes invalid right? So it must update those indexes, just not rebuild them.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search