SQL Server 2005 methods to find table INDEX or Statistics age for better performance
DMVs to provide state of TEMPDB during a performance tuning analysis
SQL Server indexing - when to reorganize or rebuild?
Guidelines for enabling indexes and online index operations
Unused indexes on a database, its good and bad
Right on this huge topic for a DBA taking a decision of whether a new index or modifying current index would help any gain in the performance or not is typical. For every performance if the object statistics are poor then impact of running slow is highly-possible SQL Server dynamic management views have a set of DMVs which provide excellent and detailed information for object usage. To know more whether any usefulness in adding new index then run the following query, this is find potential indexes:
select d.*, s.avg_total_user_cost , s.avg_user_impact, s.last_user_seek
,s.unique_compiles
from sys.dm_db_missing_index_group_stats s ,sys.dm_db_missing_index_groups g
,sys.dm_db_missing_index_details d
where s.group_handle = g.index_group_handle and d.index_handle = g.index_handle
order by s.avg_user_impact desc
go
The result would encompass where the index will be beneficial that will help the performance, bear in mind the DMV will return the state since the last restart of that SQL Server instance. Based on the result and timing on availability for maintenance window you must decide whether this is required or not. The usefulness of blogs are give suggestion and best follower is to test that suggestion within their environment to prove it works or not.