Historical article
- Original title: DMV of the Day - sys.dm_db_missing_index_details
- Author: Brian Knight
- Original publication date: October 23, 2005
- Originally published on: White Knight Technology
- Preservation note: This article preserves the substance recovered from the Wayback snapshot.
Archive provenance: White Knight Technology Historical Archive
Dynamic Management Views, or DMVs, were one of SQL Server 2005's useful new database-administration features. They exposed information that had been hidden or unavailable in SQL Server 2000. One useful place to begin investigating an unfamiliar environment was sys.dm_db_missing_index_details.
The DMV supplies hints about indexes the database engine thinks may be useful for a database. It identifies the object and the columns involved in the recommendation. The historical article specifically suggested using object_name(object_id) to translate the object identifier into its logical name:
SELECT object_name(object_id)
FROM sys.dm_db_missing_index_details;
This was a first-pass investigation tool: it could provide a general sense of possible weaknesses when entering a new environment, after which an administrator could work through the system and decide whether that initial picture was correct.
There were two important cautions. First, the DMV contents lasted only until SQL Server was stopped and restarted. After a restart, the information was flushed and needed time under a representative workload to become useful again. Second, its recommendations were hints, not rules. Like the Database Engine Tuning Advisor, it might recommend an index sixteen columns wide or another design that was impractical for the environment. Every recommendation still required judgment and validation.
2026 Update
For current syntax, permissions, platform coverage, persistence behavior, row limits, and other limitations, consult Microsoft's current sys.dm_db_missing_index_details documentation. Missing-index output remains advisory: evaluate it alongside the existing index design and a representative workload before making production changes.