I am looking at my database that several people have worked on. A particular table is responding slowly when queried on a certain join. I notice some of the parameters of the join are not indexed. So I look at the indexes that are on the table and there are currently 10 of them, a lot with duplication it looks to me:

NonClustered Indexes:

Index1: ManufacturerID, IsDeleted
Index2: IsDeleted, ManufacturerID
Index3: CustomerId
Index4: IsDeleted
Index5: ManufacturerId, IsDeleted, Exported
Index6: ManufacturerId
Index7: ManufacturerId, ModifiedOn
Index8: ManufacturerId, OrderGUID
Index9: SalesRepId

ClusteredIndex: OrderId

The 2 columns I do join on are RepNumber and WrittenForRepNumber.

So my confusion here is why do I have so may indexes that seem to contain the same columns?

Do I need an index for each of these as they look like some of them are geared towards specific queries?

  • 7
    Take a look at sys.dm_db_index_usage_stats. You want to keep the ones with the highest ratio of read:write. (Why do you have duplicates/redundants? You'll have to ask the person/people who created them.) – Aaron Bertrand Apr 6 '15 at 18:13
  • As far as I know it's not possible to see what fields are actually being used in indexes, so the statistics might give you a slightly wrong idea about the usage if only the first field is actually used. You can look at plan cache, but that's quite a big task, and only shows those plans that are still in cache – James Z Apr 6 '15 at 18:25
  • It sounds like you need a index consolidation project. Kim Tripp strongly states that having multiple overlapping indexes is not as good as having 1 or 2 covering indexes. See if you can consolidate. – Ali Razeghi Apr 6 '15 at 18:44
  • 1
    Yes it would, because your search condition (ManufacturerID) is the first column in the multi-column index. – Michael Green Apr 7 '15 at 5:39
  • 1
    Be aware that filtered indexes and included columns exist. This can make for more efficient, better targeted indexing strategies. – Michael Green Apr 7 '15 at 5:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.