how to determine what indexes are being used.

I have inherited a few tables with muliple indexes, is there a way to
determine which indexes are actually being used? Both in 2000 and 2005
versions, either by some system view, tracing (what event to monitor), etc.
ThanksHi Tom,
Check this out:-
http://blogs.msdn.com/sqlcat/archiv.../12/502735.aspx
Manu
"Tom" wrote:

> I have inherited a few tables with muliple indexes, is there a way to
> determine which indexes are actually being used? Both in 2000 and 2005
> versions, either by some system view, tracing (what event to monitor), etc
.
> Thanks|||Regarding SQL Server 2000 you can make use of Index tuning wizard and provid
e
it a good workload file(saved using profiler when full data load was here on
server) and check its recommendations.
Manu
"Tom" wrote:

> I have inherited a few tables with muliple indexes, is there a way to
> determine which indexes are actually being used? Both in 2000 and 2005
> versions, either by some system view, tracing (what event to monitor), etc
.
> Thanks|||I use this on 2005. It is almost easier to upgrade to 2005 if you are on
2000 and want to do this.
declare @.dbid int
select @.dbid = db_id()
select objectname=object_name(s.object_id)
, indexname=i.name
, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes = user_updates
, i2.rowcnt
from sys.dm_db_index_usage_stats s, sys.indexes i , sysindexes i2
where objectproperty(s.object_id,'IsUserTable') = 1
and s.object_id = i.object_id
and i.index_id = s.index_id
and s.object_id = i2.id
and i2.indid = s.index_id
and s.database_id = @.dbid
order by rowcnt desc, reads
Jason Massie
http://statisticsio.com
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:127E8737-4123-4184-9A9F-FB7376640344@.microsoft.com...
>I have inherited a few tables with muliple indexes, is there a way to
> determine which indexes are actually being used? Both in 2000 and 2005
> versions, either by some system view, tracing (what event to monitor),
> etc.
> Thanks