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.
| 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. |
 |
|
|
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.aspxIt 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! |
|
|
|
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 ShawSQL Server MVP |
|
|
|
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 ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Defrag/Rebuild index by index .... what order?