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.

    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.


  • 1
    I assume this is SQL server. Can you tag it that way? Aug 7, 2012 at 7:35
  • 1
    Can you add page_count,index_level,type_desc to the select list and show us some example results? Aug 7, 2012 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. Aug 7, 2012 at 12:36
  • Fragmentation should only be considered on indexes that have 1000+ pages. Anything smaller is considered negligible. Aug 7, 2012 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 7, 2012 at 23:16

1 Answer 1


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?

  • Thanks for your reply. That makes sense now. Is there any way to update the query above to exclude the empty tables?
    – Sky
    Aug 7, 2012 at 22:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.