The concept of index is to help retrieve data faster and hence to improve query performance. But, it is not always the case, as poorly designed index could have negative performance impact. On a high level, Non Clustered Index is like a sub table formatted in B Tree with columns defined in the index definition. Every insert,delete,update on the base table will also have to do the same operation on the non clustered index for those columns referred in the index definition. So, all this means additional work for SQL server as well as more storage. Hence, it is important to make sure that indexes are being more valuable compared to what they are costing.
Below SQL Code gives a general idea on how useful the indexes are. I am not checking for clustered index, since it exists on the base table and probably almost all tables need a clustered index.
Select DB_Name() as [DatabaseName], Schema_Name(Schema_id)+'.'+Object_Name(A.Object_ID) as [TableName], A.Name as [IndexName],A.type_desc as [IndexType],IndexSize_MB,User_updates as [IndexCostCount], user_seeks+user_scans+user_lookups as [IndexUsageCount],Last_User_Update as Last_Cost_Operation,
(Select Max(v) from (Values (last_user_lookup),(last_user_scan),(last_user_seek)) As Value(v)) as [Last_Useful_Operation]
From sys.indexes A LEFT OUTER JOIN
sys.dm_db_index_usage_stats B on A.index_id=B.index_id and A.object_id=B.object_id and B.database_ID=db_ID()
INNER JOIN sys.tables C on C.object_id=a.object_id and A.type_desc not in ('Heap','CLUSTERED')
INNER JOIN (Select (SUM(used_page_count) * 8/1024) AS IndexSize_MB,index_id,Object_id FROM sys.dm_db_partition_stats GROUP BY object_id,index_id) D ON
D.index_id=A.index_id AND D.object_id=A.object_id
order by IndexSize_MB
I am summing the user seeks,scans and lookups as to get one total value for index for usage.
Higher the IndexUsageCount compared to IndexCostCount,it generally implies that the index is useful. Of course, whether the usage and operation(seek\scan\lookup) of a particular index in a query is optimal or not cannot be decided without investigating the actual execution plan.
This information is not persisted after restart of SQL Server or database is offline.