none
Index Fragmentation

    Question

  • Hello,

    Quick question, I have just run DBCC SHOWCONTIG against an index I created against the Person.Person table in the AdventureWorks database and the results are below. Can someone let me know how there can be no logical fragmentation but 12.5% (one extent) extent fragmentation?

    Any help or advice would be most appreciated.

    Andrew

    DBCC SHOWCONTIG scanning 'Person' table...

    Table: 'Person' (1765581328); index ID: 5, database ID: 5

    LEAF level scan performed.

    - Pages Scanned................................: 64

    - Extents Scanned..............................: 8

    - Extent Switches..............................: 7

    - Avg. Pages per Extent........................: 8.0

    - Scan Density [Best Count:Actual Count].......: 100.00% [8:8]

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

    - Extent Scan Fragmentation ...................: 12.50%

    - Avg. Bytes Free per Page.....................: 42.4

    - Avg. Page Density (full).....................: 99.48%

    Wednesday, November 20, 2013 4:26 PM

Answers

  • That means that there's a gap between two of your extents.

    In testing with dbcc ind after rebuilding a small index, it looks the non-leaf page is stored after the first 8 extents, and there's a gap after it, perhaps to accommodate any additional non-leaf pages. 

    David


    David http://blogs.msdn.com/b/dbrowne/




    Wednesday, November 20, 2013 5:07 PM

All replies

  • That means that there's a gap between two of your extents.

    In testing with dbcc ind after rebuilding a small index, it looks the non-leaf page is stored after the first 8 extents, and there's a gap after it, perhaps to accommodate any additional non-leaf pages. 

    David


    David http://blogs.msdn.com/b/dbrowne/




    Wednesday, November 20, 2013 5:07 PM
  • Sorry for the late reply David. Thank you for your help.

    Andrew

    Tuesday, November 26, 2013 3:22 PM