[sys].[dm_db_index_usage_stats] returns more entries in some databases I am using the following query to get some indexes usage statistics:

SELECT DB.[name]
      ,OBJECT_NAME(IDX.[object_id]) AS [Table]    
      ,IDX.[name] AS [Index] 
      ,US.[user_seeks]
      ,US.[user_scans]
      ,US.[user_lookups]
      ,US.[user_updates]
      ,US.[last_user_seek]
      ,US.[last_user_scan]
      ,US.[last_user_lookup]
      ,US.[last_user_update]
FROM [sys].[dm_db_index_usage_stats] US
INNER JOIN [sys].[indexes] IDX
    ON IDX.[index_id] = US.[index_id]
    AND IDX.[object_id] = US.[object_id]
INNER JOIN [sys].[dm_db_index_physical_stats] (DB_ID(),NULL,NULL,NULL,NULL) AS PHS
    ON PHS.[index_id] = US.[index_id] 
    AND PHS.[object_id] = US.[object_id]
INNER JOIN [sys].[dm_db_partition_stats] PRS
    ON PRS.[index_id] = US.[index_id]
    AND PRS.[object_id] = US.[object_id]  
INNER JOIN [sys].[databases] DB
    ON DB.[database_id] = PHS.[database_id]
WHERE [is_primary_key] = 0
    AND US.[user_seeks] = 0
    AND US.[user_scans] = 0
    AND US.[user_lookups] = 0
ORDER BY [Page_Count] DESC


The issue is on some database the [sys].[dm_db_index_usage_stats] returns more entries for certain indexes. For example:



As you can see each entry is updated in different times on different date. Could anyone tell why this is happening?
From taking a quick look, it's quite possible that you're actually looking at indexes on different databases.

Alternatively, you could have a partitioned table as the docs state that sys.dm_db_partition_stats:


  Returns page and row-count information for every partition in the
  current database.


Take it back to basics, don't use DB_ID() and actually use the database_id field within dm_db_index_usage_stats to join to sys.databases.

For now, it would also be best to remove dm_db_index_physical_stats (the INNER JOIN is not a good way to join to it anyway).

SELECT DB.[name] AS [database]
      ,OBJECT_NAME(IDX.[object_id]) AS [Table]    
      ,IDX.[name] AS [Index] 
      ,US.[user_seeks]
      ,US.[user_scans]
      ,US.[user_lookups]
      ,US.[user_updates]
      ,US.[last_user_seek]
      ,US.[last_user_scan]
      ,US.[last_user_lookup]
      ,US.[last_user_update]
FROM [sys].[dm_db_index_usage_stats] US
INNER JOIN [sys].[indexes] IDX
    ON IDX.[index_id] = US.[index_id]
    AND IDX.[object_id] = US.[object_id]
INNER JOIN [sys].[dm_db_partition_stats] PRS
    ON PRS.[index_id] = US.[index_id]
    AND PRS.[object_id] = US.[object_id]  
INNER JOIN [sys].[databases] DB
    ON DB.[database_id] = US.database_id
WHERE [is_primary_key] = 0
    AND US.[user_seeks] = 0
    AND US.[user_scans] = 0
    AND US.[user_lookups] = 0


Check the database names. Are they all definitely the same database?

Add PRS.partition_id to your SELECT. Are they all the same partition?

If that looks to be correct, then perhaps APPLY the dm_db_index_physical_stats table.

SELECT DB.[name]
      ,OBJECT_NAME(IDX.[object_id]) AS [Table]    
      ,IDX.[name] AS [Index] 
      ,US.[user_seeks]
      ,US.[user_scans]
      ,US.[user_lookups]
      ,US.[user_updates]
      ,US.[last_user_seek]
      ,US.[last_user_scan]
      ,US.[last_user_lookup]
      ,US.[last_user_update]
FROM [sys].[dm_db_index_usage_stats] US
INNER JOIN [sys].[indexes] IDX
    ON IDX.[index_id] = US.[index_id]
    AND IDX.[object_id] = US.[object_id]
INNER JOIN [sys].[dm_db_partition_stats] PRS
    ON PRS.[index_id] = US.[index_id]
    AND PRS.[object_id] = US.[object_id]  
INNER JOIN [sys].[databases] DB
    ON DB.[database_id] = US.database_id
CROSS APPLY sys.dm_db_index_physical_stats(US.DATABASE_id, US.OBJECT_ID, US.index_id, NULL, NULL)  AS ddius
WHERE [is_primary_key] = 0
    AND US.[user_seeks] = 0
    AND US.[user_scans] = 0
    AND US.[user_lookups] = 0
ORDER BY ddius.page_count DESC