3

I have a table with 307200 records, with f_column being VARCHAR(30). No missing index are shown in the missing indexes dmv or suggested in the execution plan.

SELECT f_column, f_column2 FROM t_table WHERE f_column = '23BE46F3-E9A9-4526-A2F8-3F51818025B5'

The query returns 5 results, costing 9958 logical reads. With the index below, it costs 3 logical reads.

CREATE NONCLUSTERED INDEX IX_t_Table_f_column ON t_table (f_column) INCLUDE (f_column2)

What could cause the missing indexes to not show up in both the missing indexes dmv and execution plan when adding the index would benefit the query tremendously?

I initially suspected statistics, and updated all statistics for the table, but the issue still persists. For some reason, adding or altering a column on the table will cause SQLServer to come back to its senses, and the missing indexes appear in missing indexes dmv as well as the execution plan on the query's next execution.

Improve this question
3
  • do you have other indexes on this table? what is the optimization level of both execution plans (with and without missing index recommendation)
    – NikitaSerbskiy
    Commented Sep 23, 2020 at 8:57
  • The missing index suggestions shouldn't be driving your query tuning decisions anyway. They frequently miss suggestions, not just on trivial plans. They also make suggestions that are poor or even dangerous to performance. At best, they're a shortcut for guidance on tuning. Only rely on them that much.
    – Grant Fritchey
    Commented Sep 23, 2020 at 11:52
  • @Nikita There are no other indexes on the table other than the clustered index on another primary column
    – JieLong
    Commented Sep 24, 2020 at 8:09

1 Answer 1

Reset to default
5

Almost certainly because it is seen as a trivial plan and those do not activate the missing index feature, there´s a workaround in this blog by Mr Darling

https://www.erikdarlingdata.com/sql-server/whats-the-point-of-1-select-1/

Improve this answer
1
  • This seems to be the case! Thanks!
    – JieLong
    Commented Sep 24, 2020 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.