罠①: 断片化率と page_count の見方

-- 全 INDEX の断片化を一発で返す (SAMPLED モード)
SELECT
    OBJECT_NAME(ps.object_id) AS table_name,
    si.name                   AS index_name,
    si.type_desc              AS index_type,
    ps.avg_fragmentation_in_percent AS frag_pct,
    ps.avg_page_space_used_in_percent AS page_used_pct,
    ps.page_count,
    ps.fragment_count,
    -- 判断ヒント列
    CASE
        WHEN ps.page_count < 1000                        THEN '放置 (ROI 不足)'
        WHEN ps.avg_fragmentation_in_percent <  5        THEN '放置'
        WHEN ps.avg_fragmentation_in_percent < 30        THEN 'REORGANIZE'
        ELSE                                                  'REBUILD'
    END AS suggested_action
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ps
JOIN sys.indexes si
    ON si.object_id = ps.object_id AND si.index_id = ps.index_id
WHERE si.name IS NOT NULL
  AND ps.page_count > 0
ORDER BY ps.avg_fragmentation_in_percent DESC;