DBReindex on Clustered Idex

I'm trying to reconcile a difference of information
regarding the effect of running a DBReindex on clustered
index when non-clustered indexes exist.
According to the MSCE Training Kit (SQL Server Database
Design and Implementation), the following sentence
reads "To rebuild all indexes, instruct DBCC DBReindex to
rebuild the clustered index, thereby causing a rebuild of
all indexes on a table or view".
However according to Microsoft Knowledge Base Article
304519, the symptom of bug 354670 is that using either the
Create/Drop existing syntax or DBCC DNReindex syntax on a
clustered index results in both clustered and non-
clustered indexes being rebuilt. This is consistent with
the Training Kit, but here is is mentioned as being a bug
that has been corrected with the service pack. As the
article mentions, unless a non-unique clustered index is
being rebuilt, there should be no impact on non-clustered
indexes.
Can someone point out the correct result then of applying
a rebuild on a clustered index? From my perspective, if
the condition is a bug, why was it presented not so in the
Training Kit? And if the effect of applying a service pack
does change the behaviour, it would make answering any
questions on exams difficult unless one knew the service
pack level. After all, both publications are through
Microsoft.i believe what they're calling a "bug" is that when you rebuilt a unique
clustered index before sp2, it rebuilt all of the non-clustered indexes
when it didn't need to rebuild the non-clustered indexes. see the
"more info" section of
http://support.microsoft.com/default.aspx?scid=kb;en-us;304519
Baz Star wrote:
> I'm trying to reconcile a difference of information
> regarding the effect of running a DBReindex on clustered
> index when non-clustered indexes exist.
> According to the MSCE Training Kit (SQL Server Database
> Design and Implementation), the following sentence
> reads "To rebuild all indexes, instruct DBCC DBReindex to
> rebuild the clustered index, thereby causing a rebuild of
> all indexes on a table or view".
> However according to Microsoft Knowledge Base Article
> 304519, the symptom of bug 354670 is that using either the
> Create/Drop existing syntax or DBCC DNReindex syntax on a
> clustered index results in both clustered and non-
> clustered indexes being rebuilt. This is consistent with
> the Training Kit, but here is is mentioned as being a bug
> that has been corrected with the service pack. As the
> article mentions, unless a non-unique clustered index is
> being rebuilt, there should be no impact on non-clustered
> indexes.
> Can someone point out the correct result then of applying
> a rebuild on a clustered index? From my perspective, if
> the condition is a bug, why was it presented not so in the
> Training Kit? And if the effect of applying a service pack
> does change the behaviour, it would make answering any
> questions on exams difficult unless one knew the service
> pack level. After all, both publications are through
> Microsoft.|||That is not true.
Non-clustered indexes do not contain physical links back to the clustered
index, just logical links (i.e. the cluster keys). The root node is only
stored in database metadata and so is irrelevant to this discussion.
For a unique clustered index, the cluster keys are exactly as defined by the
table schema and rebuilding a clustered index does not change any of the
cluster keys so none of the non-clustered indexes must be rebuilt.
For a non-unique clustered index, an artificial uniquifier column is added
to the defined cluster keys to give a unique logical identifier for each row
in the clustered index. Non-clustered indexes over a non-unique clustered
index have to include the uniquifier column as part of the logical link back
to the clustered index. The uniquifier is regenerated when the non-unique
clustered index is rebuilt, so if any non-clustered indexes must also be
rebuilt to pick up the new uniquifier values.
SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
rebuilt no matter what kind of clustered index was rebuilt. Not a bug per
se, but a performance drain. The problem was fixed in SP2.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sinisa Perovic" <anonymous@.discussions.microsoft.com> wrote in message
news:E493EEE6-A7EF-4BB9-8C49-08F6B54DB347@.microsoft.com...
> If the rebuild causes root node of clustered index to be changed then it
is a must for SQL Server to update(rebuild) nonclustered index no matter
wich kind!!!
> Example:
> Suppose that you're library (clustered index) is in Street One, and your
path to the library(nonclustered) is Way 1. If library moves to Street Two
there is no point to go through Way 1 as we never get to Street One, but Way
2.
> Eh, funny, isn't it?
> More info "Indexing Architecture" BOL
> Hope it Helps|||Paul,
if a non-unique clustered index contains no duplicate keys, will other
indexes still be reindexed, or is SQL-Server smart enough to only
reindex after finding a duplicate in the clustered index tree?
Thanks,
Gert-Jan
"Paul S Randal [MS]" wrote:
> That is not true.
> Non-clustered indexes do not contain physical links back to the clustered
> index, just logical links (i.e. the cluster keys). The root node is only
> stored in database metadata and so is irrelevant to this discussion.
> For a unique clustered index, the cluster keys are exactly as defined by the
> table schema and rebuilding a clustered index does not change any of the
> cluster keys so none of the non-clustered indexes must be rebuilt.
> For a non-unique clustered index, an artificial uniquifier column is added
> to the defined cluster keys to give a unique logical identifier for each row
> in the clustered index. Non-clustered indexes over a non-unique clustered
> index have to include the uniquifier column as part of the logical link back
> to the clustered index. The uniquifier is regenerated when the non-unique
> clustered index is rebuilt, so if any non-clustered indexes must also be
> rebuilt to pick up the new uniquifier values.
> SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
> rebuilt no matter what kind of clustered index was rebuilt. Not a bug per
> se, but a performance drain. The problem was fixed in SP2.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
<snip>|||They're always rebuilt and with respect, it's not a question of smart
enough.
The problem is that rebuilding the clustered index will reset all the
uniquifiers - so if the clustered index _used_ to have duplicates but
doesn't any more, the uniquifier value for the remaining unique values will
change, making the non-clustered index out of date. You could do the
theoretically do the sort using the old uniquifier values but that's very
nasty.
The alternative is to track whether a duplicate exists which is very
difficult to do and keep perf in any way respectable (think of the checks
you'd have to run for a particular key value on each update or delete).
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3FFF035B.F8D22897@.toomuchspamalready.nl...
> Paul,
> if a non-unique clustered index contains no duplicate keys, will other
> indexes still be reindexed, or is SQL-Server smart enough to only
> reindex after finding a duplicate in the clustered index tree?
> Thanks,
> Gert-Jan
>
> "Paul S Randal [MS]" wrote:
> >
> > That is not true.
> >
> > Non-clustered indexes do not contain physical links back to the
clustered
> > index, just logical links (i.e. the cluster keys). The root node is only
> > stored in database metadata and so is irrelevant to this discussion.
> >
> > For a unique clustered index, the cluster keys are exactly as defined by
the
> > table schema and rebuilding a clustered index does not change any of the
> > cluster keys so none of the non-clustered indexes must be rebuilt.
> >
> > For a non-unique clustered index, an artificial uniquifier column is
added
> > to the defined cluster keys to give a unique logical identifier for each
row
> > in the clustered index. Non-clustered indexes over a non-unique
clustered
> > index have to include the uniquifier column as part of the logical link
back
> > to the clustered index. The uniquifier is regenerated when the
non-unique
> > clustered index is rebuilt, so if any non-clustered indexes must also be
> > rebuilt to pick up the new uniquifier values.
> >
> > SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
> > rebuilt no matter what kind of clustered index was rebuilt. Not a bug
per
> > se, but a performance drain. The problem was fixed in SP2.
> >
> > Regards.
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> <snip>|||I see. I agree that it is not worth the trouble (and overhead) to do it
any other way.
Thanks for the information.
Gert-Jan
"Paul S Randal [MS]" wrote:
> They're always rebuilt and with respect, it's not a question of smart
> enough.
> The problem is that rebuilding the clustered index will reset all the
> uniquifiers - so if the clustered index _used_ to have duplicates but
> doesn't any more, the uniquifier value for the remaining unique values will
> change, making the non-clustered index out of date. You could do the
> theoretically do the sort using the old uniquifier values but that's very
> nasty.
> The alternative is to track whether a duplicate exists which is very
> difficult to do and keep perf in any way respectable (think of the checks
> you'd have to run for a particular key value on each update or delete).
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:3FFF035B.F8D22897@.toomuchspamalready.nl...
> > Paul,
> >
> > if a non-unique clustered index contains no duplicate keys, will other
> > indexes still be reindexed, or is SQL-Server smart enough to only
> > reindex after finding a duplicate in the clustered index tree?
> >
> > Thanks,
> > Gert-Jan
> >
> >
> > "Paul S Randal [MS]" wrote:
> > >
> > > That is not true.
> > >
> > > Non-clustered indexes do not contain physical links back to the
> clustered
> > > index, just logical links (i.e. the cluster keys). The root node is only
> > > stored in database metadata and so is irrelevant to this discussion.
> > >
> > > For a unique clustered index, the cluster keys are exactly as defined by
> the
> > > table schema and rebuilding a clustered index does not change any of the
> > > cluster keys so none of the non-clustered indexes must be rebuilt.
> > >
> > > For a non-unique clustered index, an artificial uniquifier column is
> added
> > > to the defined cluster keys to give a unique logical identifier for each
> row
> > > in the clustered index. Non-clustered indexes over a non-unique
> clustered
> > > index have to include the uniquifier column as part of the logical link
> back
> > > to the clustered index. The uniquifier is regenerated when the
> non-unique
> > > clustered index is rebuilt, so if any non-clustered indexes must also be
> > > rebuilt to pick up the new uniquifier values.
> > >
> > > SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
> > > rebuilt no matter what kind of clustered index was rebuilt. Not a bug
> per
> > > se, but a performance drain. The problem was fixed in SP2.
> > >
> > > Regards.
> > >
> > > --
> > > Paul Randal
> > > Dev Lead, Microsoft SQL Server Storage Engine
> > <snip>