The DMV for Day 6 is sys.dm_db_index_usage_stats, which is described by BOL as:

Returns counts of different types of index operations and the time each type of operation was last performed. 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.

This DMV tells you how much your indexes are being used, for both reads and writes. This query works with SQL Server 2005, 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

-- Possible Bad NC Indexes (writes > reads)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;

This particular query filters by the current database, and only includes non-clustered indexes. It can help you decide whether a particular index might be costing more to maintain than the benefit you are receiving from having it in place. When I run this query, I look for any indexes that have large numbers of writes with zero reads. Any index that falls into that category is a pretty good candidate for deletion (after some further investigation). You want to make sure that your SQL Server instance has been running long enough that you have your complete, typical workload included. Don’t forget about periodic, reporting workloads that might not show up in your day-to-day workload.

Next, I look at rows where there are large number of writes and a small number of reads. Dropping these indexes will be more of a judgment call, depending on the table and how familiar you are with your workload. Finding the correct balance between too many indexes and too few indexes, and having the “proper” set of indexes in place is extremely important for a DBA that wants to get the best performance from SQL Server.