DBCC INDEXDEFRAG (Transact-SQL)
Defragments indexes of the specified table or view.
Important
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead.
.gif "Topic link icon")
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.
Note
When DBCC INDEXDEFRAG is run, index defragmentation occurs serially. This means that the operation on a single index is performed using a single thread. No parallelism occurs. Also, operations on multiple indexes from the same DBCC INDEXDEFRAG statement are performed on one index at a time.
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
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
14 April 2006 |
|