ShowContig - Internal vs External Fragmentation

[wooddsw]

While running DBCC ShowContig on a SQL 2000 server I've discovered some strange outputs.

DBCC SHOWCONTIG scanning 'ContactLog_Audit' table...
Table: 'ContactLog_Audit' (1495676376); index ID: 0, database ID: 96
TABLE level scan performed.
- Pages Scanned................................: 133627
- Extents Scanned..............................: 16714
- Extent Switches..............................: 16713
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.94% [16704:16714]
- Extent Scan Fragmentation ...................: 96.21%
- Avg. Bytes Free per Page.....................: 320.0
- Avg. Page Density (full).....................: 96.05%

DBCC SHOWCONTIG scanning 'Contact' table...
Table: 'Contact' (262344049); index ID: 1, database ID: 96
TABLE level scan performed.
- Pages Scanned................................: 4706
- Extents Scanned..............................: 601
- Extent Switches..............................: 605
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 97.19% [589:606]
- Logical Scan Fragmentation ..................: 0.79%
- Extent Scan Fragmentation ...................: 99.67%
- Avg. Bytes Free per Page.....................: 2360.2
- Avg. Page Density (full).....................: 70.84%

I understanding that Scan Density should be near 100% for optimal searching which in these cases are pretty good. I also understand that Logical and Extent Scan Fragmentation are indications of how well the indexes are stored within the system when a clustered index is present and should typically be near zero. There's not a clustered index on ContactLog_Audit but there is on Contact. You see that Logical Scan Fragmentation is optimal for Contact but in both cases Extent Scan Fragmentation is entirely too high.

Extent Scan Fragmentation is defined as a 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.

I'm having trouble reconciling that several of the indicators reflect optimal usage but Extent Scan Fragmentation is off the charts. Would this be an indicator of high "external" fragmentation? The data drive partition holding these database files is highly fragmented.

Any thoughts are appreciated.

 

[mrdenny]

SQL Server has now idea about the fragmentation on the disk. It only knows if the data pages are in order within the file, but not how those pages are laid out across the physical disk.

What is causing you to look at the fragmentation?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog