Fragmentation Analysis

DBCC SHOWCONTIG ("dbo.datagathering");

DBCC SHOWCONTIG scanning 'DataGathering' table...
Table: 'DataGathering' (293576084); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 134963
- Extents Scanned..............................: 17197
- Extent Switches..............................: 17818
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 94.68% [16871:17819]
- Logical Scan Fragmentation ..................: 1.14%
- Extent Scan Fragmentation ...................: 35.13%
- Avg. Bytes Free per Page.....................: 177.7
- Avg. Page Density (full).....................: 97.80%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

For optimal performance, logical fragmentation needs to be below 10% and Extent below 70%. 

 

Analysis of the above SHOWCONTIG example shows:

1. Index ID = 1, so it qualifies to be examined.
2. Pages Scanned is above 1,000 pages making the index fragmentation important.
3. Logical Scan Fragmentation is not only above 10% which causes noticeable latency, but is above 50% which makes the index usage significantly slower.
4. Extent Scan fragmentation is above 70% also causing extensive latency.
5. Average Page Density is way below 90% causing more index reads to navigate through an index.