The missing indexes feature takes advantage of information that the query optimizer automatically generates when it optimizes a query. However, queries must first be executed on the instance of SQL Server so the optimizer can generate this missing index information.

The following example shows how to create a Data Definition Language (DDL) statement from the information returned by the MissingIndexes element:

1. Turn on the XML Showplan feature by using the SET STATISTICS XML ON option, and execute the following query against the AdventureWorks sample database:
   
   USE AdventureWorks;
   GO
   SET STATISTICS XML ON;
   GO
   SELECT CustomerID, SalesOrderNumber, SubTotal
   FROM Sales.SalesOrderHeader
   WHERE ShipMethodID > 2
   AND SubTotal > 500.00
   AND Freight < 15.00
   AND TerritoryID = 5;
   GO

2. View the returned output in the MissingIndexes element of the Showplan that is produced:
   <MissingIndexes>
  <MissingIndexGroup Impact="95.8296">
    <MissingIndex Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]">
      <ColumnGroup Usage="EQUALITY">
        <Column Name="[TerritoryID]" ColumnId="14" />
      </ColumnGroup>
      <ColumnGroup Usage="INEQUALITY">
        <Column Name="[ShipMethodID]" ColumnId="17" />
        <Column Name="[SubTotal]" ColumnId="21" />
        <Column Name="[Freight]" ColumnId="23" />
      </ColumnGroup>
      <ColumnGroup Usage="INCLUDE">
        <Column Name="[SalesOrderNumber]" ColumnId="8" />
        <Column Name="[CustomerID]" ColumnId="11" />
      </ColumnGroup>
    </MissingIndex>
  </MissingIndexGroup>
</MissingIndexes>
   
3. Create the missing index by using information returned in the MissingIndex and ColumnGroup elements to write a CREATE INDEX DDL statement as follows:
   
   USE AdventureWorks;
   GO
   IF EXISTS (SELECT name FROM sys.indexes
              WHERE name = N'IX_SalesOrderHeader_TerritoryID')
        DROP INDEX IX_SalesOrderHeader_TerritoryID ON Sales.SalesOrderHeader;
   GO
   CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_TerritoryID
        ON Sales.SalesOrderHeader (TerritoryID, ShipMethodID, SubTotal, Freight)
        INCLUDE (SalesOrderNumber, CustomerID);
   GO

   This CREATE INDEX statement uses the database (USE AdventureWorks), schema, and table name (ON Sales.SalesOrderHeader) listed in the MissingIndex element. It also uses the columns listed for each ColumnGroup subelement for the key columns (TerritoryID, ShipMethodID, SubTotal, Freight) and nonkey columns (INCLUDE (SalesOrderNumber, CustomerID)).
   

Before you can retrieve missing index information, you must execute queries on the instance of SQL Server so that the query optimizer can generate the missing index information.

The following example shows how to create a DDL statement from the information returned by the sys.dm_db_missing_index_details dynamic management view.

1. Execute the following query against the AdventureWorks sample database:
   
   USE AdventureWorks;
   GO
   SELECT City, StateProvinceID, PostalCode
   FROM Person.Address
   WHERE StateProvinceID = 9;
   GO

2. Execute the following query against the sys.dm_db_missing_index_details dynamic management view:
   
   SELECT *
   FROM sys.dm_db_missing_index_details
   

   Querying this dynamic management view returns the following results:
   

1. After querying the sys.dm_db_missing_index_details dynamic management view, you can create the missing index by using information that is returned in the equality_columns, included_columns, and statement columns as follows:
   
   USE AdventureWorks;
   GO
   IF EXISTS (SELECT name FROM sys.indexes
              WHERE name = N'IX_PersonAddress_StateProvinceID')
        DROP INDEX IX_PersonAddress_StateProvinceID ON Person.Address;
   GO
   CREATE NONCLUSTERED INDEX IX_PersonAddress_StateProvinceID
        ON Person.Address (StateProvinceID)
        INCLUDE (City, PostalCode);
   GO

In this example, no inequality_columns were returned in the sys.dm_db_missing_index_details result set. If they had been, you would list those columns after the equality_columns. Columns that are returned in included_columns are always listed in the INCLUDE clause of the CREATE INDEX statement.