Sample script that lists any index not used since the last time SQL Server was recycled. Unused indexes will not appear in sys.dm_db_index_usage_stats. This script, contributed by Microsoft's Tom Davidson, requires SQL Server 2005.

Visual Basic
Edit|Remove
use Northwind
go
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

---- note: Period index x2 is on OrderDate
select * from Period
where OrderDate = getdate()
--- note: Perid index x1 is on DateID
select * from Period
where DateID = 20001010
go
---- after executing the above select statement from the Period table, redisplay the indexes NOT used
---- you will discover the x1 and x2 indexes are no longer listed as NOT used.