question

Mario2286-5314 avatar image
0 Votes"
Mario2286-5314 asked ·

Redundant index key columns based on missing index suggestion

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-generalsql-server-transact-sql
image.png (26.0 KiB)
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TiborKaraszi avatar image
0 Votes"
TiborKaraszi answered ·

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

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Mario2286-5314 avatar image
0 Votes"
Mario2286-5314 answered ·

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

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TiborKaraszi avatar image
0 Votes"
TiborKaraszi answered ·
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.