In order to tune-up your Dynamics NAV database performances, I've always written that working on indexes is one of the first steps and using the correct index will permit you to speed up lots of operations.
But what about rarely-used indexes? Dynamics NAV comes with lots of indexes on the standard tables, but many of them are not often used. Also on custom tables I often see that developers place lots of indexes that are unuseful.
Because the cost of these indexes is incurred during maintenance (e.g. insert, update, and delete operations), the write costs of rarely-used indexes may outweigh the benefits.
So, how you can detect them?
A good way to monitor your Dynamics NAV database (but in general your SQL Server 2005 database) is to run this T-SQL script:
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,
sys.indexes i
where database_id = @dbid
and objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by (user_seeks + user_scans + user_lookups + user_updates) asc
Indexes with a little value of these parameters are indexes that can be deleted (no benefits by having them).