This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to find the total usage of your database indexes in SQL Server.
This is a handy script for DBA for finding the usage of indexes so that they can decide to remove the unused indexes.
Using below script, you can see the total number of seek and scan taken by different indexes.
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT OBJECT_NAME(DDIUS.OBJECT_ID) AS [OBJECT NAME] ,I.[NAME] AS IndexName ,USER_SEEKS AS TotalUserSeek ,USER_SCANS AS TotalUserScan ,USER_LOOKUPS AS TotalUserLookup ,USER_UPDATES AS TotalUserUpdate FROM SYS.DM_DB_INDEX_USAGE_STATS AS DDIUS INNER JOIN SYS.INDEXES AS I ON I.OBJECT_ID = DDIUS.OBJECT_ID AND I.INDEX_ID = DDIUS.INDEX_ID WHERE OBJECTPROPERTY(DDIUS.OBJECT_ID,'IsUserTable') = 1 |