Rebuild some fragmented indexes - problem with Extent Scan Fragmentation value
This was the question from SSP forums, I’m trying to rebuild few fragmented indexes on a table that is updated on regular basis. The “dbcc showcontig” gave this result:
Table: 'Confidential' (999999999); index ID: 5, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 86
- Extents Scanned..............................: 12
- Extent Switches..............................: 12
- Avg. Pages per Extent........................: 7.2
- Scan Density [Best Count:Actual Count].......: 84.62% [11:13]
- Logical Scan Fragmentation ..................: 1.16%
- Extent Scan Fragmentation ...................: 58.33%
- Avg. Bytes Free per Page.....................: 862.4
- Avg. Page Density (full).....................: 89.34%
Then I executed the dbreindex on this index (with fill-factor 90), and then used dbcc showcontig again:
Table: 'Confidential' (999999999); index ID: 5, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 86
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 100.00% [11:11]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 72.73%
- Avg. Bytes Free per Page.....................: 862.4
- Avg. Page Density (full).....................: 89.34%
Why is the Extent Scan Fragmentation increasing?
I believed this value to be close to 0 after executing the dbreindex.
Before I proceed further I would like to refer the UnderstandingDBCCSHOWCONTIG article for a better information to the newbie DBAs.
As referred in BOL this Extent Scan Fragmentation refers the out of order extents when scanning the leaf pages of an index. So out-of-order extent is one for which the extent containing the current page for an index is not physically the next extent after the extent containing the previous page for an index.
Basically the fragmentation level of an index can be determined by comparing the values of "Extent Switches" and "Extents Scanned" and having a clear understanding "Logical Scan Fragmentation" and "Extent Scan Fragmentation" values. Though the other 3 values give good indication of fragementation level, the extent scan fragmentation refers that a 'member' table is highly fragmented and the provider table is slightly fragmented based on the numbers above. In this DBCC INDEXDEFRAG will be helpful to some extent and overall using these statements it will help to diagnose and greatly reduce fragmentation on the 'member' and 'provider' tables. The member table is almost perfect and the 'provider' table shows great improvement. This will result in an extraordinary performance increase on queries that are run against these tables.
FYI, what I suggest is to when reviewing the output from DBCC SHOWCONTIG you should pay special attention to the following metrics:
- Avg. Page Density (full): Shows the average level of how filled the pages are. A percentage means the pages are almost full, and a low indicates much free space. This value should be compared to the fill factor setting specified when the index was created to decide whether or not the index is internally fragmented.
- Scan Density: Shows the ratio between the Best Count of extents that should be necessary to read when scanning all the pages of the index, and the Actual Count of extents that was read. This percentage should be as close to 100% as possible. Defining an acceptable level is difficult, but anything under 75% definitely indicates external fragmentation.
- Logical Scan Fragmentation: Shows the ratio of pages that are out of logical order. The value should be as close to 0% as possible and anything over 10% indicates external fragmentation.
Further I believe Extent Scan Fragmentation values is not that important, as the calculation does not work indexes if they are spanned over multiple files.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.