Redundant index key columns based on missing index suggestion

Question

I have generated missing index for all my servers and below is the suggestion from SQL server DMV and I have put that in a table . Below is just two columns out of few columns. As you can see below in index_columns which I have highlighted in yellow there is two same camp_id for instance name DB02 as index key column

My question : Since I have many servers instance and its taking time to do manually, does anyone knows how to write a query to check index_columns rows if there is redundant index key columns (like camp_id) based on the instance name column.

Below screenshot it's just got two instance name but actually I have many instance

Appreciate your help

Answer 1

First, you should not add indexes simply based on the missing index DMV. It will give you bogus duplicate indexes based your queries, and requires knowledge to properly create indexes.

Second, those indexes may include the same field, but they are not the same, If you create an index on [camp_id],[open_time] that index will likely not be used when you query for [camp_id],[submit_time].

I suggest you use a method of ranking the suggestions, and evaluating the top 10 results. Then try again in a month or two and see the top 10 again.

Answer 2

Treat missing indexes like a pretty stupid friend. This friend is very nice and keen to give you advice, and sometimes these are good advice - but far from always! :-)

Answer 3

Tom is right on the money, those indexes are definitely not redundant.

But since the missing-indexes feature is a little too keen to suggest covering indexes, there can certainly be redundant indexes like (a,b) INCLUDE(c,d,e) and (a,b) INCLUDE(c,d,e,f) .

Possibly sp_blitzindex could help you here, but I think it only works on existing indexes.

In any case, as Tom and Tibor says, take this list a suggestion. Then use your common sense.

Answer 4

Thank You guys but for your info I know about problem with this missing index, it will provide duplicate index, column not in order and many more, I have seen many links on it.

My question as stated above, IF any of you know how to write query to get redundant index key column based on instance name.

Please let me know if you need more clarification

Answer 5

Something like this perhaps?

https://www.sqlskills.com/blogs/kimberly/removing-duplicate-indexes/