# Missing Indexes: Automatic Index Detection > [!Note] > `SQL Server’s optimizer continuously **identifies missing index opportunities** during normal query execution, **logs** them in execution plans and DMVs, and exposes **recommendations** that you can **selectively implement** to improve performance without external tools.` Without any additional utilities, the optimizer tracks patterns where an index could significantly reduce I/O or CPU work. Whenever it encounters repetitive scans or seeks that would benefit from a new index, it records a **missing index request** in the query plan’s metadata. These recommendations are persisted in both the execution plan cache and the system dynamic management views—most notably **sys.dm_db_missing_index_*—**so that you can review them later. By querying these DMVs, you gain insight into which columns and predicates would yield the greatest performance gains if indexed. > [!tip] > Regularly review **sys.dm_db_missing_index_details** and related DMVs to identify high-impact index candidates before they become performance bottlenecks. Implementing missing index suggestions should remain a **selective** process: evaluate each recommendation against your overall workload, consider the overhead of additional indexes on writes, and validate benefits in a non-production environment. |**Stage**|**Description**| |---|---| |**Automatic Detection**|The optimizer flags index needs during live query compilation and execution, without requiring external tuning tools.| |**Information Logging**|Recommendations are stored within cached execution plans and surfaced in **sys.dm_db_missing_index_*** DMVs for post-mortem analysis.| |**Recommendation Retrieval**|You can query DMVs to retrieve proposed index definitions—key columns, included columns, and estimated impact—generated by the optimizer.| |**Selective Implementation**|Review and test each suggestion: balance the read performance gains against the cost of index maintenance on inserts, updates, and deletes.| > [!caution] > Missing-index DMVs accumulate over time; clear or cycle your plan cache judiciously to avoid stale recommendations skewing your analysis. --- ## ## References - Korotkevitch, D. (2022). _SQL Server advanced troubleshooting and performance tuning: Best practices and techniques_. O’Reilly Media. - Nevarez, B. (2022). _SQL Server query tuning and optimization: Optimize Microsoft SQL Server 2022 queries and applications_. Packt Publishing.