SQL Server Index Fragmentation and Its Resolution

Syntax (SQL Server 7.0)
DBCC SHOWCONTIG
    [    ( table_id [,index_id ]
        )
    ]

Examples:

Query to show fragmentation information on all indexes in a database

--Show fragmentation information on all indexes in a database
--Clean up the display
SET NOCOUNT ON

--Use the pubs database
USE pubs

DBCC SHOWCONTIG WITH ALL_INDEXES
GO

Query to show fragmentation information on all indexes on a table

--Show fragmentation information on all indexes on a table
--Clean up the display
SET NOCOUNT ON

--Use the pubs database
USE pubs

DBCC SHOWCONTIG (authors) WITH ALL_INDEXES
GO

Query to show fragmentation information on a specific index

--Show fragmentation information on a specific index
--Clean up the display
SET NOCOUNT ON

--Use the pubs database
USE pubs

DBCC SHOWCONTIG (authors,aunmind)
GO

Result Set

DBCC SHOWCONTIG will return the number of pages scanned, the number of extents scanned, the number of times the DBCC statement moved from one extent to another while it traversed the pages of the table or index, the average number of pages per extent, the scan density (best count is the ideal number of extent changes if everything is contiguously linked).

DBCC SHOWCONTIG scanning 'authors' table...
Table: 'authors' (1977058079); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count: Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 6002.0
- Avg. Page Density (full).....................: 25.85%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

What to Look For

Pages Scanned: If you know the approximate row size and the number of rows contained in your table or index, you can estimate the number of pages there should be in that index. Look at the number of pages scanned and if it is significantly higher than the number of pages you estimated, you have internal fragmentation.

Extents Scanned: Take the number of pages scanned and divide that number by 8, rounded to the next highest interval. This figure should match the number of extents scanned returned by DBCC SHOWCONTIG. If the number returned by DBCC SHOWCONTIG is higher, then you have some external fragmentation. The seriousness of the fragmentation depends on just how high the shown value is from the estimated value.

Extent Switches: This number should be equal to (Extents Scanned - 1). Higher numbers indicate external fragmentation.

Avg. Pages per Extent: This number is the Pages Scanned / Extents Scanned and should be 8. Numbers lower than 8 indicate external fragmentation.

Scan Density [Best Count: Actual Count]: One of the most useful of the percentages returned by DBCC SHOWCONTIG. This is the ratio between the Best Count of extents and the Actual Count of extents. This percentage should be as near to 100% as possible. Lower percentages indicate external fragmentation.

Logical Scan Fragmentation: Shows the ratio of pages that are out of order. This percentage should be between 0% and 10% with anything higher indicating external fragmentation.

Extent Scan Fragmentation: Shows any gaps between extents. This percentage should be 0% and higher percentages indicate external fragmentation.

Avg. Bytes Free per Page: Shows the average number of free bytes in a page. Higher numbers show internal fragmentation but you should take the fill factor into account before you let the higher numbers determine if you have internal fragmentation or not.

Avg. Page Density (full): Shown as a percentage that is the inverse of Avg. Bytes Free per Page. Lower percentages indicate internal fragmentation.