SQL : Fragmentation des indexes

Cette requête permet d'évaluer la fragmentation des index de la base de données. Les index fragmentés à plus de 30% auront le commentaire "Rebuild".

SELECT
    DB_NAME() AS [Database Name],
    QUOTENAME(o.name) AS [Table Name],
    stats.index_id AS [Index ID],
    i.name AS [Index Name],
i.type_desc AS [Index Type],
    CAST(stats.avg_fragmentation_in_percent AS DECIMAL (38,2)) AS [Average Fragmentation (%)],
    stats.page_count [Page Count],
    CASE
when stats.avg_fragmentation_in_percent < 30 then 'Reorganize'
when stats.avg_fragmentation_in_percent > 30 then 'Rebuild'
     END AS [Comments]

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'LIMITED') AS stats,sys.objects AS o,sys.schemas AS s, sys.indexes AS i

WHERE
        o.object_id = stats.object_id AND
        s.schema_id = o.schema_id     AND
        i.object_id = stats.object_id AND
        i.index_id = stats.index_id   AND
        stats.avg_fragmentation_in_percent >= 10.0 AND
        stats.page_count >= 5000 AND
        stats.index_id > 0

ORDER BY [Average Fragmentation (%)] DESC,[Page Count] DESC, [Comments]
Previous
« Prev Post