I have written a script that uses the dynamic management view sys.dm_db_index_physical_stats to identify the indexes for a particular database that need to be rebuilt, then rebuilds them and generates a report of the before and after fragmentation percentages.
Here is my question, why do the indexes with a lower page_count sometimes end up with a higher avg_fragmentation_in_percent after a alter index xxxx on dbo.xxxx rebuild is run?
I also tried alter index xxxx on dbo.xxx reorganize, same results, lower page count indexes didn't change their fragmentation percentage and sometimes the percentage of fragmentation went up????
How weird is that?