1

I am wondering if I should take into account information from

sys.dm_db_index_physical_stats

column

avg_page_space_used_in_percent

when deciding on defragmenting/not defragmenting an index.

How is it possible that I see low avg_fragmentation_in_percent value and also low value for avg_page_space_used_in_percent?

Based on Brent Ozar article I could think that I should rebuild an index as there is a lot of free space on pages so it is a waste of disk and RAM space. But scripts as Ola Hallengren's, for example, analyze only avg_fragmentation_in_percent, don't they?

7
  • What is the size of the table in question?
    – gbn
    Jul 10 '18 at 12:49
  • 1
    Consider a freshly rebuilt index with a low fill factor will have both low fragmentation and avg space used.
    – Dan Guzman
    Jul 10 '18 at 12:52
  • 1
    While avg_fragmentation_in_percent shows level of fragmentation which means out of order pages ,avg_page_space_used_in_percent indicates page fullness. They are not directly related and the latter has little significance in fragmentation. Yes if the value is high means there might have been lot of page splits and this have created lot of space on page.
    – Shanky
    Jul 10 '18 at 13:02
  • @gbn, 5468 pages
    – George K
    Jul 10 '18 at 13:19
  • @Shanky so page splits not necessarily lead to fragmentation? They can lead only to page fullness? Does not seen correct.... the point of the question however was 'should I rebuild indexes based on avg_page_space_used_in_percent?'
    – George K
    Jul 10 '18 at 13:47
1

I have these rules of thumb:

  • if there are fewer than 10,000 pages in a table, I don't really care about fragmentation;

  • if the storage layer is solid-state, I don't really care about fragmentation;

  • once or twice a month I might rebuild indexes on really fragmented tables, but only if fragmentation is demonstrably causing performance issues, which it usually isn't.

Fill factor can affect fragmentation, as can page splits, bad clustered index choices, and so on. Find the actual performance issues using a baseline, and focus on those.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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