SQLServerでインデックスの断片化率をSQLで取得する

2017.11.26

インデックスの断片化率を SQL で取得する

USE [table_name]
SELECT
    DB_NAME(t1.database_id),
    OBJECT_NAME(t1.object_id),
    t1.index_type_desc,
    t2.name,
    t1.fragment_count,
    t1.avg_fragmentation_in_percent
FROM
    sys.dm_db_index_physical_stats(DB_ID('[table_name]'), NULL, NULL, NULL, 'LIMITED') t1
    INNER JOIN sys.indexes t2
    ON  t2.object_id = t1.object_id
    and t2.index_id = t1.index_id
WHERE
    t1.avg_fragmentation_in_percent > 50