sys.dm_db_missing_index_details

New: 5 December 2005

Returns detailed information about missing indexes.

Column name Data type Description

index_handle

int

Identifies a particular missing index. The identifier is unique across the server. index_handle is the key of this table.

database_id

smallint

Identifies the database where the table with the missing index resides.

object_id

int

Identifies the table where the index is missing.

equality_columns

nvarchar(4000)

Comma-separated list of columns that contribute to equality predicates of the form:

table.column =constant_value

inequality_columns

nvarchar(4000)

Comma-separated list of columns that contribute to inequality predicates, for example, predicates of the form:

table.column > constant_value

Any comparison operator other than "=" expresses inequality. For a complete list of comparison operators, see Comparison Operators (Database Engine).

included_columns

nvarchar(4000)

Comma-separated list of columns needed as covering columns for the query. For more information about covering or included columns, see Creating Indexes with Included Columns.

statement

nvarchar(4000)

Name of the table where the index is missing.

Remarks

Information returned by sys.dm_db_missing_index_details is updated when a query is optimized by the query optimizer, and is not persisted. Missing index information is kept only until SQL Server is restarted. Database administrators should periodically make backup copies of the missing index information if they want to keep it after server recycling.

To determine which missing index groups a particular missing index is part of, you can query the sys.dm_db_missing_index_groups dynamic management view by equijoining it with sys.dm_db_missing_index_details based on the index_handle column.

For information about enabling and disabling missing index information collection, see About the Missing Indexes Feature.

For information about limitations of this feature, see Limitations for Using the Missing Indexes Feature.

Using Missing Index Information in CREATE INDEX Statements

To convert the information returned by sys.dm_db_missing_index_details into a CREATE INDEX statement, equality columns should be put before the inequality columns, and together they should make the key of the index. Included columns should be added to the CREATE INDEX statement using the INCLUDE clause. To determine an effective order for the equality columns, order them based on their selectivity: list the most selective columns first (leftmost in the column list). For more information about writing CREATE INDEX statements using the missing index information returned by sys.dm_db_missing_index_details, see Using Missing Index Information to Write CREATE INDEX Statements.

Transaction Consistency

If a transaction creates or drops a table, the rows containing missing index information about the dropped objects are removed from this dynamic management object, preserving transaction consistency. For more information about transaction consistency in relation to the missing indexes dynamic management objects, see About the Missing Indexes Feature.

Permissions

Users must be granted the VIEW SERVER STATE permission or any permission that implies the VIEW SERVER STATE permission to query this dynamic management view.

See Also

Reference

sys.dm_db_missing_index_columns
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats

Other Resources

About the Missing Indexes Feature

Help and Information

Getting SQL Server 2005 Assistance