I have an index on a table that is `IX_Existing on JATable (CR, MC, SR) include (NF, S)` however when I look at the missing index details I am seeing that an index on the same table is suggested as `IX_Missing on JATable (SR, MC) include (CR, NF, S)`. Why isn't the existing index being used. Is it because the indexed columns are not in the same order as the ones that, presumably, the query is returning? ie CR, MC and SR but the query wants SR followed by MC? The current index is highly used for seeks so I am loathe to alter/drop it in favour of the one suggested as I would expect to see the reverse of my current situation in due course. The most selective column in the table is CR, followed by MC and then SR - hence the order of the columns in the current index.
If your query, whatever it is, queries SR and MC, but CR is nowhere to be seen the current index is pretty useless for that query. The order of the columns of an index is paramount to it being used. Think in the old adage of a phone book, searching for all the Johns can only be done with a full index scan, whereas a search for Jones can be done with an index seek (then scan), i.e. column order is important. I would suggest adding the second index (if it really needs to be there) and then monitoring system behaviour after that. It could be that the two indexes together could be of benefit to *other* queries you weren't even considering. Don't forget plan recompiles that could happen and all the rest.
The second index That is suggested is a result of queries where the cr column is not in a where or join clause. You need both indexes for best read performance. But keep in mind that the order of the index columns may be incorrect. You should probably deity the order because of the selectivity.