ReIndex with fillfactor of 100

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
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.
|||A reindex is not guaranteed to remove 100% of the fragmentation, especially
if you did not have LOTS of free contiguous space in your data file before
you reindexed. Do you have more than 1 data file in that filegroup? If so
you should only go by the Logical Fragmentation as some of the rest are
invalid for multiple files.
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140798715.538612.221680@.u72g2000cwu.googlegr oups.com...
> 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.
>