It is day 11 of 30, of a DMV a Day for the month of April 2010. The DMV for Day 11 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.
You can find lots of information from sys.dm_db_index_usage_stats, but I will just show one query today. This particular DMV works with SQL Server 2005, 2008, and 2008 R2. It requires VIEW SERVER STATE permission.
-- List unused indexes SELECT OBJECT_NAME(i.[object_id]) AS [Table Name], i.name FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id] WHERE i.index_id NOT IN (SELECT s.index_id FROM sys.dm_db_index_usage_stats AS s WHERE s.[object_id] = i.[object_id] AND i.index_id = s.index_id AND database_id = DB_ID()) AND o.[type] = 'U' ORDER BY OBJECT_NAME(i.[object_id]) ASC;
This query uses sys.indexes and sys.objects to find tables and indexes in the current database that do not show up in sys.dm_db_index_usage_stats. This means that these indexes have no reads or writes since SQL Server was last started (or since the current database was closed or detached, whichever is shorter). If SQL Server has been running long enough that you have complete, representative workload, then there is a good chance that those indexes (and perhaps tables) are “dead”, meaning they are no longer used by your database. That means you can potentially drop them, after doing some further investigation.