3

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?

eddie_cat
  • 2,527
  • 4
  • 25
  • 43
atalay celebi
  • 79
  • 1
  • 5
  • Must be a mistake in your join statement then. I also had problems with this yearsgo (2008) so cant remember. Do a google search for DMV schema layout. I had one as a PDF or something that I got from MS that showed the relationships. – Namphibian Apr 02 '14 at 02:16
  • If I run the query without the joins only for the sys.dm_db_index_usage_stats view the result is basically the same result occurs. I have results with same incident Id, same object Id (table id) but different database id. but each database id exists only once for one incident. I have a theory about that and i was wondering if anybody can confirm it, even if it does not comply with Microsoft's documentation. Is it possible maybe if a procedure or something under one database access to a table under another database through an index then his index usage stat view also shows it as a line? – atalay celebi Apr 02 '14 at 05:13
  • Did you ever determine the reason? I happen to be seeing the exact same issue. – D-Klotz Jul 22 '21 at 21:24

1 Answers1

7

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
dahvyd
  • 481
  • 2
  • 7
  • 11
dean
  • 9,960
  • 2
  • 25
  • 26
  • 1
    OK. Then can you please tell me the reason why i am seeing the db in that row which has no such table or no such index?!? Please read my comment to an answer above. it is unlikely about limiting or so. – atalay celebi Apr 07 '14 at 01:40
  • sys.dm_db_index_usage_stats returns data of all the indexes currently in metadata cache, for all the databases, join it to sys.databases or not. If you want to see the data cor a certain database only, include a database_id in the WHERE clause. As for the same object_ids in different databases, were they restored from the same backup perhaps? – dean Apr 07 '14 at 05:09
  • 1
    Mate, please stop try answering it just for the sake of answering it. it is not restored or anything. those indexes are UNIQUE!!! so seeing them with other databases is normally not possible with "where" clause or not. database X does not have any index named IXAAA under any table but I am still seeing a row in the result set with this index and this database. simple is that. Remember again: X does not have IXAAA. – atalay celebi Apr 08 '14 at 05:54