This is a script to to identify the unused indexes within a database. The column 'ReadsPerWrite' is an important field to consider.

I am not the original author of this script. It ended up in my personal collection from some source (may be from MSDN or from another online resource). I have made slight changes to the original script

WITH cte AS (
    SELECT 
    	'['+ c.name + '].[' + o.name + ']' AS TableName,
    	i.name AS IndexName,
    	i.index_id AS IndexID,   
    	user_seeks + user_scans + user_lookups AS Reads,
    	user_updates AS Writes,
    	(
    		SELECT SUM(p.rows) 
    		FROM sys.partitions p 
    		WHERE p.index_id = s.index_id 
    			AND s.object_id = p.object_id
    	) AS TotalRows,
    	CASE
    		WHEN s.user_updates < 1 THEN 100
    		ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) 
    			/ s.user_updates
    	END AS ReadsPerWrite,
    	'DROP INDEX ' + QUOTENAME(i.name) 
    		+ ' ON ' + QUOTENAME(c.name) 
    		+ '.' + QUOTENAME(OBJECT_NAME(s.object_id)) 
    		AS 'DropSQL'
    FROM sys.dm_db_index_usage_stats s  
    INNER JOIN sys.indexes i ON i.index_id = s.index_id 
    	AND s.object_id = i.object_id   
    INNER JOIN sys.objects o on s.object_id = o.object_id
    INNER JOIN sys.schemas c on o.schema_id = c.schema_id
    WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
    	AND s.database_id = DB_ID()   
    	AND i.type_desc = 'nonclustered'
    	AND i.is_primary_key = 0
    	AND i.is_unique_constraint = 0
    AND 
    (
    	SELECT SUM(p.rows) 
    	FROM sys.partitions p 
    	WHERE p.index_id = s.index_id 
    		AND s.object_id = p.object_id
    ) > 10000
)
SELECT * FROM cte
/*
TableName   Name  ID Reads   Writes Rows  ReadsPerWrite DropSQL
----------  ----- -- ------- ------ ----- ------------- -------
[dbo].[t1]  IDX_1 20 1227972 996081 78599 1.23280335635 DROP ..
[dbo].[t2]  IDX_2 21 51359   33912  78599 1.51447865062 DROP ..
[dbo].[t3]  IDX_3 22 235280  159808 78599 1.47226672006 DROP ..
*/