For some time now I have been using the following query to find the index usage statistics for all indexes in a database.
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES, last_user_seek, last_user_scan, last_user_lookup
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
WHERE I.[NAME] not like 'PK%'
OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 order by user_updates desc
But yesterday one of me colleagues asked me if it is possible for two objects in two different databases to have the same object_id. The DMV sys.dm_db_index_usage_stats had a column for database_id and my query would never check the DB_ID of the object_id. S
Upon further investigation I found that this was indeed true. It is possible the the OBJECT_ID for two objects in two different databases could be the same. Closer inspection revealed that the query was actually giving a cumulative value for the index stats for all the objects with the same object_id, irrespective of whether the objects were in the same database or not.
So the above mentioned query would not give the exact index stats for a given database. So I made a slight modification to the query and now I check the database_id for each record in the DMV. This gives me more accurate results.
So I learned two things today.
1) Object_id is only unique within a database but not within the instance. Two objects in two different databases can have the same object_id.
2) The DMV sys.dm_db_index_usage_stats contains info for all the databases, so it is important to query that DMV for a specific DB_ID.
The correct query to find index utilization statistics is
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES, last_user_seek, last_user_scan, last_user_lookup
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
WHERE I.[NAME] not like 'PK%' and s.database_id = DB_ID() and
OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 order by user_updates desc
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES, last_user_seek, last_user_scan, last_user_lookup
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
WHERE I.[NAME] not like 'PK%'
OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 order by user_updates desc
But yesterday one of me colleagues asked me if it is possible for two objects in two different databases to have the same object_id. The DMV sys.dm_db_index_usage_stats had a column for database_id and my query would never check the DB_ID of the object_id. S
Upon further investigation I found that this was indeed true. It is possible the the OBJECT_ID for two objects in two different databases could be the same. Closer inspection revealed that the query was actually giving a cumulative value for the index stats for all the objects with the same object_id, irrespective of whether the objects were in the same database or not.
So the above mentioned query would not give the exact index stats for a given database. So I made a slight modification to the query and now I check the database_id for each record in the DMV. This gives me more accurate results.
So I learned two things today.
1) Object_id is only unique within a database but not within the instance. Two objects in two different databases can have the same object_id.
2) The DMV sys.dm_db_index_usage_stats contains info for all the databases, so it is important to query that DMV for a specific DB_ID.
The correct query to find index utilization statistics is
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES, last_user_seek, last_user_scan, last_user_lookup
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
WHERE I.[NAME] not like 'PK%' and s.database_id = DB_ID() and
OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 order by user_updates desc
No comments:
Post a Comment