發行項
04/02/2012

使用遺漏索引資訊撰寫 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 執行程序表功能，然後對 AdventureWorks2008R2 範例資料庫執行下列查詢：

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

檢視產生之執行程序表中 MissingIndexes 元素傳回的輸出：

<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>
使用 MissingIndex 和 ColumnGroup 元素傳回的資訊建立遺漏的索引，以撰寫如下的 CREATE INDEX DDL 陳述式：
```
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
```
這個 CREATE INDEX 陳述式會使用 MissingIndex 元素中列出的資料庫 (USE AdventureWorks2008R2)、結構描述和資料表名稱 (ON Sales.SalesOrderHeader)。它也使用針對每個 ColumnGroup 子元素所列出的資料行來做為索引鍵資料行 (TerritoryID, ShipMethodID, SubTotal, Freight) 和非索引鍵資料行 (INCLUDE (SalesOrderNumber, CustomerID))。

使用動態管理物件傳回的結果

擷取遺漏索引資訊之前，您必須先在 SQL Server 的執行個體上執行查詢，使查詢最佳化工具能夠產生遺漏索引資訊。

下列範例會顯示如何從 sys.dm_db_missing_index_details 動態管理檢視傳回的資訊建立資料定義語言 DDL 陳述式。

對 AdventureWorks2008R2 範例資料庫執行下列查詢：

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

在這個範例中，sys.dm_db_missing_index_details 結果集中並未傳回任何 inequality_columns。如果傳回這種資料行，您就必須將它們列在 equality_columns 後面。included_columns 中傳回的資料行則一律列在 CREATE INDEX 陳述式的 INCLUDE 子句中。