Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

select as TableName, 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 = ‘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.

share|improve this question
I posted the same question on sqlservercentral and got responses there too. The link to the topic is: – Aushin Nov 15 '11 at 15:40
Related: – Nick Chammas Nov 16 '11 at 2:29
@Aushin the link you posted above is leading to an extremely messed up discussion full of feelings and side-quests nobody really wants to follow. – Magier Oct 1 '15 at 11:20
up vote 10 down vote accepted

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.

share|improve this answer
+1 on this - I am sure it is those activities Mark mentioned. Nothing that should concern you - expanded on that in an additional answer below. – Mike Walsh Nov 15 '11 at 15:33
Thanks for this. I also had a thread on sqlservercentral about this. I will post the link to what they said in my original question. – Aushin Nov 15 '11 at 15:40

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.

share|improve this answer
Thanks for the reply! – Aushin Nov 15 '11 at 15:40

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.

share|improve this answer
Great point and I believe that Glenn's scripts look for unique constraints. If not his, perhaps a different set, I'll have to research that. – Mike Walsh Nov 16 '11 at 14:58

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.