index defrag problem

As per BOL 2005 , i tried to defrag indexes by both these methods

1.reorganizing an index

2.rebuilding an index

but still some of my tables indexes have the same

avg_fragmentation_in_percent that they were in before i did my

defrag using the above 2 method.

i used sys.dm_db_index_physical_stats instead of DBCC SHOWCONTIG

Any pointers ? thankscan you display the results from the dm_db_index_physical_stats|||

database_id object_id indexe_id partition_number alloc_unit_type_desc index_depth index_level avg_frag_in_perfrg_cnt
10118511CLUSTERED INDEXIN_ROW_DATA2058.333333333333381.512NULLNULLNULLNULLNULLNULLNULLNULL
NOTE : i used the new alter index instead of the DBCC INDEXDEFRAG or DBCC DBREINDEX|||

I am having the same issue but only on certain tables in my database. Even when I create a new index on the problem tables, the index is 60%-80% fragmented. I tried rebuilding, defragging, and dropping and recreating the index and I am getting the same results. Here is one of the index properties...

DBCC SHOWCONTIG scanning 'DimTest' table...

Table: 'DimTest' (59251366); index ID: 4, database ID: 12

LEAF level scan performed.

- Pages Scanned................................: 5

- Extents Scanned..............................: 5

- Extent Switches..............................: 4

- Avg. Pages per Extent........................: 1.0

- Scan Density [Best Count:Actual Count].......: 20.00% [1:5]

- Logical Scan Fragmentation ..................: 80.00%

- Extent Scan Fragmentation ...................: 80.00%

- Avg. Bytes Free per Page.....................: 1608.2

- Avg. Page Density (full).....................: 80.13%|||

Wanted to revisit this, as there hasn't been any resolutions or suggestions. This is happening at our company as well, but the clustered is fragmented even lower. Anyone have any ideas as to why this is happening on 2005?

Thanks...

|||

I am having issues defraging indexes on several of my tables. I noticed in my test environment that one of the indexes (a primary key, clustered index, indexed on an identity column) was 87% fragmented. I rebuild the index and it was still 87% fragmented. Occasionally, after the rebuild it will drop to 82% fragmented. It does the same thing if I try a reorg or use DBCC DBReindex. Any ideas?

|||

I'm having the same problem. I have run DBCC DBREINDEX, DBCC INDEXDEFRAG, dropped the index and recreated it. I've even changed my nonclustered index from a composit to a single index. Nothing will get my scan density over 50%. UGGGGGGG!!!!