Table still showing fragmentation after adding clustered index-SQL2K, Win2K3SE

Question

Table still showing fragmentation after adding clustered index-SQL2K, Win2K3SE

Chrissthom

SSChasing Mays

Points: 656
More actions
October 24, 2005 at 11:22 am

#166018

We have a table in a dev DB that was in a heap and showing 99% extent fragmentation. I know the results from DBCC showcontig do not count with heaps, but a clustered index was added to the table for other performance reasons.
When a DBCC showcontig is run against the table now, here are the results:
DBCC SHOWCONTIG scanning 'TransactionDetail' table...
Table: 'TransactionDetail' (1509684526); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 2381795
- Extents Scanned..............................: 298669
- Extent Switches..............................: 298668
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.68% [297725:298669]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 42.23%
- Avg. Bytes Free per Page.....................: 1489.5
- Avg. Page Density (full).....................: 81.60%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
As you can see the Extent Scan Fragmentation is still showing as 42% though logical is 0%. I am going to run a disk defrag tonight, but looking at the Win frag report, very little of the fragmentation is coming from my Data or Index files. So I don't think this is going to have a big affect.
Anyone have an idea why Extent fargmentation would show as being so fragged after a clustered index was just added?
Thanks,
Chris

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

peterhe SSChampion Points: 11469 More actions · Answer 1

If your table is in a file group with multi files, the Extent Scan Fragmentation does not reflect the real fragmentation. You can ignore this value.

Chrissthom SSChasing Mays Points: 656 More actions · Answer 2

Thanks for the feedback.

Are you saying this includes a situation where the data is all in one file and the indexes are all in another file?

elena kos Ten Centuries Points: 1082 More actions · Answer 3

DBCC SHOWCONTIG scans index for the table. Extent Scan Fragmentation value will be high if the index spans multiple files.

peterhe SSChampion Points: 11469 More actions · Answer 4

"Are you saying this includes a situation where the data is all in one file and the indexes are all in another file?"

It's upto what you are scanning, the table or the index. If you are scanning the table, the factor is accurate. If you are scanning the index, it's not.

As for your case, you are scanning the clustered index (ID=1), the data and index are in the same files.