Fragmentation hurts database performance by increasing I/O, slowing scans, and reducing parallelism. Regular maintenance—defragmenting clustered and non-clustered indexes on tables/views—improves scan efficiency. Run weekly or bi-weekly.


Types of Fragmentation


File System Fragmentation

Database files should be contiguous on disk (NTFS). Pre-allocate files to expected size at creation to prevent this. Avoid OS-level NTFS defrag tools—they ignore SQL Server internal structures.


Extent Fragmentation

Pages interleave within files (extent = 8 pages/64 KB, but can go to page level). Caused by concurrent DML, heavy updates/deletes. Only full table rewrite (e.g., rebuild clustered index) fixes it. Follow best practices for initial load and DML.


ndex Fragmentation (Logical/Physical)

Caused by INSERT/UPDATE/DELETE → uneven page fullness, page splits. Leads to extra page reads during scans. Indexes can be out of physical page order or logical key order.


Detection

Use sys.dm_db_index_physical_stats to check avg_fragmentation_in_percent (logical), page_count, and fragment_count.


Resolution Options

When fragmentation impacts queries:

1. DROP and recreate clustered index (rebuilds data, redistributes pages; use FILLFACTOR).

2. ALTER INDEX REBUILD (replaces DBCC DBREINDEX):

- Online or offline

- Use SORT_IN_TEMPDB = ON to avoid filegroup fragmentation

- MAXDOP = 1 for ordered pages and better scan performance

3. Avoid ALTER INDEX REORGANIZE for high fragmentation—it can worsen large allocations and doesn't fully compact.


Recommendations

- Set appropriate FILLFACTOR (e.g., 80-90 for update-heavy tables)

- Batch large DML to reduce splits

- Monitor and rebuild only when needed (e.g., >30% fragmentation on large indexes)

- Prioritize this for performance tuning


Excerpt

Index fragmentation from DML causes extra I/O and slower scans in SQL Server. Detect with sys.dm_db_index_physical_stats; fix via ALTER INDEX REBUILD with SORT_IN_TEMPDB=ON and MAXDOP=1. Prevent by pre-allocating files, using FILLFACTOR, and avoiding NTFS defrag.


Meta Title

SQL Server Index Fragmentation: Detection, Impact, and Best Maintenance Practices


Meta Description

Learn how index fragmentation affects SQL Server performance, detect it with sys.dm_db_index_physical_stats, and resolve with ALTER INDEX REBUILD (SORT_IN_TEMPDB, MAXDOP=1, FILLFACTOR) while avoiding common pitfalls.


Tags

SQL Server, index fragmentation, logical scan fragmentation, extent fragmentation, file system fragmentation, sys.dm_db_index_physical_stats, ALTER INDEX REBUILD, SORT_IN_TEMPDB, MAXDOP, FILLFACTOR, index maintenance, page splits, database performance