I ran the following query on my database to identify the fragmented indexes. However I surprised when saw the Indexes belong to some empty tables among the highly fragmented indexes! How can it be possible? I assume if there is no data in a table, the index should be blank and can not be fragmented.
SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, N'Limited') AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
Could someone give me an explanation?
Thank you in advance.
Fragmentation values are meaningless for small tables.
An empty (or emptied) table is small.
Small is determined by how many 8k data pages are allocated to it.
For example, see this question: Why is my database still fragmented after I rebuilt and reindexed everything?