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:
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.
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:
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
View the returned output in the MissingIndexes element of the Showplan that is produced:
<MissingIndex Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]">
<Column Name="[TerritoryID]" ColumnId="14" />
<Column Name="[ShipMethodID]" ColumnId="17" />
<Column Name="[SubTotal]" ColumnId="21" />
<Column Name="[Freight]" ColumnId="23" />
<Column Name="[SalesOrderNumber]" ColumnId="8" />
<Column Name="[CustomerID]" ColumnId="11" />
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)).
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.
Execute the following query against the AdventureWorks sample database:
USE AdventureWorks; GO SELECT City, StateProvinceID, PostalCode FROM Person.Address WHERE StateProvinceID = 9; GO
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:
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.