Index exists but still shows on Missing Indexes query results! Why?

Question

I have a table 'Patient' with the below index

CREATE NONCLUSTERED INDEX [IX_Patient_PatientId_First_LastName] ON [party].[Patient]
(
	[PatientId] ASC
)
INCLUDE([CurrentFirstName],[CurrentLastName]) 

GO

The index was created 2 days ago and its usage shows it has seeks and the last seek was on 9/20

     BUT the missing indexes query keeps showing that index as missing  

It is documented by MSFT
Information returned by sys.dm_db_missing_index_details is updated when a query is optimized by the query optimizer, and is not persisted. Missing index information is kept only until the database engine is restarted. Database administrators should periodically make backup copies of the missing index information if they want to keep it after server recycling. Use the sqlserver_start_time column in sys.dm_os_sys_info to find the last database engine startup time.
And here is when my database engine was last restarted:

The database engine was restarted BEFORE the last seek of the index seek ( usage snapshot).

Can someone explain why the Missing index query that uses sys.dm_db_missing_index_details still retain old collected data thus keeps showing the missing index?
When will the old collected data be refreshed on sys.dm_db_missing_index_details ? It should have been cleared out when the database engine got restarted on 9/20.
Thanks!

Answer 1

As you can read here this how the DMVs related to missing indexes behave. The suggested missing indexes can overlap existing indexes, if one proceeds to use the DMVs to create indexes without verifying the indexes do not already exist he/she can end up creating duplicated indexes, so Microsoft documentation encourage to double check all index recommendations before implementing them.

Query Store is usually the one feature that persists index recommendations after restarts.

Automatic Tuning on Azure SQL uses DMV's missing index information to create indexes and you can see Automatic Tuning ending up creating duplicated indexes.

Why you cannot get the missing index DMV to stop recommending the index? One possible scenario that causes this issue is when an operator on a Query Plan think it will benefit from using the index but it ends not using it and instead uses another index usually a non-clustered index, but the operator keeps reporting the index missing because it thinks the index would be useful and suggests it. Sometimes the index requested/suggested by the operator will not really benefit it, but keeps asking for it despite the index already exists.

Bottom line this is a bug on the Query Optimizer that has never been fixed on SQL Server and that exists also on Azure SQL. It’s a bug related to the propagation of metadata into the in-memory set of metadata that the query optimizer uses.