Understand the index usage from DMVs in Sql server : The below will retrieve the indexes performed look ups, scans, seeks by the user with the utilized dates. If we see any unused indexes, could be deleted to improve the performance for Insert,update,delete statements.
select object_name(a.object_id) TABLE_NAME, a.index_id,b.name IndexName,
b.type_desc,a.last_user_seek,a.last_user_scan,a.last_user_lookup,a.last_user_update
from sys.dm_db_index_usage_stats a inner join sys.indexes b
on a.object_id = b.object_id and a.index_id = b.index_id
where a.database_id =6 and a.object_id in (object_id('xxx'), object_id ('xxx'))
order by A.OBJECT_ID,a.index_id
select object_name(a.object_id) TABLE_NAME, a.index_id,b.name IndexName,
b.type_desc,a.last_user_seek,a.last_user_scan,a.last_user_lookup,a.last_user_update
from sys.dm_db_index_usage_stats a inner join sys.indexes b
on a.object_id = b.object_id and a.index_id = b.index_id
where a.database_id =6 and a.object_id in (object_id('xxx'), object_id ('xxx'))
order by A.OBJECT_ID,a.index_id
