Подсказки в запросах (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 { UNKNOWN | = literal_constant } [ , ...n ] )
                  | OPTIMIZE FOR UNKNOWN
                  | PARAMETERIZATION { SIMPLE | FORCED }
                  | RECOMPILE
                  | ROBUST PLAN 
                  | KEEP PLAN 
                  | KEEPFIXED PLAN
                  | EXPAND VIEWS 
                  | MAXRECURSION number
                    
                  | USE PLAN N'xml_plan'| TABLE HINT ( exposed_object_name  [ , <table_hint> [ [, ]...n ] ] )<table_hint> ::=
                [ NOEXPAND ] { 
                    INDEX (index_value [ ,...n ] ) | INDEX = (index_value)
                  | FASTFIRSTROW 
                  | FORCESEEK
                  | HOLDLOCK 
                  | NOLOCK 
                  | NOWAIT
                  | PAGLOCK 
                  | READCOMMITTED 
                  | READCOMMITTEDLOCK 
                  | READPAST 
                  | READUNCOMMITTED 
                  | REPEATABLEREAD 
                  | ROWLOCK 
                  | SERIALIZABLE 
                  | TABLOCK 
                  | TABLOCKX 
                  | UPDLOCK 
                  | XLOCK 
                }

Аргументы

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

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

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

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

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

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

  Примечание
  Инструкция MERGE получает доступ вначале к исходной таблице, затем к целевой в порядке соединения, принятом по умолчанию, если не задано предложение WHEN SOURCE NOT MATCHED. Если указать FORCE ORDER, сохраняется поведение по умолчанию.

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

• MAXDOP number
  Переопределяет параметр конфигурации max degree of parallelismsp_configure и регулятор ресурсов для запроса, уточняющего этот параметр. Подсказка в запросе MAXDOP может превысить значение, заданное с помощью процедуры sp_configure. Если MAXDOP превышает значение, настроенное с помощью регулятора ресурсов, компонент Database Engine использует значение MAXDOP регулятора ресурсов, описанное в ALTER WORKLOAD GROUP (Transact-SQL). Все семантические правила, используемые параметром конфигурации max degree of parallelism, применимы при использовании подсказки в запросе MAXDOP. Дополнительные сведения см. в разделе Параметр max degree of parallelism.

  Внимание!
  Если значение MAXDOP равно нулю, то сервер выбирает максимальную степень параллелизма. Сервер создает внутреннее ограничение DOP на уровне 64.

• OPTIMIZE FOR ( @variable\_name { UNKNOWN | = literal_constant } [ , ...n ] )
  Указывает оптимизатору запросов, что при компиляции и оптимизации запроса нужно использовать конкретное значение для локальной переменной. Значение используется только в процессе оптимизации запроса, но не в процессе выполнения.

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

  • UNKNOWN
    Указывает, что оптимизатор запросов использует статистические данные вместо начального значения, чтобы определить значение локальной переменной при оптимизации запроса.

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

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

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

  Если подсказки OPTIMIZE FOR @variable\_name = literal_constant и OPTIMIZE FOR UNKNOWN используются в подсказке в одном запросе, оптимизатор запросов будет использовать аргумент literal_constant, указанный для конкретного значения, и UNKNOWN — для оставшихся значений переменных. Значения используются только в процессе оптимизации запроса, но не в процессе выполнения.

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

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

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

• RECOMPILE
  Указывает компоненту SQL Server 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_value [ ,...n ] ) ). Дополнительные сведения о подсказке в запросе WITH (NOEXPAND) см. в разделе FROM.

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

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

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

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

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

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

• TABLE HINT (exposed_object_name [ , <table_hint> [ [, ]...n ] ] )
  Применяет заданную табличную подсказку к таблице или представлению, соответствующему аргументу exposed_object_name. Табличные подсказки рекомендуется использовать в качестве подсказок в запросах только в контексте структуры плана.

  Аргумент exposed_object_name может представлять одну из следующих ссылок.

  • Если в предложении FROM запроса используется псевдоним таблицы или представления, этим псевдонимом является exposed_object_name.

  • Если псевдоним не используется, exposed_object_name является точным соответствием таблицы или представления, на которые ссылается предложение FROM. Например, если в таблице или представлении имеется ссылка с двухкомпонентным именем, аргумент exposed_object_name представляет собой это двухкомпонентное имя.

  Если аргумент exposed_object_name задан без указания табличной подсказки, все индексы, заданные в запросе как часть табличной подсказки, не учитываются, а использование индексов определяется оптимизатором запроса. Этот метод можно использовать для устранения влияния табличной подсказки INDEX, если невозможно изменить первоначальный запрос. См. пример К.

• <табличная_подсказка> ::= { [ NOEXPAND ] { INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value ) | FASTFIRSTROW | FORCESEEK | HOLDLOCK | NOLOCK | NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK | XLOCK }
  Табличная подсказка, применяемая в качестве подсказки в запросе к таблице или представлению, которое соответствует аргументу exposed_object_name . Описание этих подсказок см. в разделе Табличные подсказки (Transact-SQL).

  Табличные подсказки, за исключением INDEX и FORCESEEK, не допускаются в качестве подсказок в запросах, кроме тех случаев, когда в запросе уже содержится предложение WITH, задающее табличную подсказку. Дополнительные сведения см. ниже, в разделе «Примечания».

Замечания

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

Подсказки в запросе можно указывать только в запросах верхнего уровня, но не во вложенных запросах. Если табличная подсказка задана в качестве подсказки в запросе, ее можно указать в запросе верхнего уровня или во вложенном запросе. Тем не менее, значение аргумента exposed_object_name в предложении TABLE HINT должно точно соответствовать видимому имени в запросе или вложенном запросе.

Определение табличных подсказок как подсказок в запросе

Примеры

А. Использование подсказки 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

USE AdventureWorks;
GO
DECLARE @city_name nvarchar(30);
DECLARE @postal_code nvarchar(15);
SET @city_name = 'Ascheim';
SET @postal_code = 86171;
SELECT * FROM Person.Address
WHERE City = @city_name AND PostalCode = @postal_code
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );
GO

В. Использование подсказки 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

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

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

Д. Использование подсказок 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

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

Ж. Использование подсказки INDEX

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
              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, INDEX (IX_Employee_ManagerID)))';
GO
EXEC sp_create_plan_guide 
    @name = N'Guide2', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e
              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, INDEX(PK_Employee_EmployeeID, IX_Employee_ManagerID)))';
GO

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

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

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

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

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

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

Л. Указание табличных подсказок, влияющих на семантику

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

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

См. также

Справочник

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