Redundant index key columns based on missing index suggestion

Mario2286 441 Reputation points
2021-03-11T15:51:41.357+00:00

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

76759-image.png

Appreciate your help

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,486 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
0 comments No comments
{count} votes

5 answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2021-03-11T17:30:06.293+00:00

    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.

    0 comments No comments

  2. tibor_karaszi@hotmail.com 4,301 Reputation points
    2021-03-11T17:55:28.91+00:00

    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! :-)

    0 comments No comments

  3. Erland Sommarskog 100.1K Reputation points MVP
    2021-03-11T22:40:39.29+00:00

    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.

    0 comments No comments

  4. Mario2286 441 Reputation points
    2021-03-12T01:30:26.413+00:00

    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

    0 comments No comments

  5. tibor_karaszi@hotmail.com 4,301 Reputation points
    2021-03-12T08:53:38.803+00:00
    0 comments No comments