Join several tables in SQL Server

Question

I have a select statement which must join several tables including sys.indexes, sys.objects, sys.schemas and sys.dm_db_index_physical_stats in order to get a table like this

This is the statement:

select
    db_name() as [Database],
    sc.[name] as [Schema],
    obj.[name] as [Table],  
    idx.[type_desc] as [Index Type],
    idx.[name] as [Index Name],
    ips.[avg_fragmentation_in_percent] as [Fragmentation Percent]
from 
    sys.indexes as idx
inner join 
    sys.objects as obj on idx.object_id = obj.object_id
inner join 
    sys.schemas as sc  on obj.schema_id = sc.schema_id
inner join 
    sys.dm_db_index_physical_stats( NULL,NULL, NULL, NULL ,'LIMITED') AS ips on obj.object_id = ips.object_id 
where 
    idx.[name] is not null 
    and obj.[type] = 'u' or obj.[type] = 'v' 
order by 
    [Fragmentation Percent] desc

The problem is that this statement works wrong and includes some rows twice (does something like a cross join but not exactly that). Can anyone point out my mistake?

Thanks for your time!

Answer 1

Use CROSS_APPLY instead of INNER JOIN. This will allow you to pass correlated values to the table-valued function. Added partition number to the result here:

SELECT
    db_name() as [Database],
    sc.[name] as [Schema],
    obj.[name] as [Table],  
    idx.[type_desc] as [Index Type],
    idx.[name] as [Index Name],
    ips.partition_number AS [Partition Number],
    ips.[avg_fragmentation_in_percent] as [Fragmentation Percent]
FROM sys.indexes as idx
INNER JOIN sys.objects as obj on idx.object_id = obj.object_id
INNER JOIN sys.schemas as sc  on obj.schema_id = sc.schema_id
CROSS APPLY sys.dm_db_index_physical_stats( DB_ID(), idx.object_id, idx.index_id, NULL ,'LIMITED') AS ips
ORDER BY [Fragmentation Percent] desc;

The mistakes with your original query are you didn't include index_id in the join to sys.dm_db_index_physical_stats and did not filter for objects in the current database (the same object_id may exist in different databases). The WHERE clause is superfluous so I removed it.

You should exclude small indexes for defragmentation purposes since those are allocated from mixed extents and will report high fragmentation even when freshly rebuilt.