DBCC reindex not improving fragmentation

  • Hello

    I have a job which reindexes indexes which have a logical fragmentation of over a certain percentage. However, although SMS says that the reindex has completed, the index still shows the same level of fragmentation

    Why would this be?

    Thanks

  • Indexes on smaller tables often won't defragment, because fragmentation doesn't matter in them. I don't remember the threshhold, but I think it's something like 10k rows before index fragmentation actually matters. Below that, it won't bother fixing it.

    If you want to override that, drop and recreate the index manually or with a script. But it's usually fine to leave it alone.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Also check to verify those are not heap tables. (with indid=0)

    the Heap tables cannot be de-fragmented, by the DBCC commands.

    There are other ways to take care of the heap tables.

    Select * from sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,NULL)

    Where index_type_desc <> 'HEAP'

    now check if your table is in the list.

  • Microsoft has as whitepaper on this.

    "Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

    Just put a filter on page_count in sys.dm_db_index_physical_stats.

    I have a stored procedure that you are welcome to use.

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    Ola Hallengren

    http://ola.hallengren.com

  • wat u do is jus check indexes with dbcc showcontig and if u are getting the same result drop and recreate the indexes .... and try to create cluster index on the table .....

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

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