Querying Table for Index Fragmentation - Multiple Different Results for Same Index I am using the following query to check index fragmentation on a table. Each index returns a 3 rows and each one has a different value for "avg_fragmentation_in_percent". The second row seems to be the accurate value when I check the index fragmentation via the Properties menu of the actual index. 

I am curious about the other values that are returned because one row says 100% fragmentation and another row says 0% fragmentation. Does anyone have an idea why I am seeing this?

DECLARE @db_id SMALLINT;  
DECLARE @object_id INT;  

SET @db_id = DB_ID(N'wfm');  
SET @object_id = OBJECT_ID(N'wfm.dbo.Lab_Employee_Time_Off');

IF @db_id IS NULL  
BEGIN;  
    PRINT N'Invalid database';  
END;  
ELSE IF @object_id IS NULL  
BEGIN;  
    PRINT N'Invalid object';  
END;  
ELSE  
BEGIN;

SELECT OBJECT_NAME(ips.OBJECT_ID) AS 'TableName'
 ,i.NAME AS 'IndexName'
 ,ips.index_id
 ,ips.index_type_desc
 ,ips.avg_fragmentation_in_percent
 ,ips.avg_page_space_used_in_percent
 ,ips.page_count
FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL, 'DETAILED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
 AND (ips.index_id = i.index_id)
ORDER BY i.name, avg_fragmentation_in_percent DESC

END;  
GO
There's a row for each level in the index (add ips.index_level to the output and you will see that there is a row for each level). 

Your query should probably simply filter out any row with a low page count (say, 1000), which will eliminate all but level 0 in most cases.