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