I've been delving into query store to find the cause of some high CPU usage and found some potential index improvements to some existing indexed views by looking through the plans of the highest CPU consuming queries.
The odd thing is that none of these index improvements appear when querying the missing index DMVs that I would normally use to find missing indexes.
What could be the cause of this?
We use the noexpand hint with indexed views.
The missing index DMVs only allow for a maximum of 600 entries (threshold details), so it is possible some suggestions were aged out.
There are other ways this transient information can be purged, as partially documented in the advice to use Query Store instead:
Missing index suggestions in DMVs are cleared by events such as instance restarts, failovers, and setting a database offline. Additionally, when the metadata for a table changes, all missing index information about that table is deleted from these dynamic management objects. Table metadata changes can occur when columns are added or dropped from a table, for example, or when an index is created on a column of a table. Performing an ALTER INDEX REBUILD operation on an index on a table also clears missing index requests for that table.
Similarly, execution plans stored in the plan cache are cleared by events such as instance restarts, failovers, and setting a database offline. Execution plans may be removed from cache due to memory pressure and recompilations.
Missing index suggestions in execution plans can be persisted across these events by enabling Query Store.
Missing index suggestions are not generated for an indexed view where the NOEXPAND hint is specified. Index suggestions may still be generated for other tables in the same query.
When NOEXPAND is not specified, the indexed view is expanded into the underlying definition before optimization. Missing indexes may be generated on this expansion as usual.
One of the cost-based decisions the optimizer makes is to match (parts of) the expanded plan back to (one or more) indexed view(s). This is only available in Enterprise Edition and equivalents.
Missing index suggestions are never made for indexed views themselves regardless of Edition. I don't believe this limitation is specifically documented, so my assertion is based only on observed behaviour.
I highly recommend reading the second documentation link in full, but particularly the section on the limitations of the missing index feature.
You may also be interested in the Database Engine Tuning Advisor, which can suggest partitioning, indexed views, and indexes on those views, given a workload as input. It has its limitations as well, but it is much more capable than the opportunistic missing indexes feature. All recommendations should be validated by a capable database practitioner before implementation.
Related Q & A: Why doesn't SQL Server have any missing index requests in the DMVs or Query Plans?
