列出需要重建的索引

SELECT
    OBJECT_SCHEMA_NAME(FRAG.[object_id]) + '.' + OBJECT_NAME(FRAG.[object_id]),
    SIX.[name],
    FRAG.avg_fragmentation_in_percent,
    FRAG.page_count
FROM
    sys.dm_db_index_physical_stats
    (
        DB_ID(),    --use the currently connected database
        0,          --Parameter for object_id.
        DEFAULT,    --Parameter for index_id.
        0,          --Parameter for partition_number.
        DEFAULT     --Scanning mode. Default to "LIMITED", which is good enough
    ) FRAG
    JOIN
    sys.indexes SIX ON FRAG.[object_id] = SIX.[object_id] AND FRAG.index_id = SIX.index_id
WHERE
    --don't bother with heaps, if we have these anyway outside staging tables.
    FRAG.index_type_desc <> 'HEAP' AND
    (
    --Either consider only those indexes that need treatment
    (FRAG.page_count > 128 AND FRAG.avg_fragmentation_in_percent > 10)  
    )
ORDER BY
    FRAG.avg_fragmentation_in_percent DESC;


※根據官方建議當索引的分裂情況在5% ~ 30%就需要重組。當超過30%的時候就最好是重建。