
/*
Lists how often indexes are used, from least to most. Obviously these 
are tools that we need to consider using when we tune a new system, 
but we will certainly want to really watch stuff like this during early
days when a system hits production to look at trimming back indexes 
that are purely for performance that are never being used for such.  
Stats like this are useless unless you have a totally full load and 
have gone through real life situations, unlike the previous, 
“what might have helped” queries from the automatic indexing stuff. 
*/

--- rarely used indexes appear first 
declare @dbid int 
select @dbid = db_id() 
select objectname = object_name(s.object_id) 
       , s.object_id 
       , indexname = i.name 
       , i.index_id 
       , user_seeks 
       , user_scans 
       , user_lookups 
       , user_updates 
from sys.dm_db_index_usage_stats s 
           join  sys.indexes i 
               on i.object_id = s.object_id 
                    and i.index_id = s.index_id 
where database_id = @dbid 
and objectproperty(s.object_id , 'IsUserTable') = 1 
