Fragmentácia indexov

USE [{{DatabaseName:AdventureWorks}}];

SELECT  OBJECT_SCHEMA_NAME(ips.object_id) + '.' + OBJECT_NAME(ips.object_id) AS table_name,
        i.name                                   AS index_name,
        ips.index_type_desc,
        ips.avg_fragmentation_in_percent,
        ips.page_count,
        CASE WHEN ips.avg_fragmentation_in_percent >= 30 THEN 'REBUILD'
             WHEN ips.avg_fragmentation_in_percent >= 5  THEN 'REORGANIZE'
             ELSE 'OK' END                       AS recommendation
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
JOIN    sys.indexes AS i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE   ips.page_count >= {{MinPages:1000}}
  AND   ips.index_id > 0
ORDER BY ips.avg_fragmentation_in_percent DESC;