I am trying to find any stored procedures or tables that have not been used in the last 2 months or so. Can anyone recommend me a good way to do this?
SQL 2005 Enterprise Edition SP 2
Thanks in advance.
Kay
There is a dynamic management view that will give you what you want since the last restart:
select object_name(i.object_id) as object_name
, case when i.is_unique = 1 then 'UNIQUE ' else '' end + i.type_desc as index_type
, i.object_id
, i.name as index_name
, i.index_id
, coalesce(user_seeks,0) as user_seeks
, coalesce(user_scans,0) as user_scans
, coalesce(user_lookups,0) as user_lookups
, coalesce(user_updates,0) as user_updates
from sys.indexes i
left outer join sys.dm_db_index_usage_stats s
on i.object_id = s.object_id
and i.index_id = s.index_id
and database_id = db_id()
where objectproperty(i.object_id , 'IsUserTable') = 1
and i.index_id in (1,0) --clustered index or heap
order by user_seeks + user_scans + user_lookups + user_updates asc
It tells how many times the index has been accessed in queries, changed, etc.
No comments:
Post a Comment