Статья
09/28/2010

Использование сведений об отсутствующих индексах для формирования инструкций CREATE INDEX

В этом разделе содержатся указания и примеры использования сведений, возвращаемых компонентами функции поиска отсутствующих индексов для формирования инструкций CREATE INDEX для их создания.

Определение порядка столбцов в инструкции CREATE INDEX

Компоненты функции поиска отсутствующих индексов включают в результаты своей работы равенство, неравенство и включенные столбцы.

Например, элемент MissingIndexes XML-инструкции Showplan указывает на то, использовался ли ключевой столбец указателя для определения равенства (=) или неравенства (<, > и т. д.) в предикате инструкции Transact-SQL или был включен только из-за того, что подходит для выполнения запроса. Эти сведения отображаются в виде одного из следующих значений для атрибута Usage вложенного элемента ColumnGroup:

Объекты DMO 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, создаваемых на основе выходных данных компонента функции поиска отсутствующих индексов.

Сначала перечисляются столбцы равенства (самые левые в списке столбцов).
После них перечисляются столбцы неравенства (справа от столбцов равенства).
Включенные столбцы перечисляются в предложении INCLUDE инструкции CREATE INDEX.
Чтобы определить эффективный порядок для столбцов равенства, расположите их в избирательном порядке, сначала указав столбцы с самой высокой степенью избирательности.

Примеры

Использование выходных параметров XML-элемента MissingIndexes инструкции Showplan

Функция поиска отсутствующих индексов весьма эффективна при работе со сведениями, автоматически формируемыми оптимизатором запросов во время своей работы. Однако чтобы у оптимизатора появилась возможность сформировать сведения об отсутствующих индексах, необходимо предварительное выполнение запросов на экземпляре SQL Server.

В следующем примере показано создание инструкции на языке DDL с использованием сведений, полученных из элемента MissingIndexes.

Включите функцию XML Showplan при помощи параметра SET STATISTICS XML ON и выполните в образце базы данных AdventureWorks следующий запрос:
```
USE AdventureWorks;GOSET STATISTICS XML ON;GOSELECT CustomerID, SalesOrderNumber, SubTotalFROM Sales.SalesOrderHeaderWHERE ShipMethodID > 2AND SubTotal > 500.00AND Freight < 15.00AND TerritoryID = 5;GO
```
Просмотрите данные, возвращенные в элементе MissingIndexes оператора Showplan:

<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, выполнив следующую DDL-инструкцию CREATE INDEX:
```
USE AdventureWorks;GOIF EXISTS (SELECT name FROM sys.indexes           WHERE name = N'IX_SalesOrderHeader_TerritoryID')     DROP INDEX IX_SalesOrderHeader_TerritoryID ON Sales.SalesOrderHeader;GOCREATE NONCLUSTERED INDEX IX_SalesOrderHeader_TerritoryID     ON Sales.SalesOrderHeader (TerritoryID, ShipMethodID, SubTotal, Freight)     INCLUDE (SalesOrderNumber, CustomerID);GO
```
В инструкции CREATE INDEX используются база данных (USE AdventureWorks), схема и имя таблицы (ON Sales.SalesOrderHeader), перечисленные в элементе MissingIndex. Кроме того, здесь используются столбцы, перечисленные для каждого вложенного элемента ColumnGroup ключевых (TerritoryID, ShipMethodID, SubTotal, Freight) и неключевых (INCLUDE (SalesOrderNumber, CustomerID)) столбцов.

Использование результатов, возвращаемых объектами DMO

Прежде чем запрашивать сведения об отсутствующих индексах, необходимо произвести выполнение запросов на экземпляре SQL Server, чтобы у оптимизатора запросов была возможность сформировать сведения об отсутствующих индексах.

В следующем примере показано создание DDL-инструкции на основе сведений, полученных из динамического административного представления sys.dm_db_missing_index_details.

В образце базы данных AdventureWorks выполните следующий запрос:

USE AdventureWorks;GOSELECT City, StateProvinceID, PostalCodeFROM Person.AddressWHERE 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;GOIF EXISTS (SELECT name FROM sys.indexes           WHERE name = N'IX_PersonAddress_StateProvinceID')     DROP INDEX IX_PersonAddress_StateProvinceID ON Person.Address;GOCREATE 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, всегда перечисляются в предложении INCLUDE инструкции CREATE INDEX.