Index related DMVs and DMFs - sys.dm_db_index_usage_stats

Finally what else sys.dm_db_index_usage_stats gives us?

Though we can drill down the usage of a particular index by examining individual columns, we can get the overall picture of usage of all indexes. One important view is indexes that are not used. The below query gives will highlight this:

-- query 12
select object_name(i.object_id) as tablename,  i.name as indexname
from sys.indexes i
       left outer join sys.dm_db_index_usage_stats s
              on s.object_id = i.object_id
                     and s.index_id = i.index_id
                     and s.database_id = db_id()
where objectproperty(i.object_id, 'IsUserTable') = 1
       and i.index_id > 0 -- 0 indicates the heap       
and s.object_id is null

This returns all the indexes that have not been used for any requests by users or the system from the time SQL Server is started. If the service has been running for months without any interruptions and the usage of the indexes returned by the query is very low we can either remove them from the system or modify them after analyzing them. One thing needs to be made checked before removing - there may be some indexes that have been created for read-only tables (so, no updates) and they are used by some scheduled (eg. quarterly) jobs. If one of the indexes returned from above query is used by such a job, removing the index from the system will be badly affected to the job. Therefore check the purpose of the indexes before removing them.

This query returns the list of indexes ordered by the usage. Indexes that are listed at the top of the result-set may not be as beneficial as indexes listed at the bottom.

-- query 13
select object_name(i.object_id) as tablename,  i.name as indexname,
       s.user_seeks + s.user_scans + s.user_lookups + s.user_updates as usage
from sys.indexes i
       inner join sys.dm_db_index_usage_stats s
              on s.object_id = i.object_id
                     and s.index_id = i.index_id
                     and s.database_id = db_id()
where objectproperty(i.object_id, 'IsUserTable') = 1
       and i.index_id > 0
order by usage

There is a possibility of being misguided by this query too. We saw that after inserting records into the table, the value of the user_updates became 1000 but there were no values for other columns. That type of indexes may be listed at the bottom of the output, hence it leads us to assume the index is heavily used. Because of that, it would be better to analyze the bottom part of result-set separately by examining individual columns such as user_scans and user_seeks.