Monitoring Indexes

Overview

Effective index monitoring helps maintain query performance and reduces resource consumption. This guide covers the tools and techniques available in SQL Server to assess index health, identify fragmentation, detect missing indexes, and automate maintenance.

Fragmentation

Fragmented indexes can cause inefficient I/O. Use sys.dm_db_index_physical_stats to evaluate fragmentation levels.

SELECT 
    db_name(database_id) AS DatabaseName,
    OBJECT_NAME(object_id) AS TableName,
    name AS IndexName,
    index_id,
    avg_fragmentation_in_percent,
    page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
JOIN sys.indexes i ON i.object_id = sys.dm_db_index_physical_stats.object_id
                 AND i.index_id = sys.dm_db_index_physical_stats.index_id
WHERE page_count > 100
ORDER BY avg_fragmentation_in_percent DESC;

Interpretation:

• 0–10%: Low fragmentation – no action needed.
• 10–30%: Consider reorganizing the index.
• >30%: Rebuild the index for optimal performance.

Missing Indexes

SQL Server's Dynamic Management Views can suggest indexes that could improve query performance.

SELECT 
    mid.statement AS SqlStatement,
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
JOIN sys.dm_db_missing_index_groups AS mig 
    ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details AS mid 
    ON mig.index_handle = mid.index_handle
WHERE migs.user_seeks > 0
ORDER BY improvement_measure DESC;

Review the suggested indexes and evaluate their impact before implementation.

Statistics

Accurate statistics enable the optimizer to choose efficient execution plans. Use UPDATE STATISTICS and sp_autostats to manage them.

EXEC sp_autostats 'YourDatabaseName', 'ON';
GO
UPDATE STATISTICS dbo.YourTableName WITH FULLSCAN;

Alerts & Reports

Set up alerts for index health using SQL Server Agent jobs and Database Mail.

EXEC msdb.dbo.sp_add_alert 
    @name=N'IndexFragmentationAlert',
    @message_id=0,
    @severity=0,
    @enabled=1,
    @delay_between_responses=0,
    @include_event_description_in=1,
    @notification_message=N'High index fragmentation detected.',
    @job_id=N'00000000-0000-0000-0000-000000000000';

Use the built‑in Index Usage Report in SSMS for a visual overview.

Best Practices

• Schedule regular index maintenance based on fragmentation thresholds.
• Prioritize rebuilds for large, heavily fragmented indexes.
• Monitor index usage patterns; drop unused indexes to reduce overhead.
• Combine index maintenance with statistics updates.
• Automate reporting to track trends over time.