2

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.

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 Sep 23 '20 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 Sep 23 '20 at 11:52
  • @Nikita There are no other indexes on the table other than the clustered index on another primary column – JieLong Sep 24 '20 at 8:09
4

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/

1
  • This seems to be the case! Thanks! – JieLong Sep 24 '20 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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