August 19, 2009 at 7:49 am
i have a script that list unused indexes and i do want if someone can look the script and let me know that it is a good script or not.
this script will return some rows including reads, writes and rows.
so id the reads column returns "0" value then can i go ahead and delete that index since that has "0" reads?
--Unused indexes
declare @dbid int
select @dbid = db_id()
select object_name(s.object_id) as ObjName
, i.name as IndName
, i.index_id
, user_seeks + user_scans + user_lookups as reads
, user_updates as writes
, sum(p.rows) as rows
from sys.dm_db_index_usage_stats s join sys.indexes i on s.object_id = i.object_id and i.index_id = s.index_id
join sys.partitions p on s.object_id = p.object_id and p.index_id = s.index_id
where objectproperty(s.object_id,'IsUserTable') = 1 and s.index_id > 0 and s.database_id = @dbid
group by object_name(s.object_id), i.name, i.index_id, user_seeks + user_scans + user_lookups, user_updates
order by reads, writes desc
August 19, 2009 at 7:59 am
Please note that index_usage_stats only contains info since the last time the SQL Service was started. If an index is, for example, only used at month end and the server has been restarted since then, it will show as 'unused'
Also note that only indexes that have been used for something (read or update) will appear in this DMV. So if an index has not been read or updated since the last time the server was started, your query will not return it at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2009 at 11:32 am
so deleting indexes that have reads 0 is a good idea?
August 19, 2009 at 11:41 am
Sometimes.
Other times, no.
You could have 0 use because the index hasn't been used since the service was started, but that doesn't mean it won't be used a dozen times in the next five minutes. It depends on what the index is for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 19, 2009 at 2:01 pm
espanolanthony (8/19/2009)
so deleting indexes that have reads 0 is a good idea?
Not without doing some additional investigation to confirm that the index is not used anywhere. You need data over a representative period of time to make the drop/keep decision (or you need to test all queries against the table that has the index).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply