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

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

Improve this answer
1
  • 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 '12 at 22:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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