Are your indexes being used?

Too many indexes are just as bad as too few.  The ones we create really should read the data quickly, with as little I/O as possible.  I have mentioned before how much I love the DMV's -- Dynamic Management Views  -- introduced in v2005, and expanded upon in v2008.  You can use the sys.dm_db_index_usage_stats DMV to review your user seeks, scans and lookups.  Very helpful to see which indices are being used, and which ones aren't. 

This is a very good overview from BOL: 
    http://technet.microsoft.com/en-us/library/ms188917(v=sql.105).aspx

This is example script uses the sys.dm_db_index_usage_stats DMV, along with sys.objects and sys.indexes, to report which indexes have not been used:

  SELECT   
      OBJECTNAME = OBJECT_NAME(i.OBJECT_ID),
      INDEXNAME = i.NAME,
      i.INDEX_ID
  FROM     
      SYS.INDEXES I INNER JOIN SYS.OBJECTS o

        ON i.OBJECT_ID = o.OBJECT_ID

  WHERE 

      OBJECTPROPERTY(o.OBJECT_ID, 'IsUserTable') = 1

      AND i.INDEX_ID NOT IN (

            SELECT s.INDEX_ID

            FROM SYS.DM_DB_INDEX_USAGE_STATS s

            WHERE s.OBJECT_ID = i.OBJECT_ID

            AND i.INDEX_ID = s.INDEX_ID

       )

  ORDER BY

      OBJECTNAME,

      i.INDEX_ID,

      INDEXNAME ASC

 

This example script lists each index used within a query execution, ordering by those that have been scanned the most.  Take a look at the seeks, scans, lookups and user reads/writes.  This is very helpful output when you are trying to determine which indices are needed, and which potentially could be removed. 

  SELECT
      OBJECT_NAME(ius.[object_id], ius.database_id) [ObjectName],
      ius.index_id,
      ius.user_seeks,
      ius.user_scans,
      ius.user_lookups,
      ius.user_seeks + ius.user_scans + ius.user_lookups [UserReads],
      ius.user_updates [UserWrites],
      ius.last_user_scan,
      ius.last_user_update
  FROM
      sys.dm_db_index_usage_stats ius
  WHERE 
      ius.database_id > 4     –- TO EXCLUDE SYSTEM TABLES
      AND OBJECTPROPERTY(ius.OBJECT_ID, 'IsUserTable') = 1
      AND ius.index_id > 0  -- TO EXCLUDE HEAPS
  ORDER BY
      ius.user_scans DESC


Please remember, collections from the DMVs are dynamic.  They will be reset any time your SQL Server service is restarted.  I often use this DMV as I am reviewing my index strategies.  Because it is dynamic, I also find it useful to schedule a collection from this DMV on a recurring basis. Maybe weekly, or monthly... just depending upon the activity you are reviewing, and how many times the index in question may be executed.  This provides a nice set of statistics to measure.  Again, very helpful for troubleshooting performance, and reviewing your index strategies.