DBCC INDEXDEFRAG improves SQL Server index scanning
 Fragmented
SQL Server indexes can result in performance degradation and longer index
scanning time. DBCC INDEXDEFRAG helps improve index scanning performance by
defragmenting clustered and nonclustered indexes.
Syntax
DBCC INDEXDEFRAG (database1, table1,
index1)
Results
DBCC INDEXDEFRAG returns a result set similar to this:
Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
359 346 8
Details
DBCC INDEXDEFRAG also compacts the pages of an index, taking into
account the FILLFACTOR specified when the index was created. Any empty
pages created as a result of this compaction will be removed.
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. 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.
|
