Simple but accurate following script will give you list of all the indexes in the database which are unused. If indexes are not used they should be dropped as Indexes reduces the performance for INSERT/UPDATE statement. Indexes are only useful when used with SELECT statement.

Script to find unused Indexes.

USE AdventureWorks
GO
DECLARE @dbid INT
SELECT @dbid = DB_ID ( DB_NAME ())
SELECT OBJECTNAME = OBJECT_NAME ( I. OBJECT_ID ),
INDEXNAME = I.NAME ,
I.INDEX_ID
FROM SYS.INDEXES?I
JOIN SYS.OBJECTS?O
ON I. OBJECT_ID = O. OBJECT_ID
WHERE OBJECTPROPERTY ( O. OBJECT_ID , 'IsUserTable' ) = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS?S
WHERE S. OBJECT_ID = I. OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid )
ORDER?BY OBJECTNAME ,
I.INDEX_ID ,
INDEXNAME ASC
GO


Note: This is only for SQL Server 2005, it will not run for previous version of SQL Server.

SQL Server 查找未使用的索引