1

I have a very simple, standard query to show me the fragmentation of indexes within a database, as follows:

SELECT  DB_NAME(DB_ID()) AS DatabaseName,
    OBJECT_NAME(ddips.[object_id]) AS TableName,
    ind.[name] AS IndexName,
    ddips.index_type_desc AS IndexType,
    ddips.avg_fragmentation_in_percent AS FragmentationPercentage,
    ddips.fragment_count AS FragmentCount,
    ddips.avg_fragment_size_in_pages AS AvgFragmentSizeInPages,
    ddips.page_count AS [PageCount]
FROM    sys.dm_db_index_physical_stats (DB_ID(), null, null, null, null) AS ddips
    INNER JOIN sys.indexes ind on ind.[object_id] = ddips.[object_id]
ORDER BY ddips.avg_fragmentation_in_percent DESC

When I run it, however, I see the exact same data - same tablename, indexname, and indextype - returned multiple times with varying percentages of fragmentation. An example is below:

Fragmentation

I don't understand these results. When I open the table in SSMS, open the index, and go to the Fragmentation page, it's telling me that the lowest number, 1.71%, is the fragmentation. Where are all of these other fragmentation numbers coming from? Can anyone help explain these results? Many thanks.

Skkra
  • 121
  • 2
  • 5
  • 1
    Add `AND ind.index_id = ddips.index_id` to your join condition and `index_depth` to the `SELECT` list. – Martin Smith Jun 08 '12 at 20:09
  • Thank you... I knew it was a rookie issue. I gave mdoyle the answer, though you got it first... is there a way to flag your comment as the answer? If you care about the credit. – Skkra Jun 08 '12 at 20:35

1 Answers1

2

You are doing on the join on object_id in the two views, and that is not a unique value. Multiple occurences of a value for object_id means multiple row joins. You need to include index_id to the join condition. EDIT: Yeh, what Martin Smith said!

mdoyle
  • 737
  • 9
  • 22