SQL Serverでインデックス断片化情報を抽出する

適切なインデックスをつけているにもかかわらずデータベースのパフォーマンスが悪い場合、インデックスの断片化が起こっている可能性がある。
クエリのパフォーマンスを向上させてリソースの消費を削減するためにインデックスのメンテナンスを最適化するで説明されるように、インデックスの断片化情報などの統計を見ながら、インデックスの再構成やインデックスの再構築を行うとパフォーマンスが改善されることがある。

SQL Serverのインデックス断片化統計情報は、sys.dm_db_index_physical_stats()から取得できる。構文は以下の通りである。

sys.dm_db_index_physical_stats (   
    { database_id | NULL | 0 | DEFAULT }  
  , { object_id | NULL | 0 | DEFAULT }  
  , { index_id | NULL | 0 | -1 | DEFAULT }  
  , { partition_number | NULL | 0 | DEFAULT }  
  , { mode | NULL | DEFAULT }  
)  

modeには、LIMITED、SAMPLED、DETAILEDのいずれかを指定することができる。DETAILEDではすべての統計が返される。

戻り値の avg_fragmentation_in_percent は、論理的な断片化率 (インデックスが順序どおりになっていないページの割合)である。一般に30%を超えるとパフォーマンスへの影響に注意が必要といわれる。

他に、平均のページ密度（avg_page_space_used_in_percent）も

以下は、データベース内のすべてのテーブル上のインデックスについて、すべての統計（DETAILED）から、断片化率が30%以上のものを取得するクエリである。

SELECT 
    SCM.name AS schema_name,
    OBJCT.name AS table_name,
    IDX.name AS index_name,
    IPS.avg_fragmentation_in_percent,
    IPS.fragment_count,
    IPS.avg_fragment_size_in_pages,
    IPS.page_count,
    IPS.avg_page_space_used_in_percent,
    IPS.record_count,
    IDX.type_desc,
    IDX.is_primary_key,
    IDX.is_unique,
    IDX.is_disabled

FROM sys.dm_db_index_physical_stats (DB_ID(),null,null,null,'DETAILED') AS IPS 
LEFT OUTER JOIN  sys.objects AS OBJCT 
    ON  IPS.object_id = OBJCT.object_id 
LEFT OUTER JOIN  sys.indexes AS IDX 
    ON  IPS.object_id = IDX.object_id  AND IPS.index_id = IDX.index_id 
LEFT OUTER JOIN  sys.schemas AS SCM 
    ON  OBJCT.schema_id = SCM.schema_id 

WHERE OBJCT.type = 'U'
    AND   IDX.index_id > 0 

AND   IPS.avg_fragmentation_in_percent > 30 

ORDER BY IPS.avg_fragmentation_in_percent DESC;