5

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.

Nazila

ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
Sky
  • 3,684
  • 16
  • 49
  • 68
  • 1
    I assume this is SQL server. Can you tag it that way? – Atilla Ozgur Aug 07 '12 at 07:35
  • 1
    Can you add page_count,index_level,type_desc to the select list and show us some example results? – Martin Smith Aug 07 '12 at 11:37
  • Can you also explain how the tables became empty? Did you delete the rows, truncate the table, drop and re-create, never populate them, etc. – Aaron Bertrand Aug 07 '12 at 12:36
  • Fragmentation should only be considered on indexes that have 1000+ pages. Anything smaller is considered negligible. – Thomas Stringer Aug 07 '12 at 12:45
  • Many thanks for your comments. We are creating the database from scratch and as part of data migration, some tables need to be populated, some not (will be loaded by users through application ). I added page_count, index_level columns. The non clustered index on the empty table has got values below: Avg fragmentation: 57.71484375 Page counts:1024 Type_desc:NONCLUSTERED Index_level:0 Still I can't understand how an index on an empty table can have 1024 page_count?!!! – Sky Aug 07 '12 at 23:16
  • And what if you add record_count,ghost_record_count to the select list too? Any ghost records? – Martin Smith Aug 08 '12 at 06:21
  • Martin, I added the columns you mentioned to the query and I get null for both on that index (belong to empty table). – Sky Aug 09 '12 at 00:25

1 Answers1

3

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?

Community
  • 1
gbn
  • 69,809
  • 8
  • 163
  • 243
  • Thanks for your reply. That makes sense now. Is there any way to update the query above to exclude the empty tables? – Sky Aug 07 '12 at 22:30