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.
Best Answer
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/