Index Fragmentation: Definition, Detection, and Impacts

Index Fragmentation in SQL

Index fragmentation occurs when the logical order of pages in an index does not match the physical order on the disk. This can lead to inefficient data retrieval and degraded query performance. There are two main types of index fragmentation:

1. Internal Fragmentation

• Description: This happens when there is unused space within the data pages. It often occurs due to frequent insert, update, and delete operations, which leave gaps in the pages.
• Impact: Increases the number of pages SQL Server needs to read, leading to slower query performance.
• Example: If a page is only 70% full due to deletions, it causes internal fragmentation.

2. External Fragmentation

• Description: This occurs when the logical order of the pages does not match the physical order. Pages are scattered across the disk, causing SQL Server to perform more I/O operations to retrieve data.
• Impact: Slows down read operations as the disk head has to move more to access the scattered pages.
• Example: If pages are not stored sequentially on disk, it results in external fragmentation.

Identifying Fragmentation

You can identify index fragmentation using the sys.dm _db_index_physical_stats dynamic management view (DMV) in SQL Server. Here’s a query to check fragmentation levels:

SELECT

??? dbschemas.name AS 'Schema',

??? dbtables.name AS 'Table',

??? dbindexes.name AS 'Index',

??? indexstats.avg_fragmentation_in_percent,

??? indexstats.page _count

FROM

??? sys.dm _db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

??? INNER JOIN sys.tables dbtables ON dbtables.object_id = indexstats.object_id

??? INNER JOIN sys.schemas dbschemas ON dbtables.schema_id = dbschemas.schema_id

??? INNER JOIN sys.indexes dbindexes ON dbtables.object_id = dbindexes.object_id

WHERE

??? indexstats.database_id = DB_ID()

ORDER BY

??? indexstats.avg_fragmentation_in_percent DESC;

?

?

Resolving Fragmentation

To resolve index fragmentation, you can use the following methods:

1.??? Reorganize Index:

o?? Description: This operation defragments the leaf level of the clustered and non-clustered indexes by physically reordering the pages to match the logical order.

o?? Command:

o?? ALTER INDEX index_name ON table_name REORGANIZE;

2.??? Rebuild Index:

o?? Description: This operation drops and recreates the index. It removes fragmentation by rebuilding the index from scratch.

o?? Command:

o?? ALTER INDEX index_name ON table_name REBUILD;

When to Use Each Method

• Reorganize: Use when fragmentation is low to moderate (typically between 5% and 30%).
• Rebuild: Use when fragmentation is high (greater than 30%).