Sql-server – Defragmenting indexes SQL Server bases on avg_page_space_used_in_percent

maintenanceperformanceperformance-tuningsql serversql-server-2012

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?

Best Answer

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.

Related Question