DBCC SHOWCONTIG (T-SQL)

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

Syntax

DBCC SHOWCONTIG
    [
        (    table_id [, index_id]
        )
    ]

Arguments

table_id: Is the table identification number (ID) for which to check fragmentation information. If not specified, all tables in the current database are checked. To obtain the table ID, use the OBJECT_ID function.
index_id: Is the index identification number (ID) for which to check fragmentation information. If not specified, the statement processes all indexes for the specified table. To obtain the index ID, use sysindexes.

Remarks

This 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, this can cause additional page reads.

When a table is heavily fragmented, reduce fragmentation and improve read-ahead (parallel data scan) performance by dropping and re-creating a clustered index (without using the SORTED_DATA option). Re-creating a clustered index reorganizes the data, resulting in full data pages. The level of “fullness” can be configured using the FILLFACTOR option.

Result Sets

DBCC SHOWCONTIG returns this result set when a table ID is specified; the output is for the authors table of the pubs database (values may vary):

DBCC SHOWCONTIG scanning 'authors' table...

[SHOW_CONTIG - SCAN ANALYSIS]

------------------------------------------------------------------------

Table: 'authors' (117575457) Indid: 1 dbid:5

TABLE level scan performed.

- Pages Scanned................................: 2

- Extents Scanned..............................: 2

- Extent Switches..............................: 1

- Avg. Pages per Extent........................: 1.0

- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]

- Logical Scan Fragmentation ..................: 0.00%

- Extent Scan Fragmentation ...................: 50.00%

- Avg. Bytes free per page.....................: 7051.0

- Avg. Page density (full).....................: 12.89%

(11 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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 pages 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.

Permissions

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

Examples

A. Display fragmentation information for a table

This example displays fragmentation information for the table with the specified table ID number.

DBCC SHOWCONTIG (16003088)

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

CREATE INDEX	Space Allocation and Reuse
DBCC DBREINDEX	sysindexes
DROP INDEX	Table and Index Architecture
OBJECT_ID	DBCC