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
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.