June 25, 2008 at 7:27 am
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
June 25, 2008 at 7:32 am
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
June 25, 2008 at 7:38 am
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.
Maninder
www.dbanation.com
June 25, 2008 at 1:52 pm
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
June 25, 2008 at 10:51 pm
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