July 19, 2011 at 3:29 am
I'm trying to get some stats on our index usage, and am using the sys.dm_db_index_usage_stats dmv. For some reason, it is reporting what appears to be incorrect information on the databases affected.
For example, we have a table which has object_id=53575229.
If I run "select object_id, name, type_desc from sys.tables where object_id=53575229", I correctly get one row.
If I run "select object_id, index_id, name, type_desc from sys.indexes where object_id=53575229", I correctly get the 2 indices related to this table.
However, if I run "select * FROM sys.dm_db_index_usage_stats where object_id=53575229", I get 7 rows with 6 different values for database_id, all of which have the last_user_update within the last 2 days. 2 of the rows match the correct database_id, but I don't understand why the other 5 rows are being returned as there is no object with that object_id in those databases.
Shouldn't the database_id match the database that the object/table belongs to, as MSDN says: "ID of the database on which the table or view is defined."?
Thanks
July 19, 2011 at 3:41 am
How are you determining that the other DB's don't have the object_id?
Craig Outcalt
July 19, 2011 at 3:49 am
Craig
Thanks for the reply, and for pointing me at the obvious.
I was running "select * from sys.objects where object_id=53575229", but I'd forgotten that this is database-specific. Having changed database and run the query again I can see that, yes, unfortunately the other databases do have that object_id!
Adding "and database_id=db_id()" has given me what I need.
Thanks again
Alun
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply