Determining that Indexes on a table are unused

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.

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.

StackExchange Database Administrators Q#7906, answer score: 10