Transact-SQL Reference

DBCC SHOWCONTIG

Displays fragmentation information for the data and indexes of the specified table.

Syntax

DBCC SHOWCONTIG
    [    ( { table_name | table_id | view_name | view_id }
            [ , index_name | index_id ]
        )
    
]
    [ WITH { ALL_INDEXES
                | FAST [ , ALL_INDEXES ]
                | TABLERESULTS [ , { ALL_INDEXES } ]
                [ , { FAST | ALL_LEVELS } ]
            }
    ]

Arguments

table_name | table_id | view_name | view_id

Is the table or view for which to check fragmentation information. If not specified, all tables and indexed views in the current database are checked. To obtain the table or view ID, use the OBJECT_ID function.

index_name | index_id

Is the index for which to check fragmentation information. If not specified, the statement processes the base index for the specified table or view. To obtain the index ID, use sysindexes.

WITH

Specifies options for the type of information returned by the DBCC statement.

FAST

Specifies whether to perform a fast scan of the index and output minimal information. A fast scan does not read the leaf or data level pages of the index.

TABLERESULTS

Displays results as a rowset, with additional information.

ALL_INDEXES

Displays results for all the indexes for the specified tables and views, even if a particular index is specified.

ALL_LEVELS

Can only be used with the TABLERESULTS option. Cannot be used with the FAST option. Specifies whether to produce output for each level of each index processed. If not specified, only the index leaf level or table data level will be processed.

Remarks

The DBCC SHOWCONTIG statement traverses the page chain at the leaf level of the specified index when index_id is specified. If only table_id is specified, or if index_id is 0, the data pages of the specified table are scanned.

DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) made against the table. Because these modifications are not usually distributed equally among the rows of the table, the fullness of each page can vary over time. For queries that scan part or all of a table, such table fragmentation can cause additional page reads, which hinders parallel scanning of data.

When an index is heavily fragmented, there are two choices for reducing fragmentation:

The Avg. Bytes free per page and Avg. Page density (full) statistic in the result set give an indication of the fullness of index pages. The Avg. Bytes free per page figure should be low and the Avg. Page density (full) figure should be high. Dropping and recreating a clustered index, with the FILLFACTOR option specified, can improve these statistics. Also, the DBCC INDEXDEFRAG command will compact an index, taking into account its FILLFACTOR, which will improve these statistics.

The fragmentation level of an index can be determined in two ways:

Result Sets

This table describes the information in the result set.

Statistic Description
Pages Scanned Number of pages in the table or index.
Extents Scanned Number of extents in the table or index.
Extent Switches Number of times the DBCC statement moved from one extent to another while it traversed the pages of the table or index.
Avg. Pages per Extent Number of pages per extent in the page chain.
Scan Density
[Best Count: Actual Count]
Best count is the ideal number of extent changes if everything is contiguously linked. Actual count is the actual number of extent changes. The number in scan density is 100 if everything is contiguous; if it is less than 100, some fragmentation exists. Scan density is a percentage.
Logical Scan Fragmentation Percentage of out-of-order pages returned from scanning the leaf pages of an index. This number is not relevant to heaps and text indexes. An out of order page is one for which the next page indicated in an IAM is a different page than the page pointed to by the next page pointer in the leaf page.
Extent Scan Fragmentation Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent containing the current page for an index is not physically the next extent after the extent containing the previous page for an index.
Avg. Bytes free per page Average number of free bytes on the pages scanned. The higher the number, the less full the pages are. Lower numbers are better. This number is also affected by row size; a large row size can result in a higher number.
Avg. Page density (full) Average page density (as a percentage). This value takes into account row size, so it is a more accurate indication of how full your pages are. The higher the percentage, the better.

When a table ID and the FAST option are specified, DBCC SHOWCONTIG returns a result set with only the following columns:

When TABLERESULTS is specified, DBCC SHOWCONTIG returns these eight columns, described in the first table, and the following additional columns.

Statistic Description
ObjectName Name of the table or view processed.
ObjectId ID of the object name.
IndexName Name of the index processed. IndexName is NULL for a heap.
IndexId ID of the index. IndexId is 0 for a heap.
Level Level of the index. Level 0 is the leaf (or data) level of the index. The level number increases moving up the tree toward the index root. Level is 0 for a heap.
Pages Number of pages comprising that level of the index or entire heap.
Rows Number of data or index records at that level of the index. For a heap, this is the number of data records in the entire heap.
MinimumRecordSize Minimum record size in that level of the index or entire heap.
MaximumRecordSize Maximum record size in that level of the index or entire heap.
AverageRecordSize Average record size in that level of the index or entire heap.
ForwardedRecords Number of forwarded records in that level of the index or entire heap.
Extents Number of extents in that level of the index or entire heap.

DBCC SHOWCONTIG returns the following columns when TABLERESULTS and FAST are specified.

Permissions

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

Examples
A. Display fragmentation information for a table

This example displays fragmentation information for the table with the specified table name.

USE Northwind
GO
DBCC SHOWCONTIG (Employees)
GO
B. Use OBJECT_ID to obtain the table ID and sysindexes to obtain the index ID

This example uses OBJECT_ID and sysindexes to obtain the table ID and index ID for the aunmind index of the authors table.

USE pubs
GO
DECLARE @id int, @indid int
SET @id = OBJECT_ID('authors')
SELECT @indid = indid 
FROM sysindexes
WHERE id = @id 
   AND name = 'aunmind'
DBCC SHOWCONTIG (@id, @indid)
GO
C. Display an abbreviated result set for a table

This example returns an abbreviated result set for the authors table in the pubs database.

USE pubs
DBCC SHOWCONTIG ('authors', 1) WITH FAST
D. Display the full result set for every index on every table in a database

This example returns a full table result set for every index on every table in the pubs database.

USE pubs
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
E. Use DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database

This example shows a simple way to defragment all indexes in a database that is 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 (128)
DECLARE @execstr   VARCHAR (255)
DECLARE @objectid  INT
DECLARE @indexid   INT
DECLARE @frag      DECIMAL
DECLARE @maxfrag   DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0

-- Declare cursor
DECLARE tables CURSOR FOR
   SELECT 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 cursor for 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

See Also

CREATE INDEX

DBCC

DBCC DBREINDEX

DBCC INDEXDEFRAG

DROP INDEX

OBJECT_ID

Space Allocation and Reuse

sysindexes

Table and Index Architecture