Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rebuild/Reorg Indexes (meaning of index_level in sys.dm_db_index_physical_stats) Expand / Collapse
Author
Message
Posted Thursday, September 12, 2013 1:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 10:12 AM
Points: 3, Visits: 45
Hello,
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.
Hello
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

Index_Level is
"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.

Thank you.
Post #1494340
Posted Thursday, September 12, 2013 4:07 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 810, Visits: 728
The upper levels of the indexs are not scanned, nor are they always full. No, you should not be worried about fragmentation in these levels as long as leaf level is good.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1494390
Posted Friday, September 13, 2013 7:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 10:12 AM
Points: 3, Visits: 45
Thank you for the quick response.
Post #1494574
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse