Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Cluster Re-Index Expand / Collapse
Author
Message
Posted Friday, September 03, 2010 2:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 14, 2011 2:39 AM
Points: 7, Visits: 67
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
Post #980070
Posted Friday, September 03, 2010 2:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
No.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #980079
Posted Friday, September 03, 2010 3:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 14, 2011 2:39 AM
Points: 7, Visits: 67
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?
Post #980082
Posted Friday, September 03, 2010 3:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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 2008, MVP
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

Post #980101
Posted Friday, September 03, 2010 7:41 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:32 PM
Points: 881, Visits: 1,402
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.
Post #980201
Posted Friday, September 03, 2010 7:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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 2008, MVP
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

Post #980212
Posted Friday, September 03, 2010 8:17 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:32 PM
Points: 881, Visits: 1,402
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.
Post #980227
Posted Friday, September 03, 2010 8:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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 2008, MVP
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

Post #980244
Posted Sunday, September 05, 2010 6:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 14, 2011 2:39 AM
Points: 7, Visits: 67
Thanks guys for the link its very informative.
Post #980873
Posted Monday, October 28, 2013 9:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 28, 2013 10:39 AM
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.
Post #1508964
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse