Tips Centre Wednesday, September 20, 2017

Home Home | About Us About Us | Contact Us Contact Us

vrPakistanis Technologies - Web Development, Logo Designing, Web Designing

Home > Databases

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.

This article has been taken from teratrax.com.

Tips Centre 2008-2012. All rights reserved. Developed by vrPakistanis Technologies.