Sql-server – Dropping unused indexes – Assessing the unexpected dangers

I don't know of any exposed or documented way that SQL Server keeps track of this as a separate statistic (no pun intended). But if you monitor your server for expensive queries (long duration, high CPU, high I/O etc) and collect the plans for those queries, you may be able to derive some of that information from the plans you've collected - but keep in mind that not all statistics will be represented (some details on that here). If you know of queries off-hand that are slow and you suspect the problem may be due to specific statistics or missing statistics, you can generate an actual execution plan for that query and inspect the plan - you'll often find evidence such as a missing statistics warning or a huge discrepancy between estimated and actual rows (and the latter is why generating an estimated plan only is kind of worthless).

SentryOne Performance Advisor product is one way to help with the collection of high-impact queries ("Top SQL") and the execution plans associated with them at runtime, since they are collected and saved for you over time. Also Plan Explorer is a free tool that can generate plans or open existing plans you've collected, often highlighting some of these issues much easier than viewing the graphical showplan in Management Studio or parsing the XML yourself.*

As long as you are sure the server was up for that entire time, and that nobody cleared out DMV stats inadvertently. This can happen if the database is detached + re-attached / restored / offline + online / auto-close + online, or if the index has been explicitly dropped / re-created (the DMV is not affected by disable / rebuild / reorganize, except in the case of SQL Server 2012, where rebuild currently clears the stats - I suspect that will be fixed - thanks @MartinSmith).

You should expect that if a DML activity used the index in a read capacity for some reason (e.g. to perform an update on some other table), this would register as a read activity, not a write. All of the writes you see are index maintenance.

As an aside, here is a slightly more efficient way to write this query. I removed the twice-referenced correlated subquery and dropped the unnecessary joins to sys.objects and sys.schemas. I also fixed a few minor syntax things, such as prefixing all columns with aliases where appropriate, placing reserved words in square brackets, and removing AS 'column alias' syntax. I found it confusing that half way through the SELECT list you changed from alias = expression syntax to expression AS alias syntax - you should pick one and be consistent IMHO. I changed these both for majority within the query and also according to my personal preference. :-)

SELECT name = OBJECT_NAME(s.[object_id])
    , indexname = i.name
    , i.index_id   
    , reads = s.user_seeks + s.user_scans + s.user_lookups   
    , writes = s.user_updates   
    , g.[rows]
    , reads_per_write = CASE
        WHEN s.user_updates < 1 THEN 100.0
        ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
      END
    , [drop statement] = 'DROP INDEX ' + QUOTENAME(i.name) 
      + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(s.[object_id])) 
      + '.'    + QUOTENAME(OBJECT_NAME(s.[object_id]))
    FROM sys.dm_db_index_usage_stats AS s  
    INNER JOIN sys.indexes AS i 
      ON i.index_id = s.index_id 
      AND s.object_id = i.object_id   
    INNER JOIN 
    (
      SELECT [object_id], index_id, [rows] = SUM([rows]) 
        FROM sys.partitions GROUP BY [object_id], index_id
        HAVING SUM([rows]) > 2000
    ) AS g
      ON i.[object_id] = g.[object_id] 
      AND i.index_id = g.index_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
    ORDER BY name, reads;