Explanation of Find Fragmented Indexes in a Database in SQL Server

When data is frequently inserted, updated, or deleted, indexes can become fragmented. Fragmentation leads to slower query performance because SQL Server has to work harder to read data from scattered pages.

You can run a script to identify which indexes are fragmented and how much. This helps decide whether to reorganize or rebuild them.

Example Script:

1. SELECT   
2.     OBJECT_SCHEMA_NAME(ips.object_id) AS SchemaName,  
3.     OBJECT_NAME(ips.object_id) AS TableName,  
4.     i.name AS IndexName,  
5.     ips.index_id,  
6.     ROUND(ips.avg_fragmentation_in_percent, 2) AS FragmentationPercent,  
7.     ips.page_count  
8. FROM   
9.     sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips  
10. JOIN   
11.     sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id  
12. WHERE   
13.     i.index_id > 0 -- exclude heaps  
14.     AND ips.page_count > 100 -- ignore small indexes  
15. ORDER BY   
16.     FragmentationPercent DESC;  

Conclusion:

This script lists all fragmented indexes in your database with their fragmentation level. Use it to monitor index health and decide which indexes to reorganize (if 5–30% fragmented) or rebuild (if over 30%). Regular monitoring improves database performance and keeps queries running efficiently.