Настройте некластеризованные индексы с помощью рекомендаций по отсутствующим индексам

Применимо к:SQL ServerAzure SQL База данныхAzure SQL Управляемый экземплярSQL База данных в Microsoft Fabric

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

Ограничения функции отсутствующих индексов

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

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

• Предложения отсутствующих индексов основаны на оценках, созданных во время оптимизации запроса до его выполнения. Предложения отсутствующих индексов не проверяются и не обновляются после выполнения запросов.
• Функция поиска отсутствующих индексов рекомендует только некластеризованные дисковые индексы rowstore. Уникальные и отфильтрованные индексы не предлагаются.
• Предлагаются ключевые столбцы, но предложение не указывает порядок для этих столбцов. Сведения об упорядочивании столбцов см. в разделе Применение предложений отсутствующих индексов этой статьи.
• Включенные столбцы рекомендуются, но SQL Server не выполняет анализ соотношения затрат и выгод относительно размера результирующего индекса, если рекомендуется большое количество включенных столбцов.
• Запросы на создание отсутствующих индексов могут предлагать схожие варианты индексов для одной и той же таблицы и столбца (или столбцов) в разных запросах. Необходимо просмотреть предложения индексов и по возможности их объединить.
• Рекомендации не формируются для тривиальных планов запросов.
• В запросах, включающих только предикаты неравенства, сведения о стоимости будут менее точными.
• Рекомендации собираются максимум для 600 отсутствующих групп индексов. После достижения этого порога сбор недостающих данных группы индексов прекращается.

Из-за этих ограничений предложения отсутствующих индексов стоит воспринимать как лишь один из источников информации при выполнении анализа, проектирования, настройки и проверки индексов. Рекомендации по отсутствующим индексам не являются предписанием создавать индексы в точности так, как рекомендовано.

Просмотр рекомендаций по отсутствующим индексам

Функция отсутствующих индексов состоит из двух компонентов:

• Элемент MissingIndexes в XML-коде планов выполнения. Он позволяет коррелировать индексы, которые считаются отсутствующими оптимизатором запросов, с запросами, для которых они отсутствуют.
• Набор представлений динамического управления (DMV), к которым можно обращаться с запросами, чтобы получить сведения об отсутствующих индексах. Это позволяет просматривать все рекомендации по отсутствующим индексам для базы данных.

Просмотр рекомендаций по отсутствующим индексам в планах выполнения

Обзор плана выполнения можно создать или получить несколькими способами:

• При написании или настройке запроса можно использовать SQL Server Management Studio (SSMS) для отображения предполагаемого плана выполнения без выполнения запроса или выполнения запроса и отображения фактического плана выполнения.
• Отслеживайте производительность с помощью хранилища запросов, которое при включении собирает планы выполнения.
• Кэшированные планы выполнения можно определить, выполнив запрос к таким динамическим административным представлениям (DMV), как sys.dm_exec_text_query_plan.

Например, можно использовать следующий запрос для создания отсутствующих запросов индекса к примеру баз данных AdventureWorks.

SELECT City, StateProvinceID, PostalCode
FROM Person.Address as a
JOIN Person.BusinessEntityAddress as ba on
    a.AddressID = ba.AddressID
JOIN Person.Person as  p on
    ba.BusinessEntityID = p.BusinessEntityID
WHERE p.FirstName like 'K%' and
    StateProvinceID = 9;
GO

Чтобы создать и просмотреть запросы по отсутствующим индексам, выполните следующие действия:

1. Откройте SSMS и подключитесь к своей копии образцовых баз данных AdventureWorks.

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

   

   Один план выполнения может содержать несколько отсутствующих запросов индекса, но в графическом плане выполнения можно отобразить только один запрос на индекс. Чтобы просмотреть полный список отсутствующих индексов плана выполнения, можно просмотреть XML-код плана выполнения.

3. Щелкните план выполнения правой кнопкой мыши и выберите в меню Показать XML-код плана выполнения….

   

   XML-код плана выполнения откроется в новой вкладке в SSMS.

   Note

   В параметре меню Сведения об отсутствующих индексах… будет отображаться только одно предложение отсутствующего индекса, даже если в XML-коде плана выполнения содержится несколько предложений. Отображаемая рекомендация по отсутствующему индексу может быть не той, которая даёт наибольшее расчётное улучшение для данного запроса.

4. Откройте диалоговое окно Найти с помощью сочетания клавиш CTRL+F.

5. Найдите MissingIndex.

   

   В этом примере есть два элемента MissingIndex.

   • Первый недостающий индекс указывает на то, что запрос может использовать индекс таблицы Person.Address, поддерживающий поиск по точному совпадению в столбце StateProvinceID и включающий два дополнительных столбца: City и PostalCode. На момент оптимизации оптимизатор запросов рассчитал, что этот индекс может сократить оценочную стоимость запроса на 34,2737 %.
   • Второй отсутствующий индекс указывает на то, что запрос может использовать индекс таблицы Person.Person, который поддерживает поиск по неравенству в столбце FirstName. На момент оптимизации оптимизатор запросов рассчитал, что этот индекс может сократить оценочную стоимость запроса на 18,1102 %.

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

Просмотр рекомендаций по отсутствующим индексам в динамических административных представлениях

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

Следующий запрос использует отсутствующие динамические административные представления индекса для создания CREATE INDEX инструкций. Инструкции создания индексов предназначены для помощи в создании собственного языка описания данных DDL после изучения всех запросов и существующих индексов для таблицы.

SELECT TOP 20
    CONVERT (varchar(30), getdate(), 126) AS runtime,
    CONVERT (decimal (28, 1),
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
        ) AS estimated_improvement,
    'CREATE INDEX missing_index_' +
        CONVERT (varchar, mig.index_group_handle) + '_' +
        CONVERT (varchar, mid.index_handle) + ' ON ' +
        mid.statement + ' (' + ISNULL (mid.equality_columns, '') +
        CASE
            WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL THEN ','
            ELSE ''
        END + ISNULL (mid.inequality_columns, '') + ')' +
        ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON
    migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON
    mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;
GO

Этот запрос упорядочивает предложения по столбцу под названием estimated_improvement. Предполагаемое улучшение основано на сочетании следующих факторов:

• Предполагаемая стоимость запросов, связанных с запросом по отсутствующим индексам.
• Предполагаемое воздействие после добавления индекса. Это приблизительная оценка снижения стоимости запроса после добавления некластеризованного индекса.
• Сумма выполнений операторов запроса (операций поиска и сканирования), выполненных для запросов, связанных с запросом на отсутствующий индекс. Как описано в разделе Хранение отсутствующих индексов в хранилище запросов, эта информация периодически очищается.

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

Хранение отсутствующих индексов в хранилище запросов

Рекомендации по отсутствующим индексам в динамических административных представлениях сбрасываются при таких событиях, как перезапуск экземпляра, переключение при отказе и перевод базы данных в автономный режим. Кроме того, при изменении метаданных таблицы все сведения об отсутствующих индексах для этой таблицы удаляются из объектов динамического управления. Изменения метаданных таблицы могут возникать при добавлении или удалении столбцов из таблицы, например при создании индекса в столбце таблицы. ALTER INDEX Выполнение операции с индексом в таблице также очищает отсутствующие запросы индекса для этой таблицы.

Аналогичным образом планы выполнения, хранящиеся в кэше планов, очищаются при таких событиях, как перезапуски экземпляров, переключения при отказе и перевод базы данных в автономный режим. Планы выполнения могут быть удалены из кэша из-за нехватки памяти и перекомпиляции.

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

Следующий запрос возвращает 20 лучших планов запросов из хранилище запросов, содержащих запросы на отсутствующие индексы, на основе приблизительной оценки общего числа логических чтений для запроса. Данные ограничены выполнениями запросов за последние 48 часов.

SELECT TOP 20
    qsq.query_id,
    SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,
    SUM(qrs.count_executions) AS sum_executions,
    AVG(qrs.avg_logical_io_reads) AS avg_avg_logical_io_reads,
    SUM(qsq.count_compiles) AS sum_compiles,
    (SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt
        WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text,
    TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2
        WHERE qsp2.query_id=qsq.query_id
        ORDER BY qsp2.plan_id DESC)) AS query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
CROSS APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx
JOIN sys.query_store_runtime_stats qrs on
    qsp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi on
    qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id
WHERE
    qsp.query_plan like N'%<MissingIndexes>%'
    and qsrsi.start_time >= DATEADD(HH, -48, SYSDATETIME())
GROUP BY qsq.query_id, qsq.query_hash
ORDER BY est_logical_reads DESC;
GO

Применить рекомендации по отсутствующим индексам

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

Проверка структуры базовой таблицы

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

Один из способов проверить наличие кластеризованного индекса — использовать системную хранимую процедуру sp_helpindex. Например, можно просмотреть сводку индексов таблицы Person.Address, выполнив следующую инструкцию:

exec sp_helpindex 'Person.Address';
GO

Просмотрите столбец index_description. Таблица может содержать только один кластеризованный индекс. Если в таблице реализован кластеризованный индекс, index_description будет содержать слово "clustered".

Если кластеризованный индекс отсутствует, таблица является кучей. В таком случае проверьте, не была ли эта таблица намеренно создана как куча для решения конкретной проблемы производительности. Для большинства таблиц полезны кластеризованные индексы: часто таблицы по ошибке организуются как кучи. Рассмотрите реализацию кластеризованного индекса на основе рекомендаций по проектированию кластеризованных индексов.

Проверка отсутствующих и существующих индексов на дублирование

Отсутствующие индексы могут предлагать аналогичные варианты некластеризованных индексов в одной таблице и столбцах в запросах. Отсутствующие индексы также могут быть похожи на существующие индексы в таблице. Для оптимальной производительности рекомендуется изучить отсутствующие индексы и существующие индексы для перекрытия и избежать создания повторяющихся индексов.

Создать скрипт существующих индексов для таблицы

Один из способов просмотреть определения существующих индексов для таблицы — сформировать скрипт индексов с помощью окна «Обозреватель объектов — подробные сведения»:

1. Подключите обозреватель объектов к вашему экземпляру или базе данных.
2. В обозреватель объектов разверните узел нужной базы данных.
3. Разверните папку Таблицы.
4. Разверните таблицу, для индексов которой необходимо создать скрипт.
5. Выберите папку Индексы.
6. Если панель сведений обозревателя объектов еще не открыта, в меню "Вид " выберите "Сведения обозревателя объектов " или нажмите клавишу F7.
7. Выберите все индексы, перечисленные на панели сведений об обозревателе объектов с помощью сочетания клавиш CTRL+A.
8. Щелкните правой кнопкой мыши в любом месте выделенной области и выберите пункт меню Сценарий индекса как, затем CREATETo** и Новое окно редактора запросов.

Проверка индексов и объединение при возможности

Ознакомьтесь с рекомендациями по отсутствующим индексам для таблицы в виде группы, а также с определениями существующих индексов таблицы. Не забывайте, что при определении индексов обычно столбцы равенства ставятся перед столбцами неравенства, а вместе они образовывают ключ индекса. Чтобы определить эффективный порядок столбцов равенства, расположите их на основе их выборности, перечисляя наиболее выбираемые столбцы первыми (крайние левые в списке столбцов). Уникальные столбцы более выбираемые, а столбцы с повторяющимися значениями — менее выбираемые.

Включённые столбцы следует добавить в инструкцию CREATE INDEX с помощью предложения INCLUDE. Порядок включенных столбцов не влияет на производительность запроса. Поэтому при объединении индексов включенные столбцы могут объединяться без беспокойства о порядке. Дополнительные сведения см. в рекомендациях по включённым столбцам.

Например, у вас может быть таблица Person.Address с существующим индексом по ключевому столбцу StateProvinceID. Для таблицы могут появиться отсутствующие рекомендации по индексу Person.Address для следующих столбцов:

• Фильтры EQUALITY для StateProvinceID и City
• Фильтры РАВЕНСТВА для StateProvinceID и City, INCLUDEPostalCode

Изменение существующего индекса согласно второй рекомендации (индекс с ключами в столбцах StateProvinceID и City, включая PostalCode), скорее всего, будет соответствовать запросам, создавшим оба предложения индексов.

При настройке индексов часто прибегают к компромиссам. Скорее всего, City для многих наборов данных столбец является более выборочным, чем StateProvinceID столбец. Однако, если существующий индекс по StateProvinceID активно используется, а другие запросы в основном выполняют поиск по обоим StateProvinceID и City, то в целом для базы данных менее затратно иметь один индекс с обоими столбцами в ключе, где первым идет StateProvinceID, хотя это и не самый селективный столбец.

Индексы могут быть изменены несколькими способами:

• ИнструкциюCREATE INDEX можно использовать с предложением DROP_EXISTING. Возможно, вы захотите переименовать индексы после изменения, чтобы имя по-прежнему точно отражало определение индекса в зависимости от принятого у вас соглашения об именовании.
• Можно использовать инструкцию DROP INDEX (Transact-SQL), за которой следует инструкция CREATE INDEX Statement.

При объединении предложений индексов порядок их ключей имеет значение: City в качестве начального столбца отличается от StateProvinceID в качестве начального столбца. Дополнительные сведения см. в рекомендациях по проектированию некластеризованных индексов.

Во время создания индексов рассмотрите операции с индексами в режиме "в сети", если они доступны.

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

Проверьте, успешно ли изменён индекс

Важно убедиться, что внесённые вами изменения в индексы дали результат: использует ли оптимизатор запросов ваши индексы?

Проверить изменения индексов можно с помощью хранилища запросов для обнаружения запросов по отсутствующим индексам. Обратите внимание на "query_id" запросов. Воспользуйтесь представлением отслеживаемых запросов в хранилище запросов, чтобы проверить, изменились ли планы выполнения для запроса, а также использует ли оптимизатор новый или измененный индекс. Дополнительные сведения об отслеживаемых запросах см. в разделе Устранение неполадок с производительностью запросов.

Связанный контент

• Руководство по архитектуре и разработке индексов SQL Server и Azure SQ
• sys.dm_db_missing_index_details (Transact-SQL)
• Мониторинг производительности с использованием хранилища запросов