Post reply

sys.dm_db_index_usage_stats - why showing incorrect databases?

  • 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

  • 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

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply