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.

Table and fill factor Table Structure Table output Fragmentation after rebuid

Try to post the fill factor of your table as well as your table layout. It sounds like you need to adjust your fill factor based off of the results of the rebuild. — JStead, Jul 13 '14 at 02:45
@JStead : I have uploaded the screen shots.. The fill factor is 0 — user2438237, Jul 13 '14 at 05:02
This is a pretty small index based on the stats you have in addition the table is pretty skinny. Walk the fill factor down from 95 to around 70 in chunks of 5 at each step rebuild your index. If you get in the 10-20 percent fragmentation range based off how small this table is I would say it is mission complete. — JStead, Jul 13 '14 at 10:32

Shanky · Answer 1 · 2015-08-06T09:46:29.133

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??

This is a normal behavior if your index is small . If index has page_cont <1000 after rebuild pages are not allocated from uniform extent but if index is big pages would be allocated from inform extent. Due to pages being allocated from mixed extent chances of fragmentation increase. But such fragmentation would not have any side affect.

When pages are allocated from Mixed extent they can be allocated to any particular IAM chain, this means that it may hold pages allocated to possibly 8 separate IAM. And thus they can be scattered and lying anywhere thus causing logical fragmentation.

I have explained it in more details in The article Why is my index still fragmanted after rebuild

When to rebuild and when to reorganize indexes

1 Answers1