duplicate indexes , need opinion on dropping unwanted indexes.

Question

hi all,
We really have bad database design for one of the table, table size is around 100G and index sizes are around 200G. I ran a query to find duplicate indexes and found following indexes list. The index that can be dropped ive marked them with the arrow, if anyone has a suggestion on dropping any other index that will be great. Another question is if i ve one index on column1 and another one on column 1 and column 2 then can i drop first index thats solely on column1 as this column is covered by 2nd index.

Answer 1

Indexes should support queries. The simplest thing to do is to remove all the unused indexes and then add back specific ones if you find a performance issue.

Having index size greater than the data in the table is not really an issue to be solved. If you have lots of indexes that is very possible.

I suggest you read this:
https://www.sqlshack.com/how-to-identify-and-monitor-unused-indexes-in-sql-server/

Answer 2

Thanks Tom,

My other question is.

1. if you compare package_req_id, search_type_code index with package_req_id, search_type_code, order_date_time do these indexes make case of duplicate index? so that i can only keep second one as it includes columns from first one too.
2. how can i check the impact of dropping these indexes other than testing in lower environments.

Answer 3

if anyone has a suggestion on dropping any other index that will be great.

Supposedly, you know more about that table than we do. It's difficult to suggest about indexing on a table I know nothing about.

Another question is if i ve one index on column1 and another one on column 1 and column 2 then can i drop first index thats solely on column1 as this column is covered by 2nd index.

Probably. However, say that this query is very important:

SELECT column1, COUNT(*) FROM tbl GROUP BY column1

If you drop the index on only column1, this query will run slower, as it now has to scan the composite index on (column1, column2).

But, yes, in most cases you can consider such indexes redundant. I think I noticed a few more in the list above.

how can i check the impact of dropping these indexes other than testing in lower environments.

You drop them in production, and then waits to see if someone starts screaming.

More seriously, you can dig in the plan cache to see which queries where these indexes are used, but I don't have any query canned for that. And I am not sure that it's worth the effort.

Answer 4

Thank you Erland for your response. Dropping index in production definitely sounds like a good plan :)

One more question, if i've 2 indexes on the same set of columns , but order of these columns is different, can i call it redundant and drop one of them ?
I'm talking in reference to following indexes in the screenshot.

Answer 5

Hi SQLServerBro,

if i've 2 indexes on the same set of columns , but order of these columns is different, can i call it redundant and drop one of them?

If two indexes contain the same index keys, but the order of the keys is different, they are not redundant. The difference in keys order may cause one index to be more beneficial for queries than another similar index.
Please refer to this thread and this article which might be helpful.

Best Regards,
Amelia