Observation & Removing Duplicate index

Duplicate Indexes:

When i had to do maintenance of SQL Databases on Duplicate indexes i will follow these steps before recommending dropping of duplicate index:-

1. Finding duplicate indexes 
2. Looking into index usage stats for duplicate indexes.

As per my observations sql recommends new index for every new query, even if these columns are available inside index but with different order.

I have following observations about duplicate indexes:-

---

Observation 1:

• In the following screenshot, as two duplicate index found and in index usage stats one of index only have updates(bad index or unused index), so it is dropped and saved 380MB space.

• Index usage stats:

                

• Duplicate index:

                

---

Observation:2

• In following screenshot, showing two duplicate indexes.Whereas One index contains only one column and another index contains three columns.
• Before recommended to drop index i had checked into index usage, that which index is effective. Picture, showing total 6 indexes available on this object where one index having only one column, if will be deleted which has less seek ,then will optimizer uses this another index?

• Index usage stats:

              

• Duplicate index:

                

• If we drop one column index and If users using the only one column in where condition, It will suggest to create new index!

• Note: Don't drop this index.

• Finding duplicate indexes is good way to help Sql server optimizer to and also save some disk space. But if the index has different columns in Include that means it is not fully duplicated
• Don't not rely on what Sql server recommends to create index but looking into the execution plan and understand what is going on helps.

---

Observation 3:

• What about these three duplicate index
• Will you suggest to drop all these three index and suggest to create only one index, with these three columns only. Look at picture their seek ratios are high.

• Index usage stats:

              

• Duplicate index:

               

Note: Don't drop as it is not considered duplicate.

Index having different order are not duplicate depending on query. Some query uses index written in particular order only.

---