Friday, August 5, 2011

SQL Server : Get Index usage report

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

No comments:

Post a Comment