Sql-server – Index Maintenance on Clustered PK Index

index-tuningsql server

I know there are similar question to this one, but I couldn't find one dealing with Index Maintenance…

I have some PK Clustered Indexes [int (auto increment).] I always notice that these Indexes frequently have two issues:

  1. Fragmentation is quickly over 10%
  2. Fragmentation is sometimes over 90%

Do I need to apply the same logic (REBUILD/REORGANIZE) to these indexes?

This is how I'm retrieving the fragmentation:

SELECT dbschemas.[name] as 'Schema',
                    dbtables.[name] as 'TableName',
                    dbindexes.[name] as 'IndexName',
                    indexstats.avg_fragmentation_in_percent as 'AvgFragmentationInPct',
                    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 AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
                    AND indexstats.index_id = dbindexes.index_id
                    WHERE indexstats.database_id = DB_ID()
                      AND indexstats.avg_fragmentation_in_percent >= 8
                      AND indexstats.page_count >= 100
                    ORDER BY indexstats.avg_fragmentation_in_percent desc

Best Answer

I wouldn't worry so much about the fragmentation over 10% but over 90% fragmentation on a clustered index is a lot...but are you seeing any performance issues with that specific table when it has that much fragmentation? (If it's not a big table, it may not even be a concern.)

If you are experiencing performance issues with higher fragmentation then you can look into rebuilding (or oftentimes more recommended reorganizing because of its similar benefits while being less heavy of an operation) the index.

For a point of reference, if you had a table with only 10 rows in it, each row on its own data page, then you'd have 90% fragmentation but the performance difference would be negligible compared to if you rebuilt it and it had 0% fragmentation. (This is a very basic example, and holds true for much larger tables to a point.)

By the way, I find this Brent Ozar Article by Kendra Little a good read on rebuilding vs reorganizing indexes.

Related Question