Поделиться через

Использование сведений об отсутствующих индексах для формирования инструкций 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 и выполните в образце базы данных 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 оператора 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>

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

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

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

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

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

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

См. также

Основные понятия

Инструкция Showplan XML
О функции отсутствующих индексов
Индексы

Другие ресурсы

SET STATISTICS XML (Transact-SQL)
CREATE INDEX (Transact-SQL)
sys.dm_db_missing_index_details
sys.dm_db_missing_index_columns

Справка и поддержка

Получение помощи по SQL Server 2005