Presents your 
SQL SERVER E-NEWSLETTER for October 29, 2002

<------------------------------------------->

PERFORM MAINTENANCE WITH DBCC INDEXDEFRAG

SQL Server 2000 has introduced the command DBCC INDEXDEFRAG. It is an
online operation; therefore, users can continue to work on a database while
this command is running. This is because the operation does not require
resources to be locked to the extent that they were with SQL Server 7.0's
DBREINDEX command. Due to the smaller amount of locking, it reduces the
blocking contention as well.

The DBCC INDEXDEFRAG operation works on small segments of data, which
allows the operation to be stopped at any time and to track the work it has
completed. The operation will also report back every five minutes with
the estimated percentage completed.

The DBCC INDEXDEFRAG will rearrange the physical leaf level of an index
on currently allocated pages for the index. The physical order of the
index will then match the logical order of the index. This should speed 
up the performance of scanning these indexes.

The operation will arrange the index pages in the same space allocated
to the index. SQL Server will use the fill factor as a target for
buffering space on pages for the index depending on denseness of the 
index  data and the amount of allocated space for the index. Pages that are 
left empty after the operation will be released, making the index more 
compact than it originally was.

However, if there are two indexes on a table that are sharing the same
space of an extent and each index is not contiguous to itself, it may 
be more beneficial to rebuild the indexes, allowing the index pages to be
contiguous.

Also, if fragmentation is heavy on an index, it may cause the DBCC
INDEXDEFRAG to perform slower than the DBREINDEX command. If 
fragmentation is light on an index, then the DBCC INDEXDEGRAG should be 
considerably faster than the DBREINDEX, with the added bonus of the operation 
being online.

The drawbacks for DBCC INDEXDEFRAG that you need to be aware of are the
following:


* The nonleaf index pages are not reordered.

* The operation will not update statistics.

----------------------------------------