IndexFragmentationReport.sql

/*
Purpose:
- Report rowstore index fragmentation and suggest a review action by threshold.

Safety:
- Read-only. LIMITED mode is used to reduce the cost of the scan.
- Do not rebuild indexes only because a percentage crosses a generic threshold.
  Consider page count, workload, edition, maintenance window, and log capacity.

Requirements:
- SQL Server 2012 or later.
- VIEW DATABASE STATE.

Customization:
- Adjust @MinPageCount, @ReorganizeFrom, and @RebuildFrom.
*/

SET NOCOUNT ON;

DECLARE @MinPageCount BIGINT = 1000;
DECLARE @ReorganizeFrom DECIMAL(5, 2) = 10.0;
DECLARE @RebuildFrom DECIMAL(5, 2) = 30.0;

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,
    ips.partition_number,
    ips.index_type_desc,
    ips.page_count,
    ips.fragment_count,
    CAST(ips.avg_fragmentation_in_percent AS DECIMAL(6, 2)) AS fragmentation_percent,
    CAST(ips.avg_page_space_used_in_percent AS DECIMAL(6, 2)) AS page_density_percent,
    CASE
        WHEN ips.avg_fragmentation_in_percent >= @RebuildFrom THEN 'REVIEW REBUILD'
        WHEN ips.avg_fragmentation_in_percent >= @ReorganizeFrom THEN 'REVIEW REORGANIZE'
        ELSE 'NO ACTION BY THRESHOLD'
    END AS suggested_review
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
JOIN sys.tables AS t
    ON t.object_id = ips.object_id
JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
JOIN sys.indexes AS i
    ON i.object_id = ips.object_id
   AND i.index_id = ips.index_id
WHERE t.is_ms_shipped = 0
  AND ips.index_id > 0
  AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
  AND ips.page_count >= @MinPageCount
  AND ips.avg_fragmentation_in_percent >= @ReorganizeFrom
ORDER BY fragmentation_percent DESC, ips.page_count DESC, schema_name, table_name, index_name;