Unused indexes on a database, its good and bad
You may be aware that the indexes on a SQL Server database take up space, so in case of unused indexes its obvious that they waste storage. It is easy to find the indexes using SP_HELPINDEX statement against a table, so to identify the unused indexes what is the way out.
In the previous versions of SQL Server Index Tuning wizard is only way out, but capturing the trace using PROFILER or server side trace and running on a production server is not advisable if the performance is degrading on timely basis. There are few third party tools out there to get you such report of unused indexes with a SQL workflow.
Having indexes is not sufficient and due to the fashion of high INSERT, UPDATE and DELETE activity they tend to fragmented heavily and to address this problem you have to reindex to reduce the fragmentation, otherwise it requires much CPU usage for each DML statement with additional performance penalty on tables with many indexes that experience.
Within SQL 2005 version DMV aka DBA-friend focused on getting such information with these views, be aware that they will only capture information since the last time SQL Server was restarted. If you regularly restart SQL Server (hopefully you don't!), these probably will not be much help for you. So this is for production server issues investigations only.
So to get the result on unused indexes mainly DMV sys.dm_db_index_usage_stats contains counts of different types of index operations and the time each type of operation was last performed. Also using sys.dm_db_missing_index_details, and sys.dm_db_missing_index_group_stats will get more information.
select object_name(i.object_id), i.name
from sys.indexes i, sys.objects o
where i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = db_id('Northwind') )
and o.type = 'U'
and o.object_id = i.object_id
order by object_name(i.object_id) asc
go
The only good option is whenever relevant query is executed then optimizer will select that index or if you mention hint to include will be useful, provided you don't have database space issues in this case.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.