Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    95

    Unanswered: Extent Scan Fragmentation Increases After DBReindex - Why?

    On some tables when I run DBCC ShowContig followed by DBReindex followed by ShowContig I notice Extent Scan Fragmentation actually increases. Why does this happen? Below are the SHOWCONTIG results after running DBReindex three times.

    After First DBReindex

    - Pages Scanned................................: 986
    - Extents Scanned..............................: 124
    - Extent Switches..............................: 123
    - Avg. Pages per Extent........................: 8.0
    - Scan Density [Best Count:Actual Count].......: 100.00% [124:124]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 47.58%
    - Avg. Bytes Free per Page.....................: 91.0
    - Avg. Page Density (full).....................: 98.88%

    After Second DBReindex

    - Pages Scanned................................: 986
    - Extents Scanned..............................: 124
    - Extent Switches..............................: 123
    - Avg. Pages per Extent........................: 8.0
    - Scan Density [Best Count:Actual Count].......: 100.00% [124:124]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 20.16%
    - Avg. Bytes Free per Page.....................: 91.0
    - Avg. Page Density (full).....................: 98.88%

    After Third DBReindex

    - Pages Scanned................................: 986
    - Extents Scanned..............................: 124
    - Extent Switches..............................: 123
    - Avg. Pages per Extent........................: 8.0
    - Scan Density [Best Count:Actual Count].......: 100.00% [124:124]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 67.74%
    - Avg. Bytes Free per Page.....................: 91.0
    - Avg. Page Density (full).....................: 98.88%

    Thanks, Dave

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    From an article posted here (registration required, but it's free).

    Quote Originally Posted by sqlservercentral.com
    Note: 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. According to some Microsoft sources, in the next version
    of SQL Server, DBCC SHOWCONTIG will not even include this metric.
    I have always been taught to focus on the scan density metric. Yours was at 100% before starting defragmentation; I would not have rebuilt that index.

    I have seen instances where defragging the index makes scan density worse, but not rebuilding one. I forget the details, but it has to do when multiple indexes cross paths over the segments (probably a really, grossly simplified statement, but it's my world and I'm the only one who has to live in it).

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    As an interesting point (or not - take it or leave it), Paul Randall's advice is to concentrate on logical fragmentation only.

  4. #4
    Join Date
    Jan 2003
    Posts
    95
    In the past I have always been concerned with Logical Fragmentation, Scan Density and Average Page Density, but last week I read some things on the Internet indicating that Extent Scan Fragmentation should also be looked at when considering fragmentation. I have my doubts this is correct.

    Dave

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Perhaps someone with experience of Oracle can let you know about this but.... isn't extent fragmentation a big deal in Oracle? I'm sure I've seen this mentioned before somewhere.....

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Tracked down Paul Randal's advice:
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54391

    If you check out SQLTeam he's posted a few times on there about fragmentation (another useful one here:
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63194). Personally, I don't really take a great deal of notice of anyone's thoughts on fragmentation if they contradict what he has to say on the subject.

    I would note that he also says to dismiss scan density

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by pootle flump
    I would note that he also says to dismiss scan density
    Meh. It's not the first time that I have had a myth explode out from underneath me. Time to head back to the books (or white papers). Thanks for the link and the info; it's always good to learn.

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •