DBCC DBREINDEX Behaviour

Folks,
I have a table with a clustered index and 6 non-clustered indexes. When I
issue a DBCC DBREINDEX without specifying a specific index all indexes are
rebuilt including the clustered index - this takes 3 hours to complete and
consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying only th
e
clustered index (which happens to be supporting the primary key) I've read
that all non-clustered indexes are also rebuilt - this takes on 1.5 hours an
d
consumes 6gb of Tlog space. I have 3 questions:
1) If you use DBCC DBREINDEX without specifying an index, are the
non-clustered indexes rebuilt twice on a table with a clustered index?
2) I would like to confirm that all indexes are being rebuilt. Is the time
the index is created captured, if so, how can I view the time?
3) Why is this a logged transaction? I would have thought that SQL Server
would create the indexes without dropping the originals, and then simply swa
p
and drop once the index has completed.
Thanks in advance for your help.
--
Scott H.It really helps to specify what version and service pack you are using. In
this case it can make a big difference. If I remember correctly in the RTM
and maybe SP1 versions of SQL2000 it worked like this:
If you rebuild the clustered index ( CI ) all non-clustered indexes ( NCI)
were always rebuilt as well. This was due to the way in which the CI key was
appended to the end of all NCI's and would change during a rebuild.
With one of the SP's (I think SP2) that behavior changed in that if the CI
was unique and you rebuilt the CI by specifying only that index it did not
rebuild the NCI's. But if the CI was not unique they would add a uniquifer
(4 byte code) to the CI which got regenerated each time the CI was rebuilt.
Since the CI (including the uniqueifier) was appended to the end of all
NCI's they in turn needed to be rebuilt as well.
IN SQL2005 they changed the way they generated the uniqifier and it no
longer changes when the CI is rebuilt. So there is no need to rebuild all
the NCI's just because you rebuild the CI.
But to answer your specific questions a little more:

> 1) If you use DBCC DBREINDEX without specifying an index, are the
> non-clustered indexes rebuilt twice on a table with a clustered index?
No. SQL Server was smart enough in all versions to only rebuild the NCI's
once.

> 2) I would like to confirm that all indexes are being rebuilt. Is the time
> the index is created captured, if so, how can I view the time?
No but you can look at DBCC SHOWCONTIG or SHOWSTATISTICS before and after
you can see the differences and will see where to look to see if work was
done.

> 3) Why is this a logged transaction? I would have thought that SQL Server
> would create the indexes without dropping the originals, and then simply
> swap
> and drop once the index has completed.
If you are in FULL recovery mode everything is always fully logged. If you
are in Bulk-Logged or Simple mode some index create or rebuild operations
can be minimally logged. So your transaction log file may not grow very much
but when you backup the log file (if in Bulk-logged) the backup will include
all the extents changed byt he operations.
Andrew J. Kelly SQL MVP
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:A76DD6A4-4D1D-40F2-AC8E-F94C9042F42C@.microsoft.com...
> Folks,
> I have a table with a clustered index and 6 non-clustered indexes. When I
> issue a DBCC DBREINDEX without specifying a specific index all indexes are
> rebuilt including the clustered index - this takes 3 hours to complete and
> consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying only
> the
> clustered index (which happens to be supporting the primary key) I've read
> that all non-clustered indexes are also rebuilt - this takes on 1.5 hours
> and
> consumes 6gb of Tlog space. I have 3 questions:
> 1) If you use DBCC DBREINDEX without specifying an index, are the
> non-clustered indexes rebuilt twice on a table with a clustered index?
> 2) I would like to confirm that all indexes are being rebuilt. Is the time
> the index is created captured, if so, how can I view the time?
> 3) Why is this a logged transaction? I would have thought that SQL Server
> would create the indexes without dropping the originals, and then simply
> swap
> and drop once the index has completed.
> Thanks in advance for your help.
> --
> Scott H.|||Thanks Andrew.
SQL Server 2000 SP3
The CI is unique, so it would appear to be rebuilding only the CI.
I re-ran the test in simple recovery mode, and the TLOG grew to only 190mb.
I guess in production I could put the server into single user mode, change t
o
simple recovery mode, run the reorg, full backup, return to full rcovery
mode. Seems like overkill. The reason I may follow this route is that we are
having disk capacity issues.
I have an application team thinking that rebuilding (DBREINDEX) this 17
million record table is a good thing to do nightly 7 days/week. I need to di
g
up supporting evidence one way or the other. I'll make use of SHOWCONTIG
prior to the execution over the next few days to determine. If there is
anything else you think could be beneficial feel free to offer. I've recentl
y
inherited the support of this instaance and there is much work to do.
Thanks for your help.
--
Thanks,
Scott H.
"Andrew J. Kelly" wrote:

> It really helps to specify what version and service pack you are using. In
> this case it can make a big difference. If I remember correctly in the RTM
> and maybe SP1 versions of SQL2000 it worked like this:
> If you rebuild the clustered index ( CI ) all non-clustered indexes ( NCI)
> were always rebuilt as well. This was due to the way in which the CI key w
as
> appended to the end of all NCI's and would change during a rebuild.
> With one of the SP's (I think SP2) that behavior changed in that if the CI
> was unique and you rebuilt the CI by specifying only that index it did not
> rebuild the NCI's. But if the CI was not unique they would add a uniquifer
> (4 byte code) to the CI which got regenerated each time the CI was rebuilt
.
> Since the CI (including the uniqueifier) was appended to the end of all
> NCI's they in turn needed to be rebuilt as well.
> IN SQL2005 they changed the way they generated the uniqifier and it no
> longer changes when the CI is rebuilt. So there is no need to rebuild all
> the NCI's just because you rebuild the CI.
> But to answer your specific questions a little more:
>
> No. SQL Server was smart enough in all versions to only rebuild the NCI's
> once.
>
> No but you can look at DBCC SHOWCONTIG or SHOWSTATISTICS before and after
> you can see the differences and will see where to look to see if work was
> done.
>
> If you are in FULL recovery mode everything is always fully logged. If you
> are in Bulk-Logged or Simple mode some index create or rebuild operations
> can be minimally logged. So your transaction log file may not grow very mu
ch
> but when you backup the log file (if in Bulk-logged) the backup will inclu
de
> all the extents changed byt he operations.
> --
> Andrew J. Kelly SQL MVP
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:A76DD6A4-4D1D-40F2-AC8E-F94C9042F42C@.microsoft.com...
>
>|||Hi Scott
You might want to consider switching to bulk_logged mode instead of simple.
The logging should be about the same, and you won't have to do a full db
backup after, on a tlog backup. Situations like this are exactly what
bulk_logged mode is intended for, i.e. so that you can do large bulk
operations, like data loads or index rebuilds, that normally are log
intensive, and make then less log intensive. Switching to bulk_logged mode
allows your chain of tlog backups to remain intact and again, no full backup
is required.
You might want to read more details about the different recovery models in
the Books Online and also this KB article might help:
A transaction log grows unexpectedly or becomes full on a computer that is
running SQL Server
http://support.microsoft.com/kb/317375/en-us
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:6355DB27-5D19-4A8F-A32D-5A2588D275D7@.microsoft.com...[vbcol=seagreen]
> Thanks Andrew.
> SQL Server 2000 SP3
> The CI is unique, so it would appear to be rebuilding only the CI.
> I re-ran the test in simple recovery mode, and the TLOG grew to only
> 190mb.
> I guess in production I could put the server into single user mode, change
> to
> simple recovery mode, run the reorg, full backup, return to full rcovery
> mode. Seems like overkill. The reason I may follow this route is that we
> are
> having disk capacity issues.
> I have an application team thinking that rebuilding (DBREINDEX) this 17
> million record table is a good thing to do nightly 7 days/week. I need to
> dig
> up supporting evidence one way or the other. I'll make use of SHOWCONTIG
> prior to the execution over the next few days to determine. If there is
> anything else you think could be beneficial feel free to offer. I've
> recently
> inherited the support of this instaance and there is much work to do.
> Thanks for your help.
> --
> Thanks,
> Scott H.
>
> "Andrew J. Kelly" wrote:
>|||Scott,
I second Kalen's advise about using Bulklogged vs Simple if you need to go
that way. Just remember that the Log backup files will be large regardless
if you have space issues. Hopefully you are not backing up to the same drive
array as the data is on anyway. But most systems rarely require an index
rebuild nightly and certainly not for all tables in the db. There is a
sample script in BooksOnLine under DBCC SHOWCONTIG that will allow you to
only reindex or Defrag the indexes that are above a certain fragmentation
level anyway. This should dramatically cut down the tlog space requirements
as well. These articles (especially the first one) are worth reading.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.c..._showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/col...
illfactors.asp
Fill Factors
http://www.sql-server-performance.c...red_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:6355DB27-5D19-4A8F-A32D-5A2588D275D7@.microsoft.com...[vbcol=seagreen]
> Thanks Andrew.
> SQL Server 2000 SP3
> The CI is unique, so it would appear to be rebuilding only the CI.
> I re-ran the test in simple recovery mode, and the TLOG grew to only
> 190mb.
> I guess in production I could put the server into single user mode, change
> to
> simple recovery mode, run the reorg, full backup, return to full rcovery
> mode. Seems like overkill. The reason I may follow this route is that we
> are
> having disk capacity issues.
> I have an application team thinking that rebuilding (DBREINDEX) this 17
> million record table is a good thing to do nightly 7 days/week. I need to
> dig
> up supporting evidence one way or the other. I'll make use of SHOWCONTIG
> prior to the execution over the next few days to determine. If there is
> anything else you think could be beneficial feel free to offer. I've
> recently
> inherited the support of this instaance and there is much work to do.
> Thanks for your help.
> --
> Thanks,
> Scott H.
>
> "Andrew J. Kelly" wrote:
>|||If your team still doesn't believe you, send me email (through the blog
below) and I'll have a con-call with you/them and convince them (I wrote
DBCC INDEXDEFRAG and SHOWCONTIG).
Cheers
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eHLY5EwbHHA.4176@.TK2MSFTNGP02.phx.gbl...
> Scott,
> I second Kalen's advise about using Bulklogged vs Simple if you need to go
> that way. Just remember that the Log backup files will be large regardless
> if you have space issues. Hopefully you are not backing up to the same
> drive array as the data is on anyway. But most systems rarely require an
> index rebuild nightly and certainly not for all tables in the db. There is
> a sample script in BooksOnLine under DBCC SHOWCONTIG that will allow you
> to only reindex or Defrag the indexes that are above a certain
> fragmentation level anyway. This should dramatically cut down the tlog
> space requirements as well. These articles (especially the first one) are
> worth reading.
>
> http://www.microsoft.com/technet/pr..._showcontig.asp Understanding
> DBCC SHOWCONTIG
> http://www.sqlservercentral.com/col...fillfactors.asp
> Fill Factors
> http://www.sql-server-performance.c...red_indexes.asp Clustered
> Indexes
> --
> Andrew J. Kelly SQL MVP
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:6355DB27-5D19-4A8F-A32D-5A2588D275D7@.microsoft.com...
>|||Just to piggy back on this thread. I didn't realise switching from
full to bulk logged would keep the transaction log chain active.
Makes interesting reading considering we do reindexing once a week.
We then have to feed 5 reporting servers via a log shipping type of
system (written internally).
Cheers,
Clive|||Andrew/Kalen/Paul,
Thank you all for your responses. What a great user group (I'd almost
forgotten). I've been off on other assignements these past few years and am
now just getting my hands "dirty" once again with SQL Server. I have
forgotten much, but am having a great time getting back into it. I spent the
entire weekend reading/testing/playing.
Andrew, I did find that script in the BOL. I do plan on using it in its
entirely, but also stole a chunk out of it and modified it slightly. I'm
going to use it to collect and archive these stats for all
instances/databases daily.
Paul, great offer. I work for a large outsourcing company, this particular
client can be a little difficult at times to convince. I hope the informatio
n
I collect using procAutoIndex is enough, if it's not I may just take you up
on the offer. I'll share my interpretation of the results (for one or two
tables) once I get them. Perhaps you can tell me if I'm correct or not.
Kalen, I'm going to work towards automating this procedure including putting
the database into bulk-logged mode. Hopefuly this will become a weekly
procedure and can be done during the weekend where impact is reduced.
By the way, I very much enjoy your articles in SSM. Thank-you.
--
Thanks,
Scott H.
"Scott H." wrote:

> Folks,
> I have a table with a clustered index and 6 non-clustered indexes. When I
> issue a DBCC DBREINDEX without specifying a specific index all indexes are
> rebuilt including the clustered index - this takes 3 hours to complete and
> consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying only
the
> clustered index (which happens to be supporting the primary key) I've read
> that all non-clustered indexes are also rebuilt - this takes on 1.5 hours
and
> consumes 6gb of Tlog space. I have 3 questions:
> 1) If you use DBCC DBREINDEX without specifying an index, are the
> non-clustered indexes rebuilt twice on a table with a clustered index?
> 2) I would like to confirm that all indexes are being rebuilt. Is the time
> the index is created captured, if so, how can I view the time?
> 3) Why is this a logged transaction? I would have thought that SQL Server
> would create the indexes without dropping the originals, and then simply s
wap
> and drop once the index has completed.
> Thanks in advance for your help.
> --
> Scott H.|||Thank-you Kalen.
--
Scott H.
"Kalen Delaney" wrote:

> Hi Scott
> You might want to consider switching to bulk_logged mode instead of simple
.
> The logging should be about the same, and you won't have to do a full db
> backup after, on a tlog backup. Situations like this are exactly what
> bulk_logged mode is intended for, i.e. so that you can do large bulk
> operations, like data loads or index rebuilds, that normally are log
> intensive, and make then less log intensive. Switching to bulk_logged mode
> allows your chain of tlog backups to remain intact and again, no full back
up
> is required.
> You might want to read more details about the different recovery models in
> the Books Online and also this KB article might help:
> A transaction log grows unexpectedly or becomes full on a computer that is
> running SQL Server
> http://support.microsoft.com/kb/317375/en-us
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:6355DB27-5D19-4A8F-A32D-5A2588D275D7@.microsoft.com...
>
>