1

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

2 Answers 2

1

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.

2
  • 1
    Not seeing performance issues at all on these clustered indexes at all... Just noticed them during my Index maintenance jobs...
    – pmeyer
    Nov 30, 2020 at 13:48
  • Then personally I'd look for bigger fish to fry. But if your server has a good maintenance window, and you want to set all phasers to stun, it won't hurt you to rebuild or reorganize those indexes just the same as any other nonclustered index. You just likely won't notice any change in performance in doing so either.
    – J.D.
    Nov 30, 2020 at 13:50
0

Just a note that an index on an ever increasing value shouldn't get fragmented from inserts. Pages will just be added at the end of the linked list.

My suspicion is that you might be using some old command (DBCC SHOWCONTIG) and/or use the wrong column when you check the fragmentation and draw the wrong conclusion.

More details would help.

1
  • Hmm... I would up the page count to 5000. Jumping back and forth on disk is irrelevant for small indexes (and possibly irrelevant for large indexes as well assuming you don't have old magnetic disks). Focus on how full pages are on average instead. Do you see significantly less than full pages? If so, you need to dig deeper. For instance if you delete data that might happen. Dec 2, 2020 at 9:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.