You can use the following query to find out which indexes are used and which are only maintained:
select object_name(s.object_id) as [object name],
i.name as index_name,
user_seeks,
user_scans,
user_lookups,
user_updates
from sys.dm_db_index_usage_stats s
join sys.indexes i
on i.object_id = s.object_id
and i.index_id = s.index_id
where objectproperty(s.object_id,'isusertable') = 1
and s.database_id = db_id();
Every individual seek, scan, lookup, or update on the specified index
by one query execution is counted as a use of that index and
increments the corresponding counter in this view. Information is
reported both for operations caused by user-submitted queries, and for
operations caused by internally generated queries, such as scans for
gathering statistics.
The user_updates counter indicates the level of maintenance on the
index caused by insert, update, or delete operations on the underlying
table or view. You can use this view to determine which indexes are
used only lightly by your applications. You can also use the view to
determine which indexes are incurring maintenance overhead. You may
want to consider dropping indexes that incur maintenance overhead, but
are not used for queries, or are only infrequently used for queries.
The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is
detached or is shut down (for example, because AUTO_CLOSE is set to
ON), all rows associated with the database are removed.
When an index is used, a row is added to sys.dm_db_index_usage_stats
if a row does not already exist for the index. When the row is added,
its counters are initially set to zero.
Here sys.dm_db_index_usage_stats (Transact-SQL) you can find more explanation.
Remember that some indexes are there in your database to enforce uniqueness
so they should not be touched at all, here is how you find them:
select indexname = i.name,
schemaname = schema_name(schema_id),
tablename = object_name(o.object_id),
is_primary_key,
is_unique
from sys.indexes i
join sys.objects o
on i.object_id = o.object_id
where (is_primary_key = 1
or is_unique = 1)
and objectproperty(o.object_id,'isusertable') = 1
order by schemaname,
tablename,
indexname;
Finally, your db can have duplicate indexes as there is no control over it, here you can find the code that finds them:
Removing duplicate indexes by Kimberly Tripp
