Morning,
Is there a specific command/clause in the DBCC SHRINKDATABASE command where I can force a database to release free space to the operating system ?
Cheers,
DuncanMorning,
Is there a specific command/clause in the DBCC SHRINKDATABASE command where I can force a database to release free space to the operating system ?
Cheers,
Duncan
yes . use TRUNCATEONLY.
from BOL:
TRUNCATEONLY
Causes any unused space in the data files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_percent is ignored when TRUNCATEONLY is used.|||See BOL for it
DBCC SHRINKDATABASE ( database_name , TRUNCATEONLY )
TRUNCATEONLY will reduce all free space until last extension allocated.
Else, try
DBCC SHRINKDATABASE ( database_name , 1 )
to release all less 1 percent|||Thanks fellas but I've tried both of those options and the database in question has automatically grabbed any existing space as free space. Both commands completed after only a few seconds.
FYI - The database is set to autogrow but only in very small amounts. The database grabs far more free space.
Is it worth turning off autogrow or is it possible that the database has been corrupted in some way ?
Any ideas ?
Showing posts with label specific. Show all posts
Showing posts with label specific. Show all posts
Sunday, March 25, 2012
Wednesday, March 7, 2012
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 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.
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 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:
> 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:
>
> 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 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...
>
>
|||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/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
Fill Factors
http://www.sql-server-performance.com/gv_clustered_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/sqlserverstorageengine/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/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Index Defrag Best Practices 2000
> http://www.sql-server-performance.com/dt_dbcc_showcontig.asp Understanding
> DBCC SHOWCONTIG
> http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
> Fill Factors
> http://www.sql-server-performance.com/gv_clustered_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 information
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 swap
> 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 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...
>
>
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.
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 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:
> 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:
>
> 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 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...
>
>
|||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/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
Fill Factors
http://www.sql-server-performance.com/gv_clustered_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/sqlserverstorageengine/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/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Index Defrag Best Practices 2000
> http://www.sql-server-performance.com/dt_dbcc_showcontig.asp Understanding
> DBCC SHOWCONTIG
> http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
> Fill Factors
> http://www.sql-server-performance.com/gv_clustered_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 information
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 swap
> 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 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...
>
>
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 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.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 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:
> 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.
>
>|||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...
> 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:
>> 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.
>>|||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/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
Fill Factors
http://www.sql-server-performance.com/gv_clustered_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...
> 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:
>> 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.
>>|||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/sqlserverstorageengine/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/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Index Defrag Best Practices 2000
> http://www.sql-server-performance.com/dt_dbcc_showcontig.asp Understanding
> DBCC SHOWCONTIG
> http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
> Fill Factors
> http://www.sql-server-performance.com/gv_clustered_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...
>> 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:
>> 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.
>>
>|||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 information
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 swap
> 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 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...
> > 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:
> >
> >> 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.
> >>
> >>
> >>
>
>
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.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 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:
> 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.
>
>|||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...
> 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:
>> 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.
>>|||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/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
Fill Factors
http://www.sql-server-performance.com/gv_clustered_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...
> 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:
>> 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.
>>|||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/sqlserverstorageengine/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/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Index Defrag Best Practices 2000
> http://www.sql-server-performance.com/dt_dbcc_showcontig.asp Understanding
> DBCC SHOWCONTIG
> http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
> Fill Factors
> http://www.sql-server-performance.com/gv_clustered_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...
>> 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:
>> 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.
>>
>|||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 information
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 swap
> 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 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...
> > 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:
> >
> >> 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.
> >>
> >>
> >>
>
>
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...
>
>
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...
>
>
DBCC DBReindex and Update Statistics
Does doing a DBReindex on each table also update all the
statistics on that table as well? I think there can be
index specific statistics as well as non index related
statistics on tables. Do all of these get updated with
DBReindex?
Thanks,
Van
Hi,
Yes, dbcc dbreindex rebuilds the index and updates the statistics.
BTW, what do you mean by "I think there can be index specific statistics as
well as non index related statistics on tables." ? FYI, statistics are
created only on index.
Thanks
Yogish
|||As far as I know all stats get updated after a dbcc dbreindex of the
clustered index. You can verify this yourself, by using the DBCC
SHOW_STATISTICS command.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:077401c52a66$9fecbc50$a501280a@.phx.gbl...
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van
|||With autocreate stats option, SQL Server creates statistics on non-indexed
columns also.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:5F149C27-5E46-4CE9-8C4F-C65F84E26988@.microsoft.com...
> Hi,
> Yes, dbcc dbreindex rebuilds the index and updates the statistics.
> BTW, what do you mean by "I think there can be index specific statistics
as
> well as non index related statistics on tables." ? FYI, statistics are
> created only on index.
> --
> Thanks
> Yogish
|||If you specify the index in the DBCC DBREINDEX command, then only this
index (and statistics of that index) are updated. If you specify DBCC
DBREINDEX without an index name, then all indexes and all non-index
statistics of the table are updated.
HTH,
Gert-Jan
Van Jones wrote:
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van
statistics on that table as well? I think there can be
index specific statistics as well as non index related
statistics on tables. Do all of these get updated with
DBReindex?
Thanks,
Van
Hi,
Yes, dbcc dbreindex rebuilds the index and updates the statistics.
BTW, what do you mean by "I think there can be index specific statistics as
well as non index related statistics on tables." ? FYI, statistics are
created only on index.
Thanks
Yogish
|||As far as I know all stats get updated after a dbcc dbreindex of the
clustered index. You can verify this yourself, by using the DBCC
SHOW_STATISTICS command.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:077401c52a66$9fecbc50$a501280a@.phx.gbl...
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van
|||With autocreate stats option, SQL Server creates statistics on non-indexed
columns also.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:5F149C27-5E46-4CE9-8C4F-C65F84E26988@.microsoft.com...
> Hi,
> Yes, dbcc dbreindex rebuilds the index and updates the statistics.
> BTW, what do you mean by "I think there can be index specific statistics
as
> well as non index related statistics on tables." ? FYI, statistics are
> created only on index.
> --
> Thanks
> Yogish
|||If you specify the index in the DBCC DBREINDEX command, then only this
index (and statistics of that index) are updated. If you specify DBCC
DBREINDEX without an index name, then all indexes and all non-index
statistics of the table are updated.
HTH,
Gert-Jan
Van Jones wrote:
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van
DBCC DBReindex and Update Statistics
Does doing a DBReindex on each table also update all the
statistics on that table as well? I think there can be
index specific statistics as well as non index related
statistics on tables. Do all of these get updated with
DBReindex?
Thanks,
VanHi,
Yes, dbcc dbreindex rebuilds the index and updates the statistics.
BTW, what do you mean by "I think there can be index specific statistics as
well as non index related statistics on tables." ? FYI, statistics are
created only on index.
Thanks
Yogish|||As far as I know all stats get updated after a dbcc dbreindex of the
clustered index. You can verify this yourself, by using the DBCC
SHOW_STATISTICS command.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:077401c52a66$9fecbc50$a501280a@.phx.gbl...
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van|||With autocreate stats option, SQL Server creates statistics on non-indexed
columns also.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:5F149C27-5E46-4CE9-8C4F-C65F84E26988@.microsoft.com...
> Hi,
> Yes, dbcc dbreindex rebuilds the index and updates the statistics.
> BTW, what do you mean by "I think there can be index specific statistics
as
> well as non index related statistics on tables." ? FYI, statistics are
> created only on index.
> --
> Thanks
> Yogish|||If you specify the index in the DBCC DBREINDEX command, then only this
index (and statistics of that index) are updated. If you specify DBCC
DBREINDEX without an index name, then all indexes and all non-index
statistics of the table are updated.
HTH,
Gert-Jan
Van Jones wrote:
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van
statistics on that table as well? I think there can be
index specific statistics as well as non index related
statistics on tables. Do all of these get updated with
DBReindex?
Thanks,
VanHi,
Yes, dbcc dbreindex rebuilds the index and updates the statistics.
BTW, what do you mean by "I think there can be index specific statistics as
well as non index related statistics on tables." ? FYI, statistics are
created only on index.
Thanks
Yogish|||As far as I know all stats get updated after a dbcc dbreindex of the
clustered index. You can verify this yourself, by using the DBCC
SHOW_STATISTICS command.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:077401c52a66$9fecbc50$a501280a@.phx.gbl...
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van|||With autocreate stats option, SQL Server creates statistics on non-indexed
columns also.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:5F149C27-5E46-4CE9-8C4F-C65F84E26988@.microsoft.com...
> Hi,
> Yes, dbcc dbreindex rebuilds the index and updates the statistics.
> BTW, what do you mean by "I think there can be index specific statistics
as
> well as non index related statistics on tables." ? FYI, statistics are
> created only on index.
> --
> Thanks
> Yogish|||If you specify the index in the DBCC DBREINDEX command, then only this
index (and statistics of that index) are updated. If you specify DBCC
DBREINDEX without an index name, then all indexes and all non-index
statistics of the table are updated.
HTH,
Gert-Jan
Van Jones wrote:
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van
DBCC DBReindex and Update Statistics
Does doing a DBReindex on each table also update all the
statistics on that table as well? I think there can be
index specific statistics as well as non index related
statistics on tables. Do all of these get updated with
DBReindex?
Thanks,
VanHi,
Yes, dbcc dbreindex rebuilds the index and updates the statistics.
BTW, what do you mean by "I think there can be index specific statistics as
well as non index related statistics on tables." ? FYI, statistics are
created only on index.
--
Thanks
Yogish|||As far as I know all stats get updated after a dbcc dbreindex of the
clustered index. You can verify this yourself, by using the DBCC
SHOW_STATISTICS command.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:077401c52a66$9fecbc50$a501280a@.phx.gbl...
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van|||With autocreate stats option, SQL Server creates statistics on non-indexed
columns also.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:5F149C27-5E46-4CE9-8C4F-C65F84E26988@.microsoft.com...
> Hi,
> Yes, dbcc dbreindex rebuilds the index and updates the statistics.
> BTW, what do you mean by "I think there can be index specific statistics
as
> well as non index related statistics on tables." ? FYI, statistics are
> created only on index.
> --
> Thanks
> Yogish|||Thanks for the replies. I think they all get updated
too. Basically I have a weekly maintenance job that
rebuilds all indexes with DBReindex and I was wondering if
I needed to set up a separate Update Statistics step in it
so that Statistics are updated weekly too. Sounds like
DBReindex will take care of it.
Thanks again,
Van
>--Original Message--
>Does doing a DBReindex on each table also update all the
>statistics on that table as well? I think there can be
>index specific statistics as well as non index related
>statistics on tables. Do all of these get updated with
>DBReindex?
>Thanks,
>Van
>.
>|||On some tables, on which query performance is very important to me, I
schedule UPDATE STATISTICS everyday, with a reindex every week. I do this,
even when auto stats are on.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:086101c52a6f$34788fe0$a401280a@.phx.gbl...
> Thanks for the replies. I think they all get updated
> too. Basically I have a weekly maintenance job that
> rebuilds all indexes with DBReindex and I was wondering if
> I needed to set up a separate Update Statistics step in it
> so that Statistics are updated weekly too. Sounds like
> DBReindex will take care of it.
> Thanks again,
> Van
>
> >--Original Message--
> >Does doing a DBReindex on each table also update all the
> >statistics on that table as well? I think there can be
> >index specific statistics as well as non index related
> >statistics on tables. Do all of these get updated with
> >DBReindex?
> >
> >Thanks,
> >
> >Van
> >.
> >|||If you specify the index in the DBCC DBREINDEX command, then only this
index (and statistics of that index) are updated. If you specify DBCC
DBREINDEX without an index name, then all indexes and all non-index
statistics of the table are updated.
HTH,
Gert-Jan
Van Jones wrote:
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van
statistics on that table as well? I think there can be
index specific statistics as well as non index related
statistics on tables. Do all of these get updated with
DBReindex?
Thanks,
VanHi,
Yes, dbcc dbreindex rebuilds the index and updates the statistics.
BTW, what do you mean by "I think there can be index specific statistics as
well as non index related statistics on tables." ? FYI, statistics are
created only on index.
--
Thanks
Yogish|||As far as I know all stats get updated after a dbcc dbreindex of the
clustered index. You can verify this yourself, by using the DBCC
SHOW_STATISTICS command.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:077401c52a66$9fecbc50$a501280a@.phx.gbl...
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van|||With autocreate stats option, SQL Server creates statistics on non-indexed
columns also.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:5F149C27-5E46-4CE9-8C4F-C65F84E26988@.microsoft.com...
> Hi,
> Yes, dbcc dbreindex rebuilds the index and updates the statistics.
> BTW, what do you mean by "I think there can be index specific statistics
as
> well as non index related statistics on tables." ? FYI, statistics are
> created only on index.
> --
> Thanks
> Yogish|||Thanks for the replies. I think they all get updated
too. Basically I have a weekly maintenance job that
rebuilds all indexes with DBReindex and I was wondering if
I needed to set up a separate Update Statistics step in it
so that Statistics are updated weekly too. Sounds like
DBReindex will take care of it.
Thanks again,
Van
>--Original Message--
>Does doing a DBReindex on each table also update all the
>statistics on that table as well? I think there can be
>index specific statistics as well as non index related
>statistics on tables. Do all of these get updated with
>DBReindex?
>Thanks,
>Van
>.
>|||On some tables, on which query performance is very important to me, I
schedule UPDATE STATISTICS everyday, with a reindex every week. I do this,
even when auto stats are on.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:086101c52a6f$34788fe0$a401280a@.phx.gbl...
> Thanks for the replies. I think they all get updated
> too. Basically I have a weekly maintenance job that
> rebuilds all indexes with DBReindex and I was wondering if
> I needed to set up a separate Update Statistics step in it
> so that Statistics are updated weekly too. Sounds like
> DBReindex will take care of it.
> Thanks again,
> Van
>
> >--Original Message--
> >Does doing a DBReindex on each table also update all the
> >statistics on that table as well? I think there can be
> >index specific statistics as well as non index related
> >statistics on tables. Do all of these get updated with
> >DBReindex?
> >
> >Thanks,
> >
> >Van
> >.
> >|||If you specify the index in the DBCC DBREINDEX command, then only this
index (and statistics of that index) are updated. If you specify DBCC
DBREINDEX without an index name, then all indexes and all non-index
statistics of the table are updated.
HTH,
Gert-Jan
Van Jones wrote:
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van
Saturday, February 25, 2012
DBCC DBREINDEX
After we run a dbcc dbreindex for a specific table, we have noticed a huge
drop in, Logical Scan Fragmentation and definite performance improvements.
However, Extent Scan Fragmentation still seems to remain quite high, in most
cases over 50%.
Is there any reason for this?
Thx
This is expected when the index spans multiple files. Also, the number is
not relevant for heaps (tables with no clustered index).
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx
|||Hi,
Have a look into this article:-
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx
drop in, Logical Scan Fragmentation and definite performance improvements.
However, Extent Scan Fragmentation still seems to remain quite high, in most
cases over 50%.
Is there any reason for this?
Thx
This is expected when the index spans multiple files. Also, the number is
not relevant for heaps (tables with no clustered index).
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx
|||Hi,
Have a look into this article:-
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx
DBCC DBREINDEX
After we run a dbcc dbreindex for a specific table, we have noticed a huge
drop in, Logical Scan Fragmentation and definite performance improvements.
However, Extent Scan Fragmentation still seems to remain quite high, in most
cases over 50%.
Is there any reason for this?
ThxThis is expected when the index spans multiple files. Also, the number is
not relevant for heaps (tables with no clustered index).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx|||Hi,
Have a look into this article:-
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx
drop in, Logical Scan Fragmentation and definite performance improvements.
However, Extent Scan Fragmentation still seems to remain quite high, in most
cases over 50%.
Is there any reason for this?
ThxThis is expected when the index spans multiple files. Also, the number is
not relevant for heaps (tables with no clustered index).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx|||Hi,
Have a look into this article:-
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx
Friday, February 24, 2012
DBCC DBREINDEX
After we run a dbcc dbreindex for a specific table, we have noticed a huge
drop in, Logical Scan Fragmentation and definite performance improvements.
However, Extent Scan Fragmentation still seems to remain quite high, in most
cases over 50%.
Is there any reason for this?
ThxThis is expected when the index spans multiple files. Also, the number is
not relevant for heaps (tables with no clustered index).
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx|||Hi,
Have a look into this article:-
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx
drop in, Logical Scan Fragmentation and definite performance improvements.
However, Extent Scan Fragmentation still seems to remain quite high, in most
cases over 50%.
Is there any reason for this?
ThxThis is expected when the index spans multiple files. Also, the number is
not relevant for heaps (tables with no clustered index).
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx|||Hi,
Have a look into this article:-
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx
Subscribe to:
Posts (Atom)