Cacher is the code snippet organizer for pro developers

/*============================================================================  
 Original Blog Link:   http://blog.sqlauthority.com/2011/01/04/sql   -server-2008-unused-index-script-download/  
 (C) Pinal Dave   http://blog.SQLAuthority.com  
 Like us on Facebook:   http://facebook.com/SQLAuth  
 Follow us ON Twitter:   http://twitter.com/pinaldave  
 ============================================================================  


 Performance Tuning is quite interesting and Index plays a vital role in it.  
 A proper index can improve the performance AND a bad index can hamper the performance.  

 Here is the script from my script bank which I use to identify unused indexes on any database.  

 Please note,  
 IF you should NOT DROP ALL the unused indexes this script suggest.  
 This is just for guidance. You should not create more than 5-10 indexes per table.  
 Additionally, this script sometime does NOT give accurate information so USE your common sense.  

 Any way, the scripts is good starting point.  
 You should pay attention TO USER Scan, USER Lookup AND USER UPDATE WHEN you ARE going TO DROP index.  
 The generic understanding is if this values are all high and User Seek is low, the index needs tuning.  
 The index DROP script IS also provided IN the LAST COLUMN.  

 */  

 -- Unused Index Script  
 -- Original Author: Pinal Dave (C) 2011  
 --   http://blog.sqlauthority.com  

 SELECT   TOP 25  
    o.   NAME    AS   ObjectName  
  , i.   NAME    AS   IndexName  
  , i.index_id   AS   IndexID    
  , dm_ius.user_seeks   AS   UserSeek  
  , dm_ius.user_scans   AS   UserScans  
  , dm_ius.user_lookups   AS   UserLookups  
  , dm_ius.user_updates   AS   UserUpdates  
  , p.TableRows  
  ,   'DROP INDEX '    +   QUOTENAME(i.   NAME   )  
     +    ' ON '    +   QUOTENAME(s.   NAME   )   +    '.'    +   QUOTENAME(OBJECT_NAME(dm_ius.object_id))   AS    'drop statement'  
 FROM     sys.dm_db_index_usage_stats dm_ius    
 INNER    JOIN    sys.indexes i   ON   i.index_id   =   dm_ius.index_id   AND   dm_ius.object_id   =   i.object_id     
 INNER    JOIN    sys.objects o   ON   dm_ius.object_id   =   o.object_id  
 INNER    JOIN    sys.schemas s   ON   o.schema_id   =   s.schema_id  
 INNER    JOIN   (SELECT   SUM   (p.   ROWS   ) TableRows, p.index_id, p.object_id  
     FROM sys.partitions p   GROUP   BY p.index_id, p.object_id) p  
      ON   p.index_id   =   dm_ius.index_id   AND   dm_ius.object_id   =   p.object_id  
 WHERE   OBJECTPROPERTY(dm_ius.object_id,   'IsUserTable'   )   =   1  
     AND   dm_ius.database_id   =   DB_ID()     
     AND   i.type_desc   =    'nonclustered'  
     AND   i.is_primary_key   =   0  
     AND   i.is_unique_constraint   =   0  
 ORDER BY   (dm_ius.user_seeks   +   dm_ius.user_scans   +   dm_ius.user_lookups)   ASC  
 GO