When to rebuild and when to reorganize indexes

Question

I have been trying to gather information about rebuilding and reorganizing indexes. From stackoverflow page (How Often should the indexes be re-build in sql-server DB?) i got this query :

SELECT 
    t.NAME 'Table name',
    i.NAME 'Index name',
    ips.index_type_desc,
    ips.alloc_unit_type_desc,
    ips.index_depth,
    ips.index_level,
    ips.avg_fragmentation_in_percent,
    ips.fragment_count,
    ips.avg_fragment_size_in_pages,
    ips.page_count,
    ips.avg_page_space_used_in_percent,
    ips.record_count,
    ips.ghost_record_count,
    ips.Version_ghost_record_count,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.avg_record_size_in_bytes,
    ips.forwarded_record_count
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN  
    sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN  
    sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
    AVG_FRAGMENTATION_IN_PERCENT > 0.0  
ORDER BY
    AVG_FRAGMENTATION_IN_PERCENT, fragment_count

The problem is that when i rebuilt indexes the avg_fragmentation_in_percent increased instead of decreasing. Any pointers?? If this is the normal behavior then what am i missing here??

Previously the avg_fragmentation_in_percent was 30 and after rebuild it has increased to 66.

enter image description here

score 3 · Answer 1 · edited Dec 09 '22 at 10:03

As per followed practice you can rebuild index when fragmentation is >30 % and can reorganize when fragmentation is between 10 and 30 %. You should also include page_count value in your query. If page_count value( which is there in DMV sys.dm_db_index_physical_stats) is <1000 you don't need to rebuild or reorganize such indexes even though they show large fragmentation. I have seen sometimes for such indexes when rebuilt index fragmentation increases but it would hardly matter.

In your case page_count is 3 so absolutely no need to rebuild or reorganize index.

I would also like to see your full rebuild script it it quite possible you may have used less value of fill factor to rebuild index that would cause more number of pages after rebuilt and thus increasing fragmentation.

Below is a article written by Bob Dor from Microsoft where he actually pointed out that due to MAXDOP setting when used during index rebuild can cause increase in fragmentation I am sure it is not applicable to your environment but have a look: Link.

Please use below query and then find out indexes which needs rebuild:

select
    ips.index_type_desc,
    ips.index_depth,
    ips.index_level,
    ips.avg_fragmentation_in_percent,
    ips.fragment_count,
    ips.page_count,
    ips.avg_page_space_used_in_percent,
    ips.record_count
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN  
    sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN 
    sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
    AVG_FRAGMENTATION_IN_PERCENT > 0.0 and page_count >1000 
ORDER BY
    AVG_FRAGMENTATION_IN_PERCENT, fragment_count

When to rebuild and when to reorganize indexes

1 Answers1