avg_fragmentation_in_percent 50% issue

[DLu]

Hi:

I am using avg_fragmentation_in_percent from the dm_db_index_physical_stats to determine either index reorgnize or rebuild.

Issue: one table's PK clustering (2 fields composite) is 50% fragment. but alter table .... rebuild does not work.

SQL2005 64 bit, sp2.

Also try to drop and recreate, but with 10 tables FKs there.

And idea why the alter index .... rebuild did not work?
Thanks
David

[rmiao]

How big the table is? Sql put small tables (less than 8 pages) on shared entents, you can't defrag them.

[DLu]

sp_spaceused ABC

rows reserved data index_size unused
539 48 KB 16 KB 32 KB 0 KB

ALTER INDEX PK_ABC ON ABC REORGANIZE --0:00 still 50.2 %
ALTER INDEX PK_ABC ON ABC REBUILD --0:00 still 50.2 %

Is there anyway to resolve it except drop the constraint and recreate it (it is in production and with 12 tables FK related to this PK.....)

PK_ABC PRIMARY KEY (clustered) clustered, unique, primary key located on PRIMARY (columnA, columnB)
both coulmnA and B are int, not null

Thanks

[rmiao]

Only way is make the table bigger so will not be in shared extents.

[DLu]

Hi Ramio:

Isn't the data 16K bigger than 8K?
Or the only way like your mentioned is to add some dummy records to, ex. 100K, reorg or rebuild, then delete them?

I have around 30% of indexes with %fragment reorg/rebuild not working, I will check their data size to see if there is that pattern.

Thanks for your tip
-D

[rmiao]

Extent is 64k (8 x 8k pages).