INDEX FRAGMENTATION

• dbit

  Valued Member

  Points: 67

  More actions

  December 9, 2009 at 10:57 am

  #213299

  SELECT INDEX_ID, AVG_FRAGMENTATION_IN_PERCENT

  FROM sys.dm_db_index_physical_stats (db_id(),

  Object_ID(production.prod),

  Default, Default, 'DETAILED');

  when I execute the above to get fragmentation %, i get more than one Index_id:

  index_id avg_fragmention_in_percent

  099.4397759103641

  20.603621730382294

  280

  20

  My question is, why are there more than one index ids? what does it mean exactly? after I execute the query to clean up fragmentation, not all index IDs decreased?

  ANY IDEAS?

  THANKS.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  December 9, 2009 at 11:05 am

  #1090053

  As per Books Online, in detailed mode index physical stats produces one row for each level of the index tree.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• george sibbald

  SSC Guru

  Points: 104210

  More actions

  December 9, 2009 at 11:10 am

  #1090059

  because you have specified detailed you are getting the non-leaf levels of the index as well. return the index_level column as well or use LIMITED.

  fragmentation of non leaf nodes wont change if index level is too small or you are reorganising rather than rebuild

  ---------------------------------------------------------------------