Sample Header Ad - 728x90

When to rebuild and when to reorganize indexes

0 votes
1 answer
6921 views
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 enter image description here enter image description here enter image description here
Asked by user2438237 (141 rep)
Jul 13, 2014, 06:03 AM
Last activity: Dec 9, 2022, 10:03 AM