June 19, 2009 at 2:03 am
I have this query which i got by googling:
select
iv.table_name,
i.name as index_name,
iv.seeks + iv.scans + iv.lookups as total_accesses,
iv.seeks,
iv.scans,
iv.lookups,
iv. UPDATES
from
(select
i.object_id,
object_name(i.object_id) as table_name,
i.index_id,
sum(i.user_seeks) as seeks,
sum(i.user_scans) as scans,
sum(i.user_lookups) as lookups,
sum(i.USER_UPDATES) as updates
from
sys.tables t
inner join sys.dm_db_index_usage_stats i
on t.object_id = i.object_id
where i.user_seeks + i.user_scans + i.user_lookups = 0
group by
i.object_id,
i.index_id) as iv
inner join sys.indexes i
on iv.object_id = i.object_id
and iv.index_id = i.index_id
order by total_accesses asc
I ran this script i am seeing updates when there hasn't been any seeks, scans, lookups.. I am a bit confused. Can someone help. Thanks.
June 19, 2009 at 2:15 am
Hello,
To quote BOL "The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view“.
i.e. this is normal because SQL Server needs to maintain the Index, even if no one is reading from the Index.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 19, 2009 at 2:20 am
Thank you for ur reply John
Does this mean that i can get rid of those indexes where there hasn't been any seeks, scans or lookups,,eventhough there has been updates. What is recommended??
June 19, 2009 at 2:25 am
Hello again,
(Sort of) yes - you can consider getting rid of them, but be careful because the counters are reset every time SQL Server is restarted. If that happens very infrequently in your environment then you should have a good picture of which indexes are really being used.
Take a quick look at the BOL Topic “sys.dm_db_index_usage_stats”.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 19, 2009 at 3:21 am
and also make sure you do not drop indexes from system tables / views .
Always run this query on the objects on which you want to drop indexes :
select (object_name(object_id)) from sys.partitions where object_id=
HTH
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply