DBCC SHOWCONTIG - Key values indicating fragmentation.

This blog shows you which key values to look for in the results of DBCC SHOWCONTIG.

DBCC SHOWCONTIG :: Viewing fragmentation data.

Use DBCC SHOWCONTIG to view how much fragmentation an index has.

Use the TABLERESULTS options to get extra columns in the output that describe statistics about the index.


When looking at the results of DBCC SHOWCONTIG you should concentrate initially on the following metrics:

Scan Density: This percentage should be as close to 100% as possible.

Shows the ratio between the Best Count of extents that should be necessary to read when scanning all the pages of the index, and the Actual Count of extents that was read. In an ideal environment, the density displayed would be close to 100% Defining an acceptable level is difficult, but anything under 75% definitely indicates external fragmentation.


Logical Scan Fragmentation:  Should be as close to 0% as possible

Shows the ratio of pages that are out of logical order. The value should be as close to 0% as possible and anything over 10% indicates external fragmentation. Logical Scan Fragmentation should be ignored for tables that do not have a clustered index

Avg. Page Density (full):

Shows how full the pages are on average. A high percentage means the pages are almost full, and a low percentage indicates a lot of free space. Avg. Page Density should be analysed alongside the fill factor setting specified to help you decide whether or not the index is internally fragmented.


n.b.: The value for Extent Scan Fragmentation is not really important. This is because the algorithm that calculates it does not work for indexes that span over multiple files.

This blog should give you a quick start. The following links will provide you with much more detail and understanding of the metrics, fragmentation and indexes:

Useful Links:

http://www.sqlservercentral.com/articles/Performance+Tuning/whocaresaboutfillfactor/1196/
http://www.sqlteam.com/article/sql-server-indexes-the-basics
http://www.sql-server-performance.com/2002/dt-dbcc-showcontig/