SQL Server 2012, rebuild not lowering avg fragmentation

Question

I have a script identifying what indices to rebuild.

select 
    'alter index ' + name + ' on ' + @dbname + '.dbo.' + OBJECT_NAME(a.object_id) + ' rebuild;'
from sys.dm_db_index_physical_stats (DB_ID(@dbname), NULL, NULL, NULL, NULL) AS a
inner join sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
where avg_fragmentation_in_percent > 30

Which, e.g generates:

alter index FooIndex on FooDb.dbo.FooTable rebuild;

But after I execute the alter statement the index still has a high fragmentation value and if I execute it again it don't get any lower (50%). Any input on what could be wrong would be highly appreciated.

UPDATED: I manually increased the size of the DB which managed to lower the fragmentation on some of the indices, but still not all. Still have a couple around 50%.

//Daniel

Answer 1

Have a look a the fragment_count - that's one of the fields in the sys.dm_db_index_physical_stats view. You should really be rebuilding indexes with a certain page threshold and as per best practices, it is best to rebuild an index having more than 1000 pages.

some reference can be found http://connect.microsoft.com/SQLServer/feedback/details/244214/index-rebuild-doesnt-affect-fragmentation

Answer 2

Is it a small index? Small indexes will always show high levels of fragmentation. You can read more at Ola Hallengren's site which has a highly rated SQL Server maintenance package - look at the PageCountLevel setting, which specifies that Microsoft recommends leaving indexes under 1000 pages - with the link.

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Answer 3

You should also filter from consideration small tables - the definition of 'small', 'big' etc. is constantly changing, but if your tables/indexes are less than 8-10 MB it is probably not worth performing the work (and SQL Server may very well not be able to improve the fragmentation levels that much anyway). So add a WHERE clause that looks at SUM(reserved_page_count) FROM sys.dm_db_partition_stats and only include those having greater than, say, 1000 as @Kin mentioned.

I'd also suggest that unless you are actually experiencing performance issues due to the fragmentation, and have proven this is the source, don't obsess over fragmentation levels. Having low numbers there doesn't get you any prize, and this constant maintenance might be worse for your overall system performance than just leaving it alone.

Also see Why is my database still fragmented after I rebuilt and reindexed everything?