DBCC INDEXDEFRAG (Transact-SQL)
Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Defragments indexes of the specified table or view.
This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ALTER INDEX instead.
Applies to: SQL Server (SQL Server 2008 through current version).
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.
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 Specify Fill Factor for an Index.
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, so a large log is unnecessary 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 the following indexes:
A disabled index.
An index with page locking set to OFF.
A spatial index.
DBCC INDEXDEFRAG is not supported for use on system tables.
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.
A. Using DBCC INDEXDEFRAG to defragment an index
The following example defragments all partitions of the PK_Product_ProductID index in the Production.Product table in the AdventureWorks database.
B. Using DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database
The following example shows a simple way to defragment all indexes in a database that are fragmented above a declared threshold.
/*Perform a 'USE <database name>' to select the database in which to run the script.*/ -- Declare variables SET NOCOUNT ON; DECLARE @tablename varchar(255); DECLARE @execstr varchar(400); DECLARE @objectid int; DECLARE @indexid int; DECLARE @frag decimal; DECLARE @maxfrag decimal; -- Decide on the maximum fragmentation to allow for. SELECT @maxfrag = 30.0; -- Declare a cursor. DECLARE tables CURSOR FOR SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'; -- Create the table. CREATE TABLE #fraglist ( ObjectName char(255), ObjectId int, IndexName char(255), IndexId int, Lvl int, CountPages int, CountRows int, MinRecSize int, MaxRecSize int, AvgRecSize int, ForRecCount int, Extents int, ExtentSwitches int, AvgFreeBytes int, AvgPageDensity int, ScanDensity decimal, BestCount int, ActualCount int, LogicalFrag decimal, ExtentFrag decimal); -- Open the cursor. OPEN tables; -- Loop through all the tables in the database. FETCH NEXT FROM tables INTO @tablename; WHILE @@FETCH_STATUS = 0 BEGIN -- Do the showcontig of all indexes of the table INSERT INTO #fraglist EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'); FETCH NEXT FROM tables INTO @tablename; END; -- Close and deallocate the cursor. CLOSE tables; DEALLOCATE tables; -- Declare the cursor for the list of indexes to be defragged. DECLARE indexes CURSOR FOR SELECT ObjectName, ObjectId, IndexId, LogicalFrag FROM #fraglist WHERE LogicalFrag >= @maxfrag AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0; -- Open the cursor. OPEN indexes; -- Loop through the indexes. FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag; WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ', ' + RTRIM(@indexid) + ') - fragmentation currently ' + RTRIM(CONVERT(varchar(15),@frag)) + '%'; SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ', ' + RTRIM(@indexid) + ')'; EXEC (@execstr); FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag; END; -- Close and deallocate the cursor. CLOSE indexes; DEALLOCATE indexes; -- Delete the temporary table. DROP TABLE #fraglist; GO