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:

• Drop and re-create a clustered index.

  Re-creating a clustered index reorganizes the data, and results in full data pages. The level of fullness can be configured using the FILLFACTOR option. The drawbacks of this method are that the index is offline during the drop/re-create cycle and that the operation is atomic. If the index creation is interrupted, the index is not re-created.

• Use DBCC INDEXDEFRAG to reorder the leaf level pages of the index in a logical order.

  The DBCC INDEXDEFRAG command is an online operation, so the index is available while the command is running. The operation is also interruptible without loss of completed work. The drawback of this method is that it does not do as good a job of reorganizing the data as a clustered index drop/re-create operation.

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:

• Comparing the values of Extent Switches and Extents Scanned.

  Note: This method of determining fragmentation does not work if the index spans multiple files. The value of Extent Switches should be as close as possible to that of Extents Scanned. This ratio is calculated as the Scan Density value, which should be as high as possible. This can be improved by either method of reducing fragmentation discussed earlier.

• Understanding Logical Scan Fragmentation and Extent Scan Fragmentation values.

  Logical Scan Fragmentation and, to a lesser extent, Extent Scan Fragmentation values give the best indication of a table's fragmentation level. Both these values should be as close to zero as possible (although a value from 0% through 10% may be acceptable). It should be noted that the Extent Scan Fragmentation value will be high if the index spans multiple files. Both methods of reducing fragmentation can be used to reduce these values.

Result Sets

This table describes the information in the result set.

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

• Pages Scanned
  
  
• Extent Switches
  
  
• Scan Density [Best Count:Actual Count]
  
  
• Logical Scan Fragmentation

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

• ExtentSwitches
  
  
• AverageFreeBytes
  
  
• AveragePageDensity
  
  
• ScanDensity
  
  
• BestCount
  
  
• ActualCount
  
  
• LogicalFragmentation    
  
  
• ExtentFragmentation

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

• ObjectName
  
  
• ObjectId
  
  
• IndexName
  
  
• IndexId
  
  
• Pages
  
  
• ExtentSwitchs
  
  
• ScanDensity
  
  
• BestCount
  
  
• ActualCount
  
  
• LogicalFragmentation

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