Script to identify indexes with heavy writes and low reads
Posted by decipherinfosys on June 13, 2008
Here is a script for SQL Server to help identify the indexes that have heavy writes done against them but few reads:
select top 100
object_name(s.object_id) as object_name,
i.name as index_name,
(user_seeks + user_scans + user_lookups) as reads,
user_updates as writes
from sys.dm_db_index_usage_stats as s
inner join sys.indexes as i
on s.object_id = i.object_id
and i.index_id = s.index_id
where objectproperty(s.object_id,’IsUserTable’ ) = 1
and s.database_id = db_id()
and (user_seeks + user_scans + user_lookups) < 100
order by writes desc, reads asc
So, why would we be interested in the output of this query? This query can help us identify the indexes that “might” not be needed in that database environment in which it is being run. And why do we have the double quotes around might? There are a couple of reasons so that if one is not familiar with the DMV, one does not become trigger happy and drop those indexes without analysis…here is what you should know before coming to a conclusion:
1) As you know, these values get re-set everytime the instance is re-started so these values are only meaningful if you have been running the instance for quite sometime.
2) The data is useful only if you have been collecting this in an instance with meaningful number of users and with a good code coverage – so for example, if you created an index which is used specifically for a report that is run every quarter and say you had re-started the instance 2 months back, the reads will be 0 against that index and the heavy writes might indicate to you that it is a redundant index to have since we only incur costs for the writes and are not using it anywhere for the reads. However, that is not true since it is used for those reports that the VPs want every quarter. So, evaluate each entry against your business rules and then make a decision. In this particular case, if you see that it is becoming an issue, you can decide to drop the index and create it just before that scheduled quarterly report.
3) You need to understand what these reads and writes mean. Write operations are essentially the Insert, updates or deletes operations that are done against the table and the read operations are selects, updates, deletes and at times insert if the insert is written with insert into … select from clause and has a not exists clause on that table…also, if there are referential integrity constraints on that table, the insert operation has to check (do a read) to validate the values.