x

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 ?
more ▼

asked Sep 09, 2012 at 12:02 PM in Default

ranjeeth.svn gravatar image

ranjeeth.svn
20 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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) and here

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

more ▼

answered Sep 26, 2012 at 01:26 PM

DirkHondong gravatar image

DirkHondong
1.3k 15 17 19

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...
Sep 29, 2012 at 01:05 PM Scot Hauder
No problem :)
Sep 29, 2012 at 01:10 PM DirkHondong
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Sep 30, 2012 at 09:27 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Sep 09, 2012 at 06:10 PM

Scot Hauder gravatar image

Scot Hauder
6.1k 13 15 18

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x65

asked: Sep 09, 2012 at 12:02 PM

Seen: 937 times

Last Updated: Sep 30, 2012 at 09:27 AM