Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Defrag/Rebuild index by index .... what order?

Author  Topic 

ekelmans
Starting Member

7 Posts

Posted - 2011-03-27 : 07:02:48

Hi,

Ive build a script that analyzes the frag, fill and forward levels for each cix/heap/non-cix in every db on a server. The script works like a charm, but one thing is bothering me..... and i can't find a clear answer in BOL and fora (yet).

When you Defrag/Rebuild index by index .... what order is best.... Clustered first, then non-clustered? Or does defragging / rebuilding the CIX allso AUTOMATICALLY defrags / rebuilds all NON-CIX on that table?

The logic goes like: you can rebuild / defrag each of the NON-CIX separately, unless you allso need to do the CIX.

Has anyone found any definitive answer in the SQL docs?

Theo (NL)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-27 : 07:30:18
If you're not going to rebuild the Clustered Index, just do the non-clustered in any order.

If you are going to rebuild the clustered index, just do that one. Rebuilding clustered rebuilds the non-clustered indexes as well.
Go to Top of Page

ekelmans
Starting Member

7 Posts

Posted - 2011-03-27 : 07:44:26
@russel...

Thats what i thought as well, till i found this page: http://technet.microsoft.com/en-us/library/ms189858.aspx

It says: Rebuilding a clustered index rebuilds associated nonclustered indexes. No... Unless the keyword ALL is specified.

This trew me for a loop, and i cant verify it ANYWHERE, if this behavior is SQL 2008 R2 specific!
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-27 : 10:00:20
quote:
Originally posted by russell

If you are going to rebuild the clustered index, just do that one. Rebuilding clustered rebuilds the non-clustered indexes as well.


Not in SQL 2005 or above it does not.

Even in SQL 2000 it only did if the cluster was not unique.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-27 : 10:04:40
quote:
Originally posted by ekelmans

This trew me for a loop, and i cant verify it ANYWHERE, if this behavior is SQL 2008 R2 specific!


No offence, but you didn't look hard. On that same page that you linked, at the top there's a 'Other versions are also available for the following: SQL Server 2005" link. Click that and you get the 2005 version, which states:

"Rebuilding a clustered index rebuilds associated nonclustered indexes.

No. Unless the keyword ALL is specified."

--
Gail Shaw
SQL Server MVP
Go to Top of Page
   

- Advertisement -