In one of the previous blog posts, we had covered the different types of dynamic management views that have been introduced with SQL Server 2005. One of those index related views is “sys.dm_db_index_usage_stats”. Whenever an index is used in the system, a row is added to it if it doesn’t already exist. If it exists, then the respective counter gets incremented. Every seek operation, scan operation, a look-up or an update due to a DML operation is counted and the respective counter gets incremented. Both application/end user submitted queries as well as internally generated queries (example: scans for collecting stats) increment different counters in the system. So, where does this come handy? Prior to rolling out your application(s) in production, you can conduct a thorough benchmark for your application flow and see which indexes are being used in your system i.e. which indexes are being heavily used, which are lightly used and which incur a maintenance overhead. So, if you find that there are indexes that are incurring heavy maintenance overhead and are very seldom used by queries, you might want to either drop them or re-design some of the indexes in the schema.
These counters get re-set when you re-start the service or shutdown the database or are moving the database using detach/attach. Here is a sample SQL that you can use to get the number of accesses and the total scans, seeks or look-ups against your indexes:
i.name as index_name,
iv.seeks + iv.scans + iv.lookups as total_accesses,
object_name(i.object_id) as table_name,
sum(i.user_seeks) as seeks,
sum(i.user_scans) as scans,
sum(i.user_lookups) as lookups
inner join sys.dm_db_index_usage_stats i
on t.object_id = i.object_id
i.index_id) as iv
inner join sys.indexes i
on iv.object_id = i.object_id
and iv.index_id = i.index_id
order by total_accesses desc
You can then use this SQL and get a list of tables and their indexes which are not used – use the EXCEPT clause or just a NOT IN or a NOT EXISTS to get you that list.