Indexes have large effect on each database. They can improve queries performance dramatically, but too many of them can cause performance degradation and overhead when changing data. This tip contains a script that will help you to find useful information about index usage in your database. It is compatible to SQL Server 2005 and up.

You can use this query to determine which indexes are used only lightly by your applications. You can also use the query 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 query is based on the DMV sys.dm_db_index_usage_stats. The counters in the view are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started.

Every individual seek, scan, lookup, or update on the specified index by one query or DML execution is counted as a use of that index and increments the corresponding counter in this view.
The index information is gathered both for user operations (e.g. submitted queries), and for internal operations (e.g. scans for gathering statistics).

SELECT
sys.objects.name AS object_name,
sys.indexes.name AS index_name,
case
when (is_unique = 1 and is_primary_key = 1) then 'PK UNIQUE '
when is_unique = 1  then 'UNIQUE '
else ''
end + sys.indexes.type_desc type_desc,
c.index_columns AS index_columns_key,
sys.dm_db_index_usage_stats.user_seeks,
sys.dm_db_index_usage_stats.user_scans,
sys.dm_db_index_usage_stats.user_lookups,
sys.dm_db_index_usage_stats.user_updates,
sys.dm_db_index_usage_stats.last_user_seek,
sys.dm_db_index_usage_stats.last_user_scan,
sys.dm_db_index_usage_stats.last_user_lookup,
sys.dm_db_index_usage_stats.last_user_update
FROM sys.objects
JOIN sys.indexes
ON sys.indexes.object_id=sys.objects.object_id
JOIN
(SELECT distinct
object_id,
index_id,
stuff((SELECT ','+col_name(object_id,column_id ) as 'data()'
FROM sys.index_columns t2
WHERE t1.object_id =t2.object_id
and t1.index_id = t2.index_id
FOR XML PATH ('')),1,1,'') as 'index_columns'
FROM sys.index_columns t1
) c
ON c.index_id = sys.indexes.index_id
AND c.object_id = sys.indexes.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats
ON sys.indexes.index_id=sys.dm_db_index_usage_stats.index_id
AND sys.indexes.object_id=sys.dm_db_index_usage_stats.object_id
AND (
(sys.dm_db_index_usage_stats.user_seeks is null)
OR  (
( sys.dm_db_index_usage_stats.user_seeks < 100 )
AND ( sys.dm_db_index_usage_stats.user_scans < 100 )
AND ( sys.dm_db_index_usage_stats.user_lookups < 100 )
)
)
WHERE sys.objects.type='u'                 --Only UserTables will be selected
AND sys.indexes.type_desc  <> 'HEAP'       --Only indexes will appear, not HEAP tables
--AND sys.objects.name = ''                --In case you would like to monitor only a specific table, put the table name
ORDER BY 1

Information about the main columns in this DMV:

User Seeks: the number of times the index has been used by a user query in a seek operation (one specific row)

User Scans: the number of times the index has been used by scanning the leaf pages of the index for data

User Lookup: for clustered indexes only, this is the number of times the index has been used in a “bookmark lookup” to fetch the full row. This is because non-clustered indexes use the clustered indexes key as the pointer to the base row