SQL Server index usage stats

Question

I have run the query below in order to find indexes to delete.

SELECT d.name AS DatabaseName, t.name AS TableName, i.name AS IndexName, ius.*
FROM sys.dm_db_index_usage_stats ius
JOIN sys.databases d ON d.database_id = ius.database_id
JOIN sys.tables t ON t.object_id = ius.object_id
JOIN sys.indexes i ON i.object_id = ius.object_id AND i.index_id =
ius.index_id
ORDER BY user_updates DESC

But the result set that returns is pretty confusing. I am receiving multiple rows for the same indexes with different database_ids and therefore names. Let's say we have an index and its name is IDXName and its is IDXID. This index is under TBL1 inthe DB1 database. But there are multiple rows for this index with same index name and same index id and same table name but different database ids.

I double checked the Microsoft documentation and it confirms that the database id in that view is the database where the index resides. So how come I have the ids of the databases that that index does not exist in?

Answer 1

You didn't limit the query to current database, that's why you're seeing usage statistics on indexes from different databases:

SELECT d.name AS DatabaseName, t.name AS TableName, i.name AS IndexName, ius.*
FROM sys.dm_db_index_usage_stats ius
JOIN sys.databases d ON d.database_id = ius.database_id
JOIN sys.tables t ON t.object_id = ius.object_id
JOIN sys.indexes i ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE d.database_id = db_id()
ORDER BY user_updates DESC

If all you need from sys.databases is a database name, there's no need for the join at all:

SELECT db_name() AS DatabaseName, t.name AS TableName, i.name AS IndexName, ius.*
FROM sys.dm_db_index_usage_stats ius
JOIN sys.tables t ON t.object_id = ius.object_id
JOIN sys.indexes i ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE ius.database_id = db_id()
ORDER BY user_updates DESC