<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
Cluster Re-Index
Cluster Re-Index
Rate Topic
Display Mode
Topic Options
Author
Message
andriancruz
andriancruz
Posted Friday, September 03, 2010 2:18 AM
Forum 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
GilaMonster
GilaMonster
Posted Friday, September 03, 2010 2:57 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 37,651,
Visits: 29,903
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
andriancruz
andriancruz
Posted Friday, September 03, 2010 3:09 AM
Forum 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
GilaMonster
GilaMonster
Posted Friday, September 03, 2010 3:45 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 37,651,
Visits: 29,903
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
NJ-DBA
NJ-DBA
Posted Friday, September 03, 2010 7:41 AM
SSC Eights!
Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 858,
Visits: 1,328
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
GilaMonster
GilaMonster
Posted Friday, September 03, 2010 7:52 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 37,651,
Visits: 29,903
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
NJ-DBA
NJ-DBA
Posted Friday, September 03, 2010 8:17 AM
SSC Eights!
Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 858,
Visits: 1,328
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
GilaMonster
GilaMonster
Posted Friday, September 03, 2010 8:39 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 37,651,
Visits: 29,903
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
andriancruz
andriancruz
Posted Sunday, September 05, 2010 6:57 PM
Forum 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
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
