Подсказка в запросе (Transact-SQL)

Изменения: 15 сентября 2007 г.

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

Важно!
Поскольку оптимизатор запросов SQL Server 2005 обычно выбирает наилучший план выполнения для запроса, опытным разработчикам и администраторам базы данных рекомендуется использовать эти подсказки, включая <query_hint>, только в качестве последнего средства.

Область применения:

DELETE;

INSERT;

SELECT;

UPDATE;

Соглашения о синтаксическом обозначении в Transact-SQL

Синтаксис

<query_hint > ::= 
{ { HASH | ORDER } GROUP 
  | { CONCAT | HASH | MERGE } UNION 
  | { LOOP | MERGE | HASH } JOIN 
  | FAST number_rows 
  | FORCE ORDER 
  | MAXDOP number_of_processors 
  | OPTIMIZE FOR ( @variable_name = literal_constant [ , ...n ] ) 
  | PARAMETERIZATION { SIMPLE | FORCED }
  | RECOMPILE
  | ROBUST PLAN 
  | KEEP PLAN 
  | KEEPFIXED PLAN
  | EXPAND VIEWS 
  | MAXRECURSION number 
  | USE PLAN N'xml_plan'
} 

Аргументы

• { HASH |ORDER } GROUP
  Указывает, что агрегаты в предложениях GROUP BY, DISTINCT и COMPUTE запроса должны использовать хэширование или упорядочивание.

• { MERGE |HASH |CONCAT } UNION
  Указывает, что все операции UNION выполняются слиянием, хэшированием или объединением наборов UNION. Если указано несколько подсказок UNION, оптимизатор запросов выбирает наименее затратную стратегию из указанных.

  Примечание.
  Если для любой пары соединяемых таблиц в предложении FROM также указана подсказка <joint_hint>, она имеет приоритет над всеми подсказками <join_hint>, указанными в предложении OPTION.

• { LOOP | MERGE | HASH } JOIN
  Указывает, что все операции соединения во всем запросе выполняются с помощью рекомендаций LOOP JOIN, MERGE JOIN или HASH JOIN. Если указано больше одной подсказки соединения, оптимизатор запросов выбирает наименее затратную стратегию из допустимых.

  Если в одном запросе подсказка соединения также указана для определенной пары таблиц, эта подсказка соединения имеет приоритет в соединении двух таблиц, хотя подсказки в запросе также должны обрабатываться. Таким образом, подсказка соединения для пары таблиц может только ограничивать выбор допустимых методов соединения для подсказки в запросе. Дополнительные сведения см. в разделе Подсказки (Transact-SQL).

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

• FORCE ORDER
  Указывает, что при оптимизации запроса сохраняется порядок объединения, заданный синтаксисом запроса.

  Примечание.
  Использование подсказки FORCE ORDER не влияет на возможный реверс ролей в оптимизаторе запросов. Дополнительные сведения см. в разделе Основные сведения о хэш-соединениях.

  Сведения о том, как оптимизатор запросов SQL Server принудительно использует подсказку FORCE ORDER, если запрос содержит представление, см. в разделе Разрешение представлений.

• MAXDOP number
  Переопределяет параметр конфигурации max degree of parallelism хранимой процедуры sp_configure для запросов, указывающих этот параметр. Подсказка запроса MAXDOP может превышать значение, заданное с помощью процедуры sp_configure. Все семантические правила, используемые параметром конфигурации max degree of parallelism, применимы при использовании подсказки в запросе MAXDOP. Дополнительные сведения см. в разделе Параметр max degree of parallelism.

• OPTIMIZE FOR
  Указывает оптимизатору, что при компиляции и оптимизации запроса нужно использовать конкретное значение для локальной переменной. Значение используется только в процессе оптимизации запроса, но не в процессе выполнения. Подсказка OPTIMIZE FOR может использоваться для отмены определения параметров в оптимизаторе или при создании структуры плана. Дополнительные сведения см. в разделах Перекомпиляция хранимых процедур и Оптимизация запросов в используемых приложениях с помощью руководств планов.

• @variable_name
  Имя локальной переменной, используемой в запросе, которой может быть присвоено значение для использования с подсказкой в запросе OPTIMIZE FOR.

• literal_constant
  Символьная константа, значение которой присваивается аргументу @variable_name для использования в подсказке в запросе OPTIMIZE FOR. Значение literal_constant используется только в процессе оптимизации запроса, а не в качестве значения @variable_name в процессе выполнения запроса. Значение literal_constant может быть любым системным типом данных SQL Server, который может быть выражен как символьная константа. Тип данных значения literal_constant должен неявно приводиться к типу данных, на который ссылается аргумент @variable_name в запросе.

• ,…n
  Указывает, что несколько аргументов @variable_name могут принять значение literal_constant для использования с подсказкой в запросе OPTIMIZE FOR.

• PARAMETERIZATION { SIMPLE | FORCED }
  Указывает правила параметризации SQL Server, которые оптимизатор запросов применяет к запросу при его компиляции.

  Важно!
  Подсказка в запросе PARAMETERIZATION может быть указана только внутри структуры плана. Она не может быть определена напрямую в запросе.

  Значение SIMPLE дает оптимизатору запросов указание использовать параметризацию Простая параметризация. Значение FORCED дает оптимизатору запросов рекомендацию использовать параметризацию Принудительная параметризация. Подсказка в запросе PARAMETERIZATION используется для переопределения текущих настроек параметра PARAMETERIZATION в структуре плана базы данных. Дополнительные сведения см. в разделе Указание механизма параметризации запросов с помощью руководств плана.

• RECOMPILE
  Указывает компоненту SQL Server 2005 Database Engine отбросить план, сформированный после выполнения запроса, заставляя оптимизатор запроса перекомпилировать план запроса при следующем выполнении этого запроса. Без указания подсказки RECOMPILE компонент Database Engine кэширует планы запросов и использует их повторно. При компиляции планов запроса подсказка в запросе RECOMPILE использует текущие значения всех локальных переменных в запросе и, если запрос находится внутри хранимой процедуры, текущие значения для всех параметров.

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

• ROBUST PLAN
  Заставляет оптимизатор запросов использовать план, который работает со строками наибольшего потенциального размера, возможно, с потерей производительности. При обработке запроса промежуточным таблицам и операторам может понадобиться сохранять и обрабатывать строки, которые шире, чем любые из входных строк. Строки могут быть настолько широки, что иногда некоторые операторы не смогут их обработать. Когда это происходит, компонент Database Engine возвращает ошибку при выполнении запроса. С помощью подсказки ROBUST PLAN оптимизатору запросов дается указание не выбирать ни один из планов запросов, который может вызвать проблему.

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

• KEEP PLAN
  Заставляет оптимизатор запросов снизить приблизительный порог повторной компиляции для запроса. Полученное пороговое значение — это точка, в которой запрос автоматически перекомпилируется, если в таблице с помощью инструкций UPDATE, DELETE или INSERT было произведено оценочное количество изменений индексированных столбцов. Указывая подсказку KEEP PLAN, убедитесь, что запрос не будет часто перекомпилирован при выполнении множественных обновлений в таблице.

• KEEPFIXED PLAN
  Принуждает оптимизатор запросов не перекомпилировать запрос при изменении статистики. Указывая подсказку KEEPFIXED PLAN, убедитесь, что запрос будет перекомпилирован только при изменении схемы базовых таблиц или если по отношению к ним выполнена процедура sp_recompile.

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

  Эта подсказка в запросе виртуально запрещает прямое использование индексированных представлений и индексов для индексированных представлений в плане запроса.

  Индексированное представление не разворачивается только в том случае, если на представление существует прямая ссылка в части SELECT запроса и определены подсказки WITH (NOEXPAND) или WITH (NOEXPAND, INDEX( index_val [ ,...n ] ) ). Дополнительные сведения о подсказке в запросе WITH (NOEXPAND) см. в разделе FROM (Transact-SQL).

  Влиянию этой подсказки подвержены только представления в части SELECT инструкций, включая находящиеся в инструкциях INSERT, UPDATE и DELETE.

• MAXRECURSION number
  Указывает максимально допускаемое количество рекурсий для запроса. Число number — неотрицательное целое, находящееся в диапазоне между 0 и 32767. Если указан 0, ограничения не применяются. Если этот параметр не указан, ограничение по умолчанию равно 100.

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

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

  Дополнительные сведения см. в разделе WITH общее_табличное_выражение (Transact-SQL).

• USE PLAN N**'xml_plan'**
  Принуждает оптимизатор запросов использовать существующий план запроса для запроса, определенного параметром 'xml_plan'. Дополнительные сведения см. в разделе Указание планов запросов с помощью форсирования планов. Подсказка запроса USE PLAN не может быть указана для инструкций INSERT, UPDATE и DELETE.

Замечания

Подсказки в запросе нельзя указывать в инструкции INSERT, кроме случая, когда внутри инструкции используется предложение SELECT.

Подсказки в запросе можно указывать только в запросах верхнего уровня, но не во вложенных запросах.

Примеры

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

В следующем примере указывается, что операция JOIN в запросе выполняется с подсказкой MERGE JOIN.

USE AdventureWorks;
GO
SELECT * 
FROM Sales.Customer AS c
INNER JOIN Sales.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
WHERE TerritoryID = 5
OPTION (MERGE JOIN);
GO

Б. Использование подсказки OPTIMIZE FOR

В следующем пример оптимизатору запросов дается указание использовать значение 'Seattle' для локальной переменной @city_name при оптимизации запроса.

DECLARE @city_name nvarchar(30)
SET @city_name = 'Ascheim'
SELECT * FROM Person.Address
WHERE City = @city_name
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle') );
GO

В. Использование подсказки MAXRECURSION

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

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

После исправления ошибки в коде подсказка MAXRECURSION больше не нужна.

Г. Использование подсказки UNION

В следующем примере используется подсказка в запросе MERGE UNION.

USE AdventureWorks ;
GO
SELECT *
FROM HumanResources.Employee e1
UNION
SELECT *
FROM HumanResources.Employee e2
OPTION (MERGE UNION) ;
GO

Д. Использование подсказок HASH GROUP и FAST

В следующем примере используется подсказка в запросе HASH GROUP и FAST.

USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (HASH GROUP, FAST 10) ;
GO

Е. Использование подсказки MAXDOP

В следующем примере используется подсказка в запросе MAXDOP.

USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (MAXDOP 2);
GO

См. также

Справочник

Подсказки (Transact-SQL)

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

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

Журнал изменений