[アーティクル]
10/25/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

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>

CREATE INDEX DDL ステートメントを記述するために、次のように、MissingIndex要素と ColumnGroup要素で返された情報を使用して欠落したインデックスを作成します。

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

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) を使用します。また、キー列 (TerritoryID, ShipMethodID, SubTotal, Freight) と非キー列 (INCLUDE (SalesOrderNumber, CustomerID)) のそれぞれの ColumnGroup サブ要素に対して示された列も使用します。

動的管理オブジェクトによって返される結果の使用

欠落したインデックス情報を取得するには、クエリオプティマイザーが欠落したインデックス情報を生成できるように、SQL Server のインスタンスに対してクエリを実行する必要があります。

次の例は、sys.dm_db_missing_index_details 動的管理ビューによって返された情報から DDL ステートメントを作成する方法を示しています。

AdventureWorks2008R2 サンプルデータベースに対して次のクエリを実行します。

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

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

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

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 句に示されます。

欠落したインデックス情報を使用した CREATE INDEX ステートメントの記述

CREATE INDEX ステートメントでの列の順序付け

例

XML プラン表示の MissingIndexes 要素の出力の使用

動的管理オブジェクトによって返される結果の使用

関連項目

参照

概念

その他のリソース