Determining that Indexes on a table are unused

Question

I’ve been running this script to try to find extraneous indexes

select o.name as TableName, i.name as IndexName, p.reserved_page_count * 8.0 / 1024 as     SpaceInMB, s.*
from     sys.dm_db_index_usage_stats s
inner join sys.objects o on s.object_id = o.object_id
inner join sys.indexes i on i.index_id = s.index_id and i.object_id = o.object_id
inner join sys.dm_db_partition_stats p on i.index_id = p.index_id and o.object_id =      p.object_id
where o.name = ‘TableName’

I know that when last_user_seek/scan/lookup are all null, that no users have used the index since last restart. But I’m wondering what system_scans/lookups/seeks …are? Because on a certain table I found 5 that had no user activity, but one had system activity 10 days ago. Do anyone have any insight on what system scans/seeks/lookups might be? These tables seem really over-indexed and I’d like to trim the fat.

Answer 1

Index maintenance (rebuild/reorganize) and DBCC CHECKDB activity most likely, possibly statistics updates. Any scheduled maintenance configured?

If there is no user access, bin them. Just be mindful of the time frame over which you decide they are no longer used. Are there any weekly or monthly reporting tasks for instance?

While you're looking, dig around for duplicate indexes as well.

Edit: regarding SSC link

From a quick scan through the thread, looks like the SSC folk had similar thoughts. They are however taking a more cautious stance on the possible "occasional" use of these indexes, taking the position that someone put them there for a reason, a perfectly reasonable argument. The counter argument is that all too often it's the exact opposite, someone put them there because they thought it was the right thing to do but through a lack of understanding or lack of testing, it wasn't.

I've brought a couple of systems back from the brink by doing nothing other than dropping unused and duplicated indexes. Over indexing can cause chaos.

It's your system, you need to understand and weigh the risks of leaving these indexes in place or dropping them. If you decide to go ahead with the drop, document what you do, why you're doing it, script the indexes and publish to all interested parties.

Answer 2

Glenn Berry has written some great scripts to help you find your missing indexes. I suggest using his scripts which take some of the guess work out of the task for you. Those scripts aren't just looking for null or 0 user lookups/scans/seeks but also looking for indexes that have a large skew between read activity and write activity, possibly still resulting in better overall performance by dropping. I'd check his scripts out - you can get a start on this post of his.

I wouldn't be worried about the system activity. That isn't something that will get worse if you remove the indexes, in fact it could be activity that only happens on that index because it exists. The main thing you care about is user read activity and user write activity and balancing that out.

Answer 3

Remember that indexes also provide useful information to the Query Optimizer, even of they're not used. I've done quite a bit of stuff on the impact of Uniqueness, for example. If you remove a unique index because it has no seeks or scans, you could still adversely affect performance.

Answer 4

In addition to what everyone else has stated, indexes against referenced FK columns may never show seeks or scans but are used under the covers.