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

このトピックでは、欠落したインデックスに対する CREATE INDEX ステートメントを記述するために、欠落したインデックス機能コンポーネントによって返される情報を使用するためのガイドラインとその例を説明します。

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

欠落したインデックス機能のコンポーネントでは、出力に等号列、不等号列、付加列が表示されます。

たとえば、XML プラン表示の MissingIndexes 要素は、インデックス キー列が Transact-SQL ステートメントの述語の中で等号 (=) または不等号 (<、> など) に対して使用されているか、またはクエリに対応するためだけに含まれているかを示します。この情報は、ColumnGroup サブ要素の Usage 属性の次のいずれかの値として表示されます。

<ColumnGroup Usage="EQUALITY">

<ColumnGroup Usage="INEQUALITY">

<ColumnGroup Usage="INCLUDE">

動的管理オブジェクト 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) ステートメントを作成する方法を示しています。

1. 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
   

2. 生成されたプラン表示の 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>

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

   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 ステートメントを作成する方法を示しています。

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

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

2. 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]

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

関連項目

参照

SET STATISTICS XML (Transact-SQL)

CREATE INDEX (Transact-SQL)

sys.dm_db_missing_index_details (Transact-SQL)

sys.dm_db_missing_index_columns (Transact-SQL)

概念

XML プラン表示

欠落したインデックス機能について

インデックス