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.
