July 27, 2010 at 2:00 pm
My understanding was that when we rebuild the index in the background it actually drops and recreate the index. I did a quick test. I looked at the fragmentation level of table with around 10 Non-clustered and 1 -clustered index, all of them were 90% fragmented. I used DBCC DBREINDEX to rebuild ONLY Clustered index but the fragmentation level only of clustered index came down , for the non-clustered remained the same. Then i manaually ran drop and create for clustered and this time it worked. Is this strange or this is the way it is suppose to work?
July 27, 2010 at 2:09 pm
About 98% sure that in 2005 and higher that rebuilding the clustered index does not force a rebuild on all of the other indexes in the table.
Actually I just confirmed that I was right...
July 27, 2010 at 2:32 pm
so is it confirmed that rebuild clustered index WILL ONLY DROP AND CREATE CLUSTERED index and will drop/recreate the NC?
July 27, 2010 at 2:34 pm
So is it confirmed that rebuild clustered index WILL ONLY DROP AND CREATE CLUSTERED index. Period, full stop. It only reindexes the index you specifically asked it to reindex. It will not reindex any of the other indexes on the table.
July 27, 2010 at 2:42 pm
It doesn't exactly drop and recreate the index. The old index is read to create the new one, then the old is replaced by the new.
Dropping a clustered index forces a rebuild of all nonclustered indexes. Creating a clustered index does the same. Rebuilding a clustered index does not affect the nonclustered 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
July 27, 2010 at 2:55 pm
thank you so much guys, things are more clear now. There is a table with bunch of NC and one Clustered..all of them are fragmented, i think the quickest way to de-frag them would be just drop and recreate the clustered index. Any better idea?
July 27, 2010 at 3:06 pm
Do you want quick or do you want minimally disruptive. They are both options...
July 27, 2010 at 3:09 pm
both please..
July 27, 2010 at 3:09 pm
iqtedar (7/27/2010)
i think the quickest way to de-frag them would be just drop and recreate the clustered index.
Nope, that's the slowest way. Do that and you'll be rebuilding every nonclustered index twice. Waste of time, waste of resources, increased log impact
Any better idea?
ALTER INDEX ALL ON <Table> REBUILD
Will lock the table exclusive for the duration.
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
July 27, 2010 at 3:13 pm
but why do i need to rebuild all the indexes when i can just drop and recreate one index on a table?
July 27, 2010 at 3:26 pm
You asked for the quickest way of doing it.
The quickest way is to run a rebuild on each index. One rebuild. That's what that command does. It rebuilds each index once and only once.
If you drop and rebuild the clustered index, SQL will rebuild each index on the table twice. Once when you drop the clustered index. Once when you create the clustered index. So dropping and recreating a single index (the clustered index) will do almost twice the work of just rebuilding each index.
Let's say that the 10 nonclustered indexes are called idx_1 through idx_10.
The alter index ALL rebuild will do the equivalent of this.
ALTER INDEX idx_clustered ON Table REBUILD
ALTER INDEX idx_1 ON Table REBUILD
ALTER INDEX idx_2 ON Table REBUILD
ALTER INDEX idx_3 ON Table REBUILD
ALTER INDEX idx_4 ON Table REBUILD
ALTER INDEX idx_5 ON Table REBUILD
ALTER INDEX idx_6 ON Table REBUILD
ALTER INDEX idx_7 ON Table REBUILD
ALTER INDEX idx_8 ON Table REBUILD
ALTER INDEX idx_9 ON Table REBUILD
ALTER INDEX idx_10 ON Table REBUILD
Dropping and recreating the clustered index will do essentially this:
DROP INDEX idx_clustered ON Table
ALTER INDEX idx_1 ON Table REBUILD
ALTER INDEX idx_2 ON Table REBUILD
ALTER INDEX idx_3 ON Table REBUILD
ALTER INDEX idx_4 ON Table REBUILD
ALTER INDEX idx_5 ON Table REBUILD
ALTER INDEX idx_6 ON Table REBUILD
ALTER INDEX idx_7 ON Table REBUILD
ALTER INDEX idx_8 ON Table REBUILD
ALTER INDEX idx_9 ON Table REBUILD
ALTER INDEX idx_10 ON Table REBUILD
CREATE CLUSTERED INDEX idx_clustered On Table ...
ALTER INDEX idx_1 ON Table REBUILD
ALTER INDEX idx_2 ON Table REBUILD
ALTER INDEX idx_3 ON Table REBUILD
ALTER INDEX idx_4 ON Table REBUILD
ALTER INDEX idx_5 ON Table REBUILD
ALTER INDEX idx_6 ON Table REBUILD
ALTER INDEX idx_7 ON Table REBUILD
ALTER INDEX idx_8 ON Table REBUILD
ALTER INDEX idx_9 ON Table REBUILD
ALTER INDEX idx_10 ON Table REBUILD
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
July 27, 2010 at 3:30 pm
Here is an article that also supports what Gail is saying.
This is by Paul Randal http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2930)-fixing-heap-fragmentation.aspx
Though he is talking about fixed fragmentation in heaps, the article applies (drop and create v. doing as Gail says).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 27, 2010 at 3:41 pm
ic...now got a better idea. So dropping a clustered index also rebuilds a NC and again while creating a clustered index rebuilds a NC , i didnt know that. I thought dropping a clustered index also drop a NC and creating a clustered index creates a NC...thanks guys...
July 27, 2010 at 5:23 pm
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply