DBCC INDEXDEFRAG (Transact-SQL)

Defragments indexes of the specified table or view.

Transact-SQL Syntax Conventions

Syntax

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

Arguments

• database_name | database_id | 0
  Is the database for which to defragment an index. If 0 is specified, the current database is used. Database names must comply with the rules for identifiers.

• table_name | table_id | view_name | view_id
  Is the table or view for which to defragment an index. Table and view names must comply with the rules for identifiers.

• index_name | index_id
  Is the name of ID of the index to defragment. If not specified, the statement defragments all indexes of the specified table or view. Index names must comply with the rules for identifiers.

• partition_number | 0
  Is the partition number of the index to defragment. If not specified or if 0 is specified, the statement defragments all partitions in the specified index.

• WITH NO_INFOMSGS
  Suppresses all informational messages that have severity levels from 0 through 10.

Result Sets

DBCC INDEXDEFRAG returns the following result set (values may vary) if an index is specified in the statement (unless WITH NO_INFOMSGS is specified):

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.

Remarks

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, therefore improving index-scanning performance.

DBCC INDEXDEFRAG also compacts the pages of an index, taking into consideration the fill factor specified when the index was created. Any empty pages created because of this compaction are removed. For more information, see Fill Factor.

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

DBCC INDEXDEFRAG reports the estimated percentage completed every five minutes. DBCC INDEXDEFRAG can be stopped at any point in the process, and any completed work is retained.

Unlike DBCC DBREINDEX, or the index building operation generally, DBCC INDEXDEFRAG is an online operation. It does not hold locks long term. Therefore, DBCC INDEXDEFRAG does not block running queries or updates. Because the time to defragment is related to the level of fragmentation, a relatively unfragmented index can be defragmented faster than a new index can be built. A very fragmented index might take considerably longer to defragment than to rebuild.

The defragmentation is always fully logged, regardless of the database recovery model setting. For more information, see ALTER DATABASE (Transact-SQL). The defragmentation of a very fragmented index can generate more log than a fully logged index creation. However, the defragmentation, is performed as a series of short transactions; therefore, it does not require a large log if log backups are taken frequently or if the recovery model setting is SIMPLE.

DBCC INDEXDEFRAG shuffles index leaf pages in place. Therefore, if an index is interleaved with other indexes on disk, running DBCC INDEXDEFRAG against that index does not make all leaf pages in the index contiguous. To improve the clustering of pages, rebuild the index.

DBCC INDEXDEFRAG cannot be used to defragment a disabled index or an index with page locking set to OFF. DBCC INDEXDEFRAG is not supported for use on system tables.

Permissions

Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

Examples

The following example defragments all partitions of the PK_Product_ProductID index in the Production.Product table in the AdventureWorks database.

DBCC INDEXDEFRAG (AdventureWorks, "Production.Product", PK_Product_ProductID)
GO

See Also

Reference

DBCC (Transact-SQL)
sys.dm_db_index_physical_stats
CREATE TABLE (Transact-SQL)
ALTER TABLE (Transact-SQL)
ALTER INDEX (Transact-SQL)

Other Resources

Table and Index Architecture

Help and Information

Getting SQL Server 2005 Assistance

Change History