Wednesday, March 7, 2012

DBCC DBREINDEX vs DBCC INDEXDEFRAG

I am a little confused of which on does what by this Microsoft statement:
Unlike DBCC DBREINDEX or any general index build, DBCC INDEXDEFRAG is an
online operation, so it does not hold long-term locks that can block running
queries or updates. Depending on the amount of fragmentation, DBCC
INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX because a
relatively unfragmented index can be defragmented much faster than a new
index can be built. Another advantage is that with DBCC INDEXDEFRAG, the
index is always available, unlike DBREINDEX. A large amount of fragmentation
can cause DBCC INDEXDEFRAG to run considerably longer than DBCC DBREINDEX,
which may or may not outweigh the benefit of the command's online
capabilities.
Which one is safe to use when users are logged in?
Thx,
DaveIn addition to Russel's excellent post, have a look at
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dave" <captan@.hotmail.com> wrote in message
news:%23GFGvXvSDHA.1920@.TK2MSFTNGP11.phx.gbl...
I am a little confused of which on does what by this Microsoft statement:
Unlike DBCC DBREINDEX or any general index build, DBCC INDEXDEFRAG is an
online operation, so it does not hold long-term locks that can block running
queries or updates. Depending on the amount of fragmentation, DBCC
INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX because a
relatively unfragmented index can be defragmented much faster than a new
index can be built. Another advantage is that with DBCC INDEXDEFRAG, the
index is always available, unlike DBREINDEX. A large amount of fragmentation
can cause DBCC INDEXDEFRAG to run considerably longer than DBCC DBREINDEX,
which may or may not outweigh the benefit of the command's online
capabilities.
Which one is safe to use when users are logged in?
Thx,
Dave

No comments:

Post a Comment