question

Zahid avatar image
Zahid 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
indexingfragmentation
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
What reason did Microsoft give for the Maintenance Plan not completing? Are these large or small indexes?
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Small indexes can still have fragmentation, see : and
2 Likes 2 ·
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.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Grant. :)
0 Likes 0 ·
Sharma avatar image
Sharma answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image
Wilfred van Dijk answered
Is this bug hitting you: http://support.microsoft.com/kb/2292737 ?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.