SQL index maintenance strategy

Version:

When looking at your SQL index maintenance strategy, consider the following thresholds and use the T-SQL to verify fragmentation.

Fragmentation thresholds:

≥ 50%: Rebuild index
30–49%: Reorganize index
< 30%: No action

Use this T-SQL to check fragmentation:


SELECT
DB_NAME() AS DBName,
OBJECT_NAME(ps.object_id) AS TableName,
i.name AS IndexName,
ips.index_type_desc,
ips.avg_fragmentation_in_percent
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.indexes i
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
CROSS APPLY sys.dm_db_index_physical_stats(
DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED'
) ips
ORDER BY ips.avg_fragmentation_in_percent DESC;

If you have suggestions for improving this article, let us know!