Transact-SQL Reference

DBCC INDEXDEFRAG

Defragments clustered and secondary indexes of the specified table or view.

Syntax

DBCC INDEXDEFRAG
    ( { database_name | database_id | 0 }
        , { table_name | table_id | 'view_name' | view_id }
        , { index_name | index_id }
    )    [ WITH NO_INFOMSGS ]

Arguments

database_name | database_id | 0

Is the database for which to defragment an index. Database names must conform to the rules for identifiers. For more information, see Using Identifiers. If 0 is specified, then the current database is used.

table_name | table_id | 'view_name' | view_id

Is the table or view for which to defragment an index. Table and view names must conform to the rules for identifiers.

index_name | index_id

Is the index to defragment. Index names must conform to the rules for identifiers.

WITH NO_INFOMSGS

Suppresses all informational messages (with severity levels from 0 through 10).

Remarks

DBCC INDEXDEFRAG can defragment clustered and nonclustered indexes on tables and views. DBCC INDEXDEFRAG defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes, thus improving index-scanning performance.

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. For more information about FILLFACTOR, see CREATE INDEX.

If an index spans more than one file, DBCC INDEXDEFRAG defragments one file at a time. Pages do not migrate between files.

Every five minutes, DBCC INDEXDEFRAG will report to the user an estimated percentage completed. DBCC INDEXDEFRAG can be terminated at any point in the process, and any completed work is retained.

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.

DBCC INDEXDEFRAG is not supported for use on system tables.

Result Sets

DBCC INDEXDEFRAG returns this result set unless WITH NO_INFOMSGS is specified (values may vary):

Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
359           346         8

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Permissions

DBCC INDEXDEFRAG permissions default to members of the sysadmin fixed server role, the db_owner and db_ddladmin fixed database role, and the table owner, and are not transferable.

Examples

DBCC INDEXDEFRAG (Northwind, Orders, CustomersOrders)
GO