dm_db_index_usage_stats - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

dm_db_index_usage_stats

Author

Topic

helpme
Posting Yak Master

141 Posts

Posted - 2010-11-01 : 15:51:36

When checking this view for a specific object_id, i'm finding entries for the object_id in two different databases, one of which the object doesn't exist in. Why would this happen?

tclaw
Starting Member

5 Posts

Posted - 2010-11-15 : 12:17:01

Can you post the SQL you're running?

By default a single SELECT * FROM sys.dm_db_index_usage_stats will return data for all databases. It's possible that two databases can have the same object_id in the database. You could also have the same object id occur multiple times with the same database_id if you have more than one index.

The following code should help explain.

This will return all object_id's that have the same object_id but exist in different datababases:


SELECT * FROM
	(SELECT database_id, object_id, index_id FROM sys.dm_db_index_usage_stats ) t1
	JOIN
		(SELECT database_id, object_id, index_id FROM sys.dm_db_index_usage_stats ) t2
			ON t1.object_id = t2.object_id AND t1.database_id <> t2.database_id

Continuing you can get the database name by issuing the following

SELECT DB_NAME( [datbase_id])

Then select the object_id from each database.

USE [database_name]
SELECT OBJECT_ID( [object_id] )

Hopefully this helps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources