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

Hi Tom,

Check this out:-

http://blogs.msdn.com/sqlcat/archive/2005/12/12/502735.aspx

Manu

Show quoteHide quote

Regarding SQL Server 2000 you can make use of Index tuning wizard and provide
it a good workload file(saved using profiler when full data load was here on
server) and check its recommendations.

Manu



Show quoteHide quote

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

Show quoteHide quote

Snapshot size
SQL Server better than DB2
The affinity mask specified does not match the CPU mask on this system
SQL Server Transaction Log growing
SQL Server 2005: log shipping