GilaMonster (1/7/2011)
In general, in most cases, worry about the leaf level and no other. The higher levels of the index are generally not large enough for fragmentation to matter, and they're far more likely to be in memory where fragmentation doesn't matter.If you have specific cases (huge indexes) that are exceptions, treat them separately.
SELECT object_id, avg_fragmentation_in_percentFROM MyTable
WHERE avg_fragmentation_in_percent > 30 and page_count > 1000 and index_level = 0 and alloc_unit_type_desc = 'IN_ROW_DATA'
In the example I gave above the largest table currently in this database would be skipped over based on the above filtering criteria...
What is to be made of the 'intermediate' levels of the clustered index that have very high fragmentation?
Is there a criteria that you review to say that 'level' #2 has high fragmentation but because of xxx and xxx we shouldn't be concerned
vs.
level #3 of this index has xxx fragmentation and because of xxx and xxx this index should be considered for a rebuild EVEN though the 0 level of the index shows only 6% fragmentation.
GilaMonster (1/7/2011)
p.s. There are several very good index rebuild scripts already written and available for use. Don't reinvent the wheel.
Believe me I am not trying to write something from scratch but when you search 'index rebuild' and see 275 results come back it is a little time consuming to determine which is good and which is not good...
it would be helpful if the search criteria allowed to sort based on views / ratings / etc...
Also if you have a recommendation and have a moment please feel free to point me in that direction I would greatly appreciate it.
Thanks again for the article and the reply,
Lee