I have a weird behavior that I'm trying to explain to a client with SQL express 2014 (12.0.2269.0). The table in question has 4.5 million rows and has over 60 thousand pages. The average fragmentation for that index is 54% before and after the rebuild. the only way I was able to fix the issue was to drop and recreate the clustered index.
Has anyone ever witnessed this behavior? Can it be as simple as upgrading to SP2, although I didn’t find any direct correlation with the SP1 and SP2 release notes?
Any help would be greatly appreciated.
This is the query I use to look at the average fragmentation level:
SELECT t.name AS TableName, sch.name as SchemaName, i.name AS IndexName, s.avg_fragmentation_in_percent, ROW_NUMBER() OVER (PARTITION BY t.name order by t.name) as RowNum, TR.RowCnt FROM SYS.TABLES t INNER JOIN sys.schemas sch on t.schema_id=sch.schema_id JOIN SYS.INDEXES i ON t.object_id = i.object_id JOIN SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) s ON t.object_id = s.object_id AND i.index_id = s.index_id LEFT JOIN (SELECT o.OBJECT_ID, ddps.row_count as RowCnt FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id WHERE i.index_id < 2 AND o.is_ms_shipped = 0) TR ON TR.object_id = t.object_id WHERE t.type = 'U' AND s.avg_fragmentation_in_percent > 5 AND i.name is not null ORDER BY t.name, s.avg_fragmentation_in_percent