SQL Server Performance

Extent Scan Fragmentation Grows After DBReindex - Why?

Discussion in 'Performance Tuning for DBAs' started by DBADave, Dec 28, 2007.

  1. DBADave New Member

    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. Luis Martin Moderator

    I would like to know if it happens if you run before:
    DBCC UPDATEUSAGE ('database')
  3. DBADave New Member

    It didn't make a difference. Sometimes Extent Scan Fragmentation will have a lower value after DBReindex and other times it would have a higher value.
    Thanks, Dave
  4. satya Moderator

    In the beginning days of DBA I was worried for that too, eventually I found that this is not a problem at all as the SHOWCONTIG algorithm on this value doesn't apply to indexes that are spread over multiple files.
    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx to keepup the practices on defragmentation and if you have followed this that should do the performance without further worries.
    http://www.sql-server-performance.com/articles/dba/dt_dbcc_showcontig_p1.aspx fyi and http://sqlserver-qa.net/blogs/perftune/archive/2007/08/24/1719.aspx that I have blogged earlier.
  5. DBADave New Member

    Hi Satya,
    In my case the indexes do not span multiple files so I am still confused as to why the Extent Scan Fragmentation increases. I'll remove Extent Scan Fragmentation from my automated defragmentation script, but this still remains a mystery to me.
    I am also a little confused by the comment in the sql-server-performance link, which states "The Logical Scan Fragmentation and Extent Scan Fragmentation are indications of how well the indexes are stored within the system when a clustered index is present (and should be ignored for tables that do not have a clustered index)." I've read in a number of places logical fragmentation can also occur for non-clustered indexes. Since logical fragmentation shows the percentage of our-of-order pages in the leaf pages of an index and non-clustered indexes contain leaf pages I'm inclined to believe logical fragmentation applies to non-clustered indexes as well as clustered indexes. Wouldn't you agree?
    Thanks, Dave
    P.S. Happy New Year. Hope all is well.
  6. frozenskys New Member

    Extent Scan Fragmentation grows because the physical pages are just moved around in the free space in the data file. If you GROW the database by an amount greater then the index size first and then try DBCC DBREINDEX again, you should see the Extent Fragmentation decrease to zero.HTH.Richard.
  7. satya Moderator

  8. moh_hassan20 New Member

    ... also extent fragmentaiton number is not relevant to heap tables (without clustered index).
    it is best to run DBCC SHOWCONTIG for tables more than 1000 Page to get accurate results

Share This Page