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 (Transact-SQL)	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 (Transact-SQL)	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 (Transact-SQL)	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 (Transact-SQL)	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

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.