SQL Server Index Tuning.

Just listened to some online training on SQL Server Index Fragmentation.

Use Query:
SELECT i.name, indexstats.avg_fragmentation_in_percent, page_count
FROM
sys.dm_db_index_physical_stats(DB_ID(‘winnipeg’), OBJECT_ID(‘lgncc_enquiry’), NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
order by indexstats.avg_fragmentation_in_percent desc;

Use general rules:

1. If page count < 1000 leave
2. If fragmentation < 30 reorganize
3. If fragmentation > 30% rebuild

Advertisement