• Article

Using Missing Index Information to Write CREATE INDEX Statements

This topic contains guidelines for and examples of using the information that is returned by the missing indexes feature components to write CREATE INDEX statements for the missing indexes.

Ordering Columns in CREATE INDEX Statements

Components of the missing indexes feature list equality, inequality, and included columns in their output.

For example, the XML Showplan MissingIndexes element indicates whether an index key column is used for equality (=) or inequality (<, >, and so on) in the Transact-SQL statement predicate, or is just included to cover a query. It displays this information as one of the following values for the Usage attribute of the ColumnGroup subelement:

<ColumnGroup Usage="EQUALITY">

<ColumnGroup Usage="INEQUALITY">

<ColumnGroup Usage="INCLUDE">

The dynamic management objects sys.dm_db_missing_index_details and sys.dm_db_missing_index_columns return results that indicate whether an index key column is an equality, inequality, or included column. The result set of sys.dm_db_missing_index_details returns this information in the equality_columns, inequality_columns, and included_columns columns. The result set returned by sys.dm_db_missing_index_columns returns this information in its column_usage column.

Use the following guidelines for ordering columns in the CREATE INDEX statements you write from the missing indexes feature component output:

  • List the equality columns first (leftmost in the column list).

  • List the inequality columns after the equality columns (to the right of equality columns listed).

  • List the include columns in the INCLUDE clause of the CREATE INDEX statement.

  • To determine an effective order for the equality columns, order them based on their selectivity; that is, list the most selective columns first.

Examples

Using the output from the XML Showplan MissingIndexes element

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 AdventureWorks2008R2 sample database:

    USE AdventureWorks2008R2;
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="[AdventureWorks2008R2]" 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 AdventureWorks2008R2;
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 AdventureWorks2008R2), 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)).

Using results returned by a dynamic management object

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 AdventureWorks2008R2 sample database:

    USE AdventureWorks2008R2;
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:

index_handle

database_id

object_id

equality_columns

inequality_columns

included_columns

statement

1

6

53575229

[StateProvince]

NULL

[City], [PostalCode]

[AdventureWorks].[Person].[Address]

  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 AdventureWorks2008R2;
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.