• Статья

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

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

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

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

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

<ColumnGroup Usage="EQUALITY">

<ColumnGroup Usage="INEQUALITY">

<ColumnGroup Usage="INCLUDE">

Объекты 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.

  1. Включите функцию XML Showplan при помощи параметра SET STATISTICS XML ON и выполните в образце базы данных 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

  2. Просмотрите данные, возвращенные в элементе MissingIndexes оператора Showplan:

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

  3. Создайте отсутствующий индекс на основе сведений, полученных из элементов MissingIndex и ColumnGroupдля записи инструкции DDL CREATE INDEX следующим образом:

    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 используется база данных (USE AdventureWorks2008R2), схема и имя таблицы (ON Sales.SalesOrderHeader), перечисленные в элементе MissingIndex. Кроме того, здесь используются столбцы, перечисленные для каждого вложенного элемента ColumnGroup ключевых (TerritoryID, ShipMethodID, SubTotal, Freight) и неключевых (INCLUDE (SalesOrderNumber, CustomerID)) столбцов.

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

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

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

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

    USE AdventureWorks2008R2;
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 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, всегда перечисляются в предложении INCLUDE инструкции CREATE INDEX.