SQL Server Interview Question #233

What are missing-index recommendations?

Advanced Indexing Senior Advanced

Detailed Explanation

SQL Server can record information about indexes the optimizer believes might reduce the estimated cost of queries. Missing-index suggestions can appear in execution plans and dynamic management views.

They are useful clues, not commands. Suggestions are generated from individual optimization opportunities and do not fully account for existing overlapping indexes, write overhead, storage, or the complete workload.

Before creating a recommended index, compare it with existing indexes and consider consolidating similar recommendations.

Code Example

SELECT *
FROM sys.dm_db_missing_index_details;