February 24, 2006 at 8:04 am
Our results of reindexing with a fillfactor of 100% for a read only database are surprising:
Here is before using a fillfactor of 100%, the previous fillfactor was 90% and had some inserts and updates since the last reindex:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 301013
- Extents Scanned..............................: 37859
- Extent Switches..............................: 40777
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 92.27% [37627:40778]
- Logical Scan Fragmentation ..................: 0.74%
- Extent Scan Fragmentation ...................: 6.30%
- Avg. Bytes Free per Page.....................: 784.6
- Avg. Page Density (full).....................: 90.31%
Here are the results after a reindex with fillfactor of 100%:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 299768
- Extents Scanned..............................: 37696
- Extent Switches..............................: 52261
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 71.70% [37471:52262]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 6.03%
- Avg. Bytes Free per Page.....................: 754.3
- Avg. Page Density (full).....................: 90.68%
The results show that logical scan fragmentation has been removed, but extent scan fragmentation has only been slightly lowered. In some other test indexes, the extent scan fragmentation actually increased.
Scan density went down significantly and avg page density barely increased. Once again with other indexes, we are seeing avg page density also decreasing.
Finally, extent switches went up significantly. while the pages scanned decreased.
I would assume that a fillfactor of 100% should condense the index as much as possible, optimizing reads to a read only database. But our testing seem to show different results.
Can anyone shed some light on this for us?
Thank You
February 24, 2006 at 9:23 am
how did you rebuild the indexes?
try updating the statistics for the object and see whether u get the same results again...
-Krishnan
February 24, 2006 at 9:32 am
Ooops, the stored proc I was executing the INDEXDEFRAG logic, not the REINDEX. I fixed the stored proc.
All is better now, but I am still seeing extent scan fragmentation around 3-5% even with the REINDEX.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply