项目
12/14/2010

使用缺失索引信息编写 CREATE INDEX 语句

本主题包含使用由缺失索引功能组件返回的信息，针对缺失索引编写 CREATE INDEX 语句的指南和示例。

排列 CREATE INDEX 语句中的列

缺失索引功能的组件在其输出中列出相等列、不等列以及包含列。

例如，XML 显示计划 MissingIndexes 元素指示索引键列在 Transact-SQL 语句谓词中是用于相等 (=) 或不等（<、> 等），还是只被包含在其中以包含查询。它将针对 ColumnGroup 子元素的 Usage 属性，将此信息显示为下列值之一：

动态管理对象 sys.dm_db_missing_index_details 和 sys.dm_db_missing_index_columns 返回指示索引键列是相等列、不等列，还是包含列的结果。sys.dm_db_missing_index_details 的结果集在 equality_columns、inequality_columns 和 included_columns 列中返回此信息。sys.dm_db_missing_index_columns 返回的结果集在其 column_usage 列中返回此信息。

请使用下列指南排列通过缺失索引功能组件输出编写的 CREATE INDEX 语句中的列：

首先列出相等列（在列列表的最左侧）。
在相等列之后列出不等列（位于列出的相等列的右侧）。
在 CREATE INDEX 语句的 INCLUDE 子句中列出包含列。
若要确定相等列的有效顺序，请根据其选择性排序；即，首先列出最具选择性的列。

示例

使用 XML 显示计划 MissingIndexes 元素的输出

缺失索引功能利用查询优化器在优化查询时自动生成的信息。但是，必须首先在 SQL Server 实例上执行查询，以便优化器能够生成此缺失索引信息。

下面的示例显示了如何通过 MissingIndexes 元素返回的信息创建数据定义语言 (DDL) 语句：

使用 SET STATISTICS XML ON 选项打开 XML 显示计划功能，并对 AdventureWorks 示例数据库执行以下查询：

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

查看生成的显示计划的 MissingIndexes 元素中返回的输出：

<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>
使用 MissingIndex 和 ColumnGroup 元素中返回的信息创建缺失索引，以按如下方式编写 CREATE INDEX DDL 语句：
```
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
```
此 CREATE INDEX 语句使用 MissingIndex 元素中列出的数据库 (USE AdventureWorks)、架构和表名 (ON Sales.SalesOrderHeader)。同时还使用为键列 (TerritoryID, ShipMethodID, SubTotal, Freight) 和非键列 (INCLUDE (SalesOrderNumber, CustomerID)) 的每个 ColumnGroup 子元素列出的列。

使用动态管理对象返回的结果

在检索缺失索引信息之前，必须在 SQL Server 实例上执行查询，以便查询优化器能够生成缺失索引信息。

下面的示例显示了如何通过 sys.dm_db_missing_index_details 动态管理视图返回的信息创建 DDL 语句。

对 AdventureWorks 示例数据库执行以下查询：

USE AdventureWorks;
GO
SELECT City, StateProvinceID, PostalCode
FROM Person.Address
WHERE StateProvinceID = 9;
GO

对 sys.dm_db_missing_index_details 动态管理视图执行以下查询：
```
SELECT *
FROM sys.dm_db_missing_index_details
```
查询此动态管理视图将返回下列结果：

index_handle	database_id	object_id	equality_columns	inequality_columns	included_columns	statement
1	6	53575229	[StateProvince]	NULL	[City], [PostalCode]	[AdventureWorks].[Person].[Address]

查询 sys.dm_db_missing_index_details 动态管理视图之后，您可以使用 equality_columns、included_columns 和 statement 列中返回的信息按如下方式创建缺失索引：

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

在此示例中，sys.dm_db_missing_index_details 结果集中不返回任何 inequality_columns。如果返回了不等列，则可以在 equality_columns 之后列出这些列。included_columns 中返回的列始终在 CREATE INDEX 语句的 INCLUDE 子句中列出。