In the last post you saw that the missing index feature does not gather statistics for more than 500 missing index groups. This time I am going to reveal another limitation which is not even documented - the missing index feature does not recommend missing indexes for trivial queries. SQL Server has to create an execution plan before executing a query because it has to evaluate the different available physical iterators depending on the cardinality estimation, supportive indexes, available hardware resources and instance settings to find a good enough plan.
The limitations of the missing index feature – It cannot gather statistics for more than 500 missing index groups
Implementing the right indexes will improve the execution time and IO reads for a Select, Update or Delete. Unfortunately sometimes it is not that easy to identify missing indexes without having a deep understanding about Query Execution Plans. That is why SQL Server gives us a hint about missing indexes in an Execution Plan. Sounds beneficial but in this blog post I will proof that it is not always a good idea to rely on these index recommendations. According to Microsoft, there are the several Limitations for Using the Missing Indexes Feature.