Sometimes the number of indexes on your database may be huge in number and it would be a teadous task to check which are indexes being used and which are not. So here is one of the solution. I found this T-SQL Statement useful which may ease out your task.
Thanks to Search Engines.
USE [YOURDATABASENAME]
GO
SELECT O.NAME AS OBJECT_NAME, I.NAME AS INDEX_NAME,
I.TYPE_DESC, U.USER_SEEKS, U.USER_SCANS,
U.USER_LOOKUPS, U.USER_UPDATES
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
LEFT JOIN SYS.DM_DB_INDEX_USAGE_STATS U ON I.OBJECT_ID = U.OBJECT_ID
AND I.INDEX_ID = U.INDEX_ID
AND U.DATABASE_ID = DB_ID()
WHERE O.TYPE <> 'S' -- NO SYSTEM TABLES!
ORDER BY (ISNULL(U.USER_SEEKS, 0) + ISNULL(U.USER_SCANS, 0) + ISNULL(U.USER_LOOKUPS, 0) + ISNULL(U.USER_UPDATES, 0)), O.NAME, I.NAME
Posts
