asked
Which Index has to be rebuilt 1st ?
There is a table with a Clustered Index and a Non- Clustered Index. Now I want to rebuild the Index's. Which Index has to be rebuilt 1st and Why ?
Neither has to be rebuilt first. If you rebuild the non-clustered first and then the clustered you are wasting time since rebuilding the clustered index will rebuild all non-clustered indexes. So the answer is, if you have to rebuild the clustered index, then only rebuild it.
Sorry to say that but it is not true that non clustered indexes are rebuild when you're rebuilding the clustered index. [Take a look here (Myth 3)][1] and [here][2] You will affect all non clustered indexes if you DROP and RECREATE your clustered index or if you change the definition of the CI. But to answer the question itself: there's no special order when defragmenting/rebuilding an index. Just make sure that you do not affect any running workload, e.g. shift your maintenance to off-hours. Regards Dirk [1]:
http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-19-Misconceptions-around-index-rebuilds-%28allocation-BULK_LOGGED-mode-locking%29.aspx [2]:
http://blogs.msdn.com/b/khen1234/archive/2007/02/27/does-rebuilding-a-clustered-index-rebuild-nonclustered-indexes.aspx
You are correct, for some reason I read the question as creating a clustered index when non-clustered indexes are present, which will rebuild the non-clustered indexes. Need more coffee...
In my opinion there is a certain order that fragmentation should be dealt with - leaving the clustered/non-clustered details to one side and considering that all indexes are equal, then; - if an index is unused then there is little point defragmenting it. After due investigation around reasons for it not being used then simply delete it. - if an index is used and is fragmented then its characteristics needs to be considered - things like how big is the index (ie how many pages?), how fragmented it is and how quickly it became this fragmented. If it is a large index (this may well coincide with it being on a big/busy table in your system). Defragmenting this may take a long time and, depending on your SQL Server edition, may cause a performance hit while the table is locked. If the index is highly fragmented and has recently been defragmented then this may point to the index being inappropriately configured in some (things like fill factor, clustering column etc), if it has built up over a long time then a simply defrag may well fix things for a long time to come. Hope this helps
10 People are following this question.
