インデックス断片化・欠損調査

インデックスの断片化具合を調査するSQLです。
断片化されたインデックス再構築する場合は、SQLCommand列に出力されるAlter文を実行します。

インデックス断片化調査

SELECT
 SCM.name 
,OBJCT.name 
,avg_fragmentation_in_percent
,IDX.name
'ALTER INDEX ' + '[' + IDX.name + ']' + ' ON [' + SCM.name + '].[' + OBJCT.name + '] REORGANIZE' AS SQLCommand
FROM sys.dm_db_index_physical_stats (DB_ID(),null,null,null,null) AS IPS
JOIN sys.objects AS OBJCT ON  IPS  .object_id = OBJCT.object_id  and OBJCT.type = 'U'
JOIN sys.schemas AS SCM   ON  OBJCT.schema_id = SCM.schema_id 
JOIN sys.indexes AS IDX   ON  IPS.object_id   = IDX.object_id  AND IPS.index_id = IDX.index_id and IDX.index_id > 0 
WHERE    IPS.avg_fragmentation_in_percent >= 30
ORDER BY IPS.avg_fragmentation_in_percent DESC

インデックス欠損行があるテーブルを調査

SELECT mid.object_id,statement AS table_name  
FROM sys.dm_db_missing_index_details AS mid  
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)  
INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle  
group by  mid.object_id,statement