外部和内部索引碎片率统计

 

--外部碎片率>10 表示外部碎片率发生;
--内部碎片率<75 表示内部碎片率发生

SELECT  OBJECT_NAME(dt.object_id) 表名 ,
        si.name 索引名 ,
        外部碎片率=CASE  WHEN
        dt.avg_fragmentation_in_percent>10 THEN '碎片率高'
        ELSE '正常'
        end
        ,
        内部碎片率= CASE WHEN dt.avg_page_space_used_in_percent <75 THEN '正常'
        ELSE '碎片率高'
        END
       
FROM    ( SELECT    object_id ,
                    index_id ,
                    avg_fragmentation_in_percent ,
                    avg_page_space_used_in_percent
          FROM      sys.dm_db_index_physical_stats(DB_ID('jinriairv2'), NULL, NULL,
                                                   NULL, 'DETAILED')
          WHERE     index_id <> 0
        ) AS dt
        INNER JOIN sys.indexes si ON si.object_id = dt.object_id
                                     AND si.index_id = dt.index_id
                                     AND dt.avg_fragmentation_in_percent >10
                                     AND dt.avg_page_space_used_in_percent <75
ORDER BY avg_fragmentation_in_percent DESC