sql-serverdatabase-fragmentation

High extent scan fragmentation but low Logical Scan Fragmentation

I'm still new to SQL Server, when I run DBCC SHOWCONTIG on table, it shows that Logical Scan Fragmentation is not big but Extent Scan Fragmentation is big, which made me a little confused.

    DBCC SHOWCONTIG scanning 'User' table...
    Table: 'User' (462624691); index ID: 1, database ID: 6
    TABLE level scan performed.
    - Pages Scanned................................: 2638
    - Extents Scanned..............................: 335
    - Extent Switches..............................: 578
    - Avg. Pages per Extent........................: 7.9
    - Scan Density [Best Count:Actual Count].......: 56.99% [330:579]
    - Logical Scan Fragmentation ..................: 12.93%
    - Extent Scan Fragmentation ...................: 94.03%
    - Avg. Bytes Free per Page.....................: 805.1
    - Avg. Page Density (full).....................: 90.05%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Seeing from the Manual on MSDN, one is about fragmentation on pages and another is on extents. Is there other difference? How can Logical Scan Fragmentation is low while Extent Scan Fragmentation remains high?

Also when we talk about "External fragmentation", it is more related with "Logical Scan Fragmentation" in the result or the "Extent Scan Fragmentation" in the result of SHOWCONTIG?

Really appreciate any answer.

Solution

  • I assume you are interested in measuring fragmentation because you want to optimize sequential scanning speed.

    All numbers about extents are meaningless as far as I can tell. What matters for sequential scan speed are page-level metrics because performance is determined by the way pages are read.

    The interesting number here is the "Logical Scan Fragmentation" because that is the percentage of pages where a disk seeks was most likely required to get to the next page. 13% means that about once in every 6 pages the physical read position changes.

    How can Logical Scan Fragmentation is low while Extent Scan Fragmentation remains high?

    If there is 100% extent fragmentation there still can be 8 contiguous pages per extent so the logical fragmentation could be as low as 12.5%.

    Probably you should not use SHOWCONTIG but use the fragmentation DMV.