About the Missing Indexes Feature

  • Article

The missing indexes feature uses dynamic management objects and Showplan to provide information about missing indexes that could enhance SQL Server query performance.

Components

When the query optimizer generates a query plan, it analyzes what are the best indexes for a particular filter condition. If the best indexes do not exist, the query optimizer generates a suboptimal query plan, but still stores information about these indexes. The missing indexes feature enables you to access information about these indexes so you can decide whether they should be implemented.

The missing indexes feature consists of the following components:

  • A set of dynamic management objects that can be queried to return information about missing indexes.
  • The MissingIndexes element in XML Showplans, which correlate indexes that the query optimizer considers missing with the queries for which they are missing.

The components of the missing indexes feature are discussed in detail in the following sections.

Dynamic Management Objects

After running a typical workload on SQL Server, you can retrieve information about missing indexes by querying the dynamic management objects listed in the following table. These dynamic management objects are stored in the master database.

Dynamic management object Information returned

sys.dm_db_missing_index_group_stats

Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes.

sys.dm_db_missing_index_groups

Returns information about a specific group of missing indexes, such as the group identifier and the identifiers of all missing indexes that are contained in that group.

sys.dm_db_missing_index_details

Returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index.

sys.dm_db_missing_index_columns

Returns information about the database table columns that are missing an index.

You can use the information returned by these dynamic management objects with tools or scripts that use the information to generate CREATE INDEX DDL statements that will implement the missing indexes.

Transaction Consistency

Individual modifications to rows in these dynamic management objects are not transactionally consistent. That is, if a query is aborted or the enclosing transaction is rolled back, the rows that contain information about the missing indexes for that query will still exist.

Only entire transactions are supported. Checkpoints and partial rollbacks are not supported.

Note

When the metadata for a table changes, all missing index information about that table is deleted from these dynamic management objects. Table metadata changes can occur when columns are added or dropped from a table, for example, or when an index is created on a column of a table.

XML Showplan MissingIndexes Element

To correlate queries with the missing indexes that are identified in dynamic management object results, you can view the MissingIndexes element in XML Showplans. The MissingIndexes element is illustrated in the following example:

<ShowPlanXML…>

 <BatchSequence>

  <Batch>

   <Statements>

    <StmtSimple…>

     <StatementSetOptions… />

      <QueryPlan…>

       ``<MissingIndexes>

        <MissingIndexGroup Impact="22.8764">

         <MissingIndex Database="[ADVENTUREWORKS]" Schema="[Person]" Table="[Address]">

          <ColumnGroup Usage="EQUALITY">

           <Column Name="[PostalCode]" ColumnId="4" />

          </ColumnGroup>

          <ColumnGroup Usage="INEQUALITY">

           <Column Name="[ModifiedDate]" ColumnId="5" />

          </ColumnGroup>

          <ColumnGroup Usage="INCLUDE">

           <Column Name="[AddressLine1]" ColumnId="2" />

           <Column Name="[AddressLine2]" ColumnId="3" />

           <Column Name="[StateProvinceID]" ColumnId="1" />

          </ColumnGroup>

         </MissingIndex>

        </MissingIndexGroup>

       </MissingIndexes>

The information contained in the MissingIndexes element can help you determine what indexes would improve the performance of the specific query described in the StmtSimple element, which includes the Transact-SQL statement itself. Then, using the information returned for this element, you can write a CREATE INDEX DDL statement.

Enabling and Disabling the Missing Indexes Feature

The missing indexes feature is on by default. No controls are provided to turn the feature on or off, or to reset any of the tables returned when the dynamic management objects are queried. When SQL Server is restarted, all of the missing index information is dropped.

This feature can only be disabled if an instance of SQL Server is started by using the -x argument with the sqlservr command-prompt utility. For more information, see sqlservr Application.

See Also

Concepts

Using Missing Index Information to Write CREATE INDEX Statements
Limitations for Using the Missing Indexes Feature
Related Query Tuning Features

Other Resources

Finding Missing Indexes

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
New content:
  • Added sentence indicating that the missing indexes feature dynamic management objects are stored in the master database.