sys.dm_db_index_usage_stats - why showing incorrect databases?

• mister boom

  Say Hey Kid

  Points: 663

  More actions

  July 19, 2011 at 3:29 am

  #241665

  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

• SQLBOT

  SSCrazy Eights

  Points: 8014

  More actions

  July 19, 2011 at 3:41 am

  #1354678

  How are you determining that the other DB's don't have the object_id?

  Craig Outcalt

  ---

  
  Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
  My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

• mister boom

  Say Hey Kid

  Points: 663

  More actions

  July 19, 2011 at 3:49 am

  #1354680

  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