In this post, I provide you one script to find total usage of your database indexes in SQL Server.
This is a very useful script for DBA to find out the usage of indexes and ït can also take a decision to remove unused indexes.
Using this script you can find out the total number of seek and scan taken by different indexes.
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
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.