インデックスの断片化率を調べる方法

クエリのパフォーマンスが悪くなってきたとき、インデックスがうまく作用していない場合があります。
意図したインデックスを使用しているにも関わらず想定されるパフォーマンスを得られない場合、インデックスが断片化している可能性があります。

そこで、インデックスの断片化率を取得し、状況を確認する方法です。

【実行手順】

• 「sys.dm_db_index_physical_stats 」を参照してインデックスの断片化率を取得することができます。
  
  [MSDN] sys.dm_db_index_physical_stats (Transact-SQL)
  http://msdn.microsoft.com/ja-jp/library/ms188917.aspx
  

  
  
  --インデックスと断片化率の一覧を取得する
  SELECT 'ALTER INDEX ' + '[' + C.name + ']' + ' ON [' + D.name + '].[' + B.name + '] REBUILD' AS 'rebuild command'
        ,D.name AS schemaname
        ,B.name AS table_name
        ,C.name AS index_name
        ,A.avg_fragmentation_in_percent
        ,A.page_count
  
  FROM sys.dm_db_index_physical_stats (DB_ID(),null,null,null,null) AS A 
  
  LEFT OUTER JOIN  sys.objects AS B 
  ON  A.object_id = B.object_id 
  
  LEFT OUTER JOIN  sys.indexes AS C 
  ON  A.object_id = C.object_id  AND A.index_id = C.index_id 
  
  LEFT OUTER JOIN  sys.schemas AS D 
  ON  B.schema_id = D.schema_id 
  
  WHERE B.type = 'U'
  AND   C.index_id > 0 
  
  --断片化率が30%以上のものを抽出する
  AND   A.avg_fragmentation_in_percent > 30 
  
  ORDER BY A.avg_fragmentation_in_percent DESC; 
  
  

【参考情報】