Post reply

sys.dm_db_index_usage_stats. why are there multiple values in the Database_ID column?

  • October 7, 2010 at 4:59 am

    #224823

    I believe the dmv sys.dm_db_index_usage_stats is database dependent. What i mean is if i set the context to Database_A, it will only return usage for indexes in Database_A.

    To confirm this i ran the dmv against my datbase db_1 and it returned 555 rows.

    I then ran it against db_2 and it returned 240 rows, confirming its data is database context specific.

    But when i run a query against the dmv, against a particular db, the value in the Database_ID column can be the id of ANY of the databases on the server.

    I know this column is the db_ID of the database of the table the index is on. but if this query is only specific to the database its currently running in, how can this column be different?

  • October 7, 2010 at 6:32 am

    #1233461

    That's a server level query. The security setting on it is View Server State, not View Database State. So it includes the DB_ID so that you can filter for a specific database.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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