DBCC DBREINDEX versus INDEXDEFRAG.

(SQL Server 2000, SP3a)
Hello all!
For our production environment, we have a decent window which to run some maintenance
scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX, is that
essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX do everything a
INDEXDEFRAG does (and possibly more)? Or are there situations where it'd be beneficial to
run *both*?
Thanks!
John PetersonAs my understanding goes, it is better to go for DBCC DBREINDEX command, as it takes care of all the table and view indexes, and any constraints on the columns are automatically taken care of. One can use, DBCC INDEXDEFRAG, if the indexes needs to be used on-line, which allows any DML operations.
So if locking of rows during index rebuliding is not a criteria, I guess you can go with DBCC DBREINDEX.
Am not sure, if I was able to answer yur query.
Thanks
GYK
-- John Peterson wrote: --
(SQL Server 2000, SP3a)
Hello all!
For our production environment, we have a decent window which to run some maintenance
scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX, is that
essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX do everything a
INDEXDEFRAG does (and possibly more)? Or are there situations where it'd be beneficial to
run *both*?
Thanks!
John Peterson|||John
BOL says:
Unlike DBCC DBREINDEX (or the index building operation in general), DBCC
INDEXDEFRAG is an online operation. It does not hold locks long term and
thus will not block running queries or updates. A relatively unfragmented
index can be defragmented faster than a new index can be built because the
time to defragment is related to the amount of fragmentation. A very
fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged,
regardless of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
Also, DBCC INDEXDEFRAG will not help if two indexes are interleaved on the
disk because INDEXDEFRAG shuffles the pages in place. To improve the
clustering of pages, rebuild the index.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:#Bgv70zpDHA.1740@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> For our production environment, we have a decent window which to run some
maintenance
> scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX,
is that
> essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX
do everything a
> INDEXDEFRAG does (and possibly more)? Or are there situations where it'd
be beneficial to
> run *both*?
> Thanks!
> John Peterson
>|||Please read "Microsoft SQL Server 2000 Index Defragmentation Best Practices"
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Bgv70zpDHA.1740@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> For our production environment, we have a decent window which to run some
maintenance
> scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX,
is that
> essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX
do everything a
> INDEXDEFRAG does (and possibly more)? Or are there situations where it'd
be beneficial to
> run *both*?
> Thanks!
> John Peterson
>|||Thanks, Gert!
The way I read this article, it sounds as if these two techniques *are* different, and
like many things, it depends on your system.
The article seems to suggest that rebuilding the index (DBCC DBREINDEX) should typically
yield better performance than doing a INDEXDEFRAG, but it showed cases where that might
not always be the case.
Now I'm even more confused than ever. I had hoped that the INDEXDEFRAG was essentially a
subset of the DBREINDEX operation so that my choice was simple. ;-)
"Gert E.R. Drapers" <GertD@.Online.SQLDev.Net> wrote in message
news:%23B95Rl2pDHA.976@.tk2msftngp13.phx.gbl...
> Please read "Microsoft SQL Server 2000 Index Defragmentation Best Practices"
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2003 All rights reserved.
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23Bgv70zpDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > For our production environment, we have a decent window which to run some
> maintenance
> > scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX,
> is that
> > essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX
> do everything a
> > INDEXDEFRAG does (and possibly more)? Or are there situations where it'd
> be beneficial to
> > run *both*?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>