I have been capturing index fragmentation statistics for one of our databases for few weeks now. I wanted to see how indexes are fragmented over time. I put the results of sys.dm_db_index_physical_stats in one table and started analyzing. For E.g.
object_name index_id avg_fragmentation_in_percent index_type_desc index_level avg_fragment_size_in_pages page_count
SMMTRANSLOG 1 6.58 CLUSTERED INDEX 0 5.60 24145
SMMTRANSLOG 1 90.3 CLUSTERED INDEX 1 1 2331
Some of the indexes have multiple index_levels and avg_fragmentation_in_percent for such indexes is very large. Rebuilding of such indexes only seems to reduce the fragmentation of the 0 level index even when the page count for level 1(and more) indexes is over 1024.
Hence my questions are:
1. Why the rebuilding of the indexes does not lower the avg_fragmentation for level 1 (and more) indexes?
2. Should I be worried about high fragmentation of such indexes?
The MSDN article (http://msdn.microsoft.com/en-us/library/ms188917(v=sql.105).aspx/html) states that
"Current level of the index.
0 for index leaf levels, heaps, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.
Greater than 0 for nonleaf index levels. index_level will be the highest at the root level of an index.
The nonleaf levels of indexes are only processed when mode = DETAILED. "
Any help would be much appreciated.