asked
Index Fragmentation
Hi, In order to deal with index fragmentation, I have set up a nightly maintenance plan that does index rebuild on line and 90% fill factor. But this maintenance plan does not de-fragment the indexes fully. therefore I do need to manually rebuild the indexes. I have a session with Microsoft Premium Engineers and they just informed that maintenance plan task can not completely does this de-fragmentation. I need to do that manual rebuild. But I am not happy with their answer as I can not understand why can't it do that? Can anyone explain me the reason of this? Also can we rebuild the index online through SSMS? Regards Zahid
I agree, I would be curious as to why they are saying that the index plan is not completing. I am not a big fan of the plans, and even more so for databases that have any sort of production on them. One think you may want to consider is using a script to rebuild your indexes. There are a couple really good ones out there: SQLFool (
http://sqlfool.com/2009/03/automated-index-defrag-script/) Ola Hallengren (
http://ola.hallengren.com/) One of the reasons that I recomend doing this is because this way you can re-index only those indexes that need it.
What reason did Microsoft give for the Maintenance Plan not completing? Are these large or small indexes?
MS did not explain the reason. They are small indexes. also I have tried with SQL command to rebuild index ALTER INDEX ALL ON dbo.TableName REBUILD WITH (FILLFACTOR = 90, ONLINE = ON) Still, the Fragmentation that is Avg_Fragmentation_in_Percent is 93. I think If i use wizard then it might de-frag the indexes. But it realy time consuming to rebuild one by one using wizard. Any idea? Is it the fill factor? As fragmentation is of two types: Internal and External. I think Internal fragmentation has some relation with fillfactor.
Indexes that are less than 8 pages in size will be stored on mixed extents within the system. You can't defrag them. In fact, trying to defrag them can even cause fragmentation to increase, but this isn't harmful since the number of pages is so small. As a general rule, I'd make sure that the indexes you're attempting to defrag are least about 100 pages. The Microsoft recommendation is above 1000 pages. Since you're using the Maintenance Wizard, I don't think you can filter for page size. But just don't worry about it for the small indexes.
I am not agree with Microsoft Premium Engineers that maintenance plan task can not completely does this de-fragmentation because I also implemented this on so many database and it works fine. Only valid reason of index not de-fragmentation is given by Grant & Kev.
