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

  • Статья

Использование подсказок в запросах INDEX и FORCESEEK в структурах планов

Можно указать табличные подсказки INDEX и FORCESEEK в качестве подсказок в запросе. При указании их как подсказок в запросе они ведут себя так же, как и встроенные табличные подсказки или подсказки представлений.

Подсказка INDEX вынуждает оптимизатор запросов использовать только указанные индексы для доступа к данным упоминаемой таблицы или упоминаемого представления в запросе. Подсказка FORCESEEK вынуждает оптимизатор запросов использовать только операцию поиска по индексу для доступа к данным ссылочной таблицы или ссылочного представления в запросе. Эти подсказки можно использовать в предложении OPTION структуры плана, чтобы оптимизировать запрос. Если запрос совпадает со структурой плана, предложение OPTION, указанное в структуре плана, добавляется к запросу прежде, чем запрос подвергается компиляции и оптимизации. Дополнительные сведения о структурах плана см. в разделе Основные сведения о структурах планов.

ПредупреждениеВнимание!

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

Табличные подсказки INDEX и FORCESEEK, указанные в качестве подсказки в запросе, допустимы для следующих объектов:

  • Таблицы

  • Представления

  • Индексированные представления

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

  • Динамические административные представления

  • Именованные вложенные запросы

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

Чтобы указать подсказку индекса для индексированного представления, в предложении OPTION также должна быть указана подсказка NOEXPAND, иначе подсказка индекса будет пропущена. Дополнительные сведения см. в разделе Разрешение индексов для представлений.

Дополнительные сведения о синтаксисе, используемом для указания подсказок INDEX и FORCESEEK в качестве подсказок в запросе, см. в разделе Подсказки в запросах (Transact-SQL).

Рекомендации

Рекомендуются следующие методы:

  • Подсказки INDEX и FORCESEEK следует использовать в качестве подсказок в запросе только в контексте структуры плана или в нерегламентированных запросах при проверке инструкций структуры плана. Для всех других нерегламентированных запросов эти подсказки следует указывать как табличные подсказки.

  • Перед использованием подсказки FORCESEEK следует убедиться, что статистика базы данных актуальна и точна.

    Актуальная статистика позволяет оптимизатору точно оценить стоимость разных планов запросов и выбрать высококачественный план. Поэтому рекомендуется установить параметры AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS в значение ON (по умолчанию) для каждой пользовательской базы данных. Также можно вручную обновить статистику по таблице или представлению с помощью инструкции UPDATE STATISTICS.

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

  • Не используйте подсказку INDEX (0) вместе с подсказкой FORCESEEK. Подсказка INDEX (0) запускает принудительный просмотр базовой таблицы. При использовании с подсказкой FORCESEEK план не находится и возвращается ошибка 8622.

  • Не используйте подсказку в запросе USE PLAN вместе с подсказкой FORCESEEK. Подсказка FORCESEEK в этом случае не будет обрабатываться.

Совместное использование подсказок INDEX и FORCESEEK с другими табличными подсказками

Подсказки INDEX и FORCESEEK могут быть указаны для запроса, который не имеет существующих табличных подсказок. Кроме того, ими можно заменить одну или несколько уже существующих подсказок INDEX или FORCESEEK в этом запросе. Если в запросе, сопоставленном со структурой плана, уже присутствует предложение WITH, указывающее табличные подсказки, то подсказки в запросе, указанные в параметре @hints структуры плана, заменяют аналогичные в этом запросе. Например, если запрос содержит табличную подсказку WITH INDEX (PK_Employee_EmployeeID) для таблицы HumanResources.Employee, а параметр @hints в структуре плана задает OPTION (TABLE HINT ( HumanResources.Employee, INDEX( IX_Employee_ManagerID ) ), оптимизатор запросов будет использовать индекс IX_Employee_ManagerID.

Табличные подсказки, отличные от INDEX и FORCESEEK, запрещено применять в качестве подсказок в запросе в структуре плана, за исключением запросов, в которых уже есть предложение WITH, указывающее табличную подсказку. В этом случае, чтобы сохранить семантику запроса, необходимо также указать соответствующую табличную подсказку в качестве подсказки в запросе, задав в предложении OPTION ключевое слово TABLE HINT. Например, если запрос содержит табличную подсказку NOLOCK, предложение OPTIONS в параметре @hints структуры плана также должно содержать подсказку NOLOCK, наряду с любой табличной подсказкой INDEX или FORCESEEK. См. пример В далее в этом разделе. Если указать табличную подсказку, отличную от INDEX или FORCESEEK, с использованием TABLE HINT в предложении OPTION без совпадающей подсказки в запросе (или наоборот), то будет возвращена ошибка 8702, сигнализирующая, что предложение OPTION может вызвать изменение в семантике запроса и запрос завершится с ошибкой.

Совместное использование подсказок INDEX и FORCESEEK с другими подсказками в запросе

Если в запросе, сопоставленном со структурой плана, уже присутствует предложение OPTION, указывающее подсказки в запросе, то подсказки в запросе, указанные в параметре @hints структуры плана, заменяют аналогичные в запросе. Чтобы структура плана совпала с запросом, уже содержащим предложение OPTION, необходимо включить предложение OPTION запроса при указании текста запроса для сопоставления в инструкции sp_create_plan_guide (Transact-SQL). Если необходимо, чтобы подсказки, указанные в структуре плана, были добавлены к подсказкам, которые уже существуют в запросе, вместо того, чтобы заменить их, следует указать и исходные, и дополнительные подсказки в предложении OPTION структуры плана.

Примеры

А. Использование подсказок FORCESEEK

В следующем примере в параметре @hints структуры плана используется подсказка FORCESEEK. Этот параметр вынуждает оптимизатор запросов использовать для доступа к данным таблицы HumanResources.Employee только операцию поиска по индексу. Следует заметить, что это может вынудить оптимизатор использовать индекс, отличный от указанного в табличной подсказке.

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide3', 
    @stmt = N'SELECT c.LastName, c.FirstName, HumanResources.Employee.Title
              FROM HumanResources.Employee
              JOIN Person.Contact AS c ON HumanResources.Employee.ContactID = c.ContactID
              WHERE HumanResources.Employee.ManagerID = 3
              ORDER BY c.LastName, c.FirstName;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT( HumanResources.Employee, FORCESEEK))';
GO

Б. Использование нескольких табличных подсказок

В следующем примере к одной таблице применяется подсказка INDEX, а к другой — подсказка FORCESEEK.

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide4', 
    @stmt = N'SELECT e.ManagerID, c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_ManagerID ) ) 
                       , TABLE HINT ( c, FORCESEEK) )';
GO

В. Указание подсказок, изменяющих семантику

В следующем примере запрос содержит две табличные подсказки: NOLOCK, которая изменяет семантику, и INDEX, которая не изменяет семантику. Чтобы сохранить семантику запроса, подсказка NOLOCK указывается в предложении OPTIONS структуры плана. Помимо подсказки NOLOCK, указываются подсказки INDEX и FORCESEEK, которые замещают не изменяющую семантику подсказку INDEX в запросе, когда инструкция компилируется и оптимизируется.

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide6', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
                   WITH (NOLOCK, INDEX (PK_Employee_EmployeeID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_ManagerID) , NOLOCK, FORCESEEK ))';
GO

В следующем примере показывается альтернативный метод сохранения семантики запроса, который также позволяет оптимизатору выбирать индекс, отличный от индекса, указанного в табличной подсказке. Это делается путем указания подсказки NOLOCK в предложении OPTIONS (поскольку она изменяет семантику) и указания ключевого слова TABLE HINT только со ссылкой на таблицу, без подсказки INDEX.

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide7', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
                   WITH (NOLOCK, INDEX (PK_Employee_EmployeeID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 2;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, NOLOCK))';
GO

Г. Использование конструкции TABLE HINT для замещения существующей табличной подсказки

В следующем примере показывается использование конструкции TABLE HINT без указания подсказки INDEX для замещения поведения табличной подсказки INDEX, указанной в предложении FROM запроса. Этот метод позволяет оптимизатору выбрать индекс, отличный от указанного в табличной подсказке.

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide5', 
    @stmt = N'SELECT e.ManagerID, c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_ManagerID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e))';
GO