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.
- It is not intended to fine tune an indexing configuration.
- It cannot gather statistics for more than 500 missing index groups.
- It does not specify an order for columns to be used in an index.
- For queries involving only inequality predicates, it returns less accurate cost information.
- It reports only include columns for some queries, so index key columns must be manually selected.
- It returns only raw information about columns on which indexes might be missing.
- It can return different costs for the same missing index group that appears multiple times in XML Showplans.