Указания (Transact-SQL) — запросы

Применятся к:yesSQL Server (все поддерживаемые версии) YesБаза данных SQL Azure

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

Внимание!

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

Применимо к:

DELETE

INSERT

SELECT

UPDATE

MERGE

Синтаксис

<query_hint> ::=   
{ { HASH | ORDER } GROUP   
  | { CONCAT | HASH | MERGE } UNION   
  | { LOOP | MERGE | HASH } JOIN   
  | DISABLE_OPTIMIZED_PLAN_FORCING 
  | EXPAND VIEWS   
  | FAST <integer_value>   
  | FORCE ORDER   
  | { FORCE | DISABLE } EXTERNALPUSHDOWN
  | { FORCE | DISABLE } SCALEOUTEXECUTION
  | IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX  
  | KEEP PLAN   
  | KEEPFIXED PLAN  
  | MAX_GRANT_PERCENT = <numeric_value>  
  | MIN_GRANT_PERCENT = <numeric_value>  
  | MAXDOP <integer_value>   
  | MAXRECURSION <integer_value>   
  | NO_PERFORMANCE_SPOOL   
  | OPTIMIZE FOR ( @variable_name { UNKNOWN | = <literal_constant> } [ , ...n ] )  
  | OPTIMIZE FOR UNKNOWN  
  | PARAMETERIZATION { SIMPLE | FORCED }   
  | QUERYTRACEON <integer_value>   
  | RECOMPILE  
  | ROBUST PLAN   
  | USE HINT ( <use_hint_name> [ , ...n ] )
  | USE PLAN N'<xml_plan>'  
  | TABLE HINT ( <exposed_object_name> [ , <table_hint> [ [, ]...n ] ] )  
}  
  
<table_hint> ::=  
{ NOEXPAND [ , INDEX ( <index_value> [ ,...n ] ) | INDEX = ( <index_value> ) ]  
  | INDEX ( <index_value> [ ,...n ] ) | INDEX = ( <index_value> )
  | FORCESEEK [ ( <index_value> ( <index_column_name> [,... ] ) ) ]  
  | FORCESCAN  
  | HOLDLOCK   
  | NOLOCK   
  | NOWAIT  
  | PAGLOCK   
  | READCOMMITTED   
  | READCOMMITTEDLOCK   
  | READPAST   
  | READUNCOMMITTED   
  | REPEATABLEREAD   
  | ROWLOCK   
  | SERIALIZABLE   
  | SNAPSHOT  
  | SPATIAL_WINDOW_MAX_CELLS = <integer_value>  
  | TABLOCK   
  | TABLOCKX   
  | UPDLOCK   
  | XLOCK  
}  

<use_hint_name> ::=
{ 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'
  | 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'
  | 'ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES'
  | 'ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES'
  | 'DISABLE_BATCH_MODE_ADAPTIVE_JOINS'
  | 'DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'
  | 'DISABLE_DEFERRED_COMPILATION_TV'
  | 'DISABLE_INTERLEAVED_EXECUTION_TVF'
  | 'DISABLE_OPTIMIZED_NESTED_LOOP'
  | 'DISABLE_OPTIMIZER_ROWGOAL'
  | 'DISABLE_PARAMETER_SNIFFING'
  | 'DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'
  | 'DISABLE_TSQL_SCALAR_UDF_INLINING'
  | 'DISALLOW_BATCH_MODE'
  | 'ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS'
  | 'ENABLE_QUERY_OPTIMIZER_HOTFIXES'
  | 'FORCE_DEFAULT_CARDINALITY_ESTIMATION'
  | 'FORCE_LEGACY_CARDINALITY_ESTIMATION'
  | 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n'
  | 'QUERY_PLAN_PROFILE' 
}

Примечание

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

{ HASH |ORDER } GROUP

Указывает, что агрегаты, описываемые в предложениях GROUP BY или DISTINCT в запросе, должны использовать хэширование или упорядочивание.

{ MERGE | HASH | CONCAT } UNION

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

{ LOOP | MERGE | HASH } JOIN

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

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

DISABLE_OPTIMIZED_PLAN_FORCING

ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server (начиная с SQL Server 2022 (16.x), предварительная версия)

Отключает принудительное выполнение оптимизированного плана для запроса.

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

EXPAND VIEWS

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

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

Примечание

Индексированное представление сохраняет сокращенный вид, если на это представление есть прямая ссылка в части SELECT запроса. Представление также останется сжатым, если указаны WITH (NOEXPAND) или WITH (NOEXPAND, INDEX( <index_value> [ ,...n ] ) ). Дополнительные сведения об указании запроса NOEXPAND см. в разделе Использование NOEXPAND.

Это указание действует только на представления в части SELECT инструкции, в том числе в инструкциях INSERT, UPDATE, MERGE и DELETE.

FAST <integer_value>

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

FORCE ORDER

Указывает, что при оптимизации запроса сохраняется порядок соединения, заданный синтаксисом запроса. Использование FORCE ORDER не влияет на возможный реверс ролей в оптимизаторе запросов.

Примечание

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

{ FORCE | DISABLE } EXTERNALPUSHDOWN

Принудительная передача или отключение передачи вычислений соответствующих выражений в Hadoop. Применяется только к запросам, использующим PolyBase. Не выполняет отправку в хранилище Azure.

{ FORCE | DISABLE } SCALEOUTEXECUTION

Принудительное применение или отключение горизонтального увеличения масштаба выполнения запросов PolyBase с использованием внешних таблиц в кластерах больших данных SQL Server 2019. Это указание будет учитываться только запросом, использующим главный экземпляр кластера больших данных SQL. Горизонтальное увеличение масштаба будет выполняться в пределах пула вычислений кластера больших данных.

KEEP PLAN

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

  • UPDATE
  • DELETE
  • MERGE
  • INSERT

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

KEEPFIXED PLAN

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

IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX

Применимо к: SQL Server (начиная с SQL Server 2012 (11.x)).

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

MAX_GRANT_PERCENT = <numeric_value>

Применимо к: SQL Server (начиная с SQL Server 2016 (13.x);) и База данных SQL Azure.

Максимальный объем предоставленной памяти (PERCENT) от заданного ограничения. Запрос гарантированно не превышает это ограничение. Реальное ограничение может быть ниже, если значение параметра Resource Governor ниже значения в этом указании. Допустимые значения — от 0 до 100.

MIN_GRANT_PERCENT = <numeric_value>

Применимо к: SQL Server (начиная с SQL Server 2016 (13.x);) и База данных SQL Azure.

Минимальный объем предоставленной памяти (PERCENT) от заданного ограничения. Запрос гарантированно получает MAX(required memory, min grant), так как для запуска запроса требуется определенный минимальный объем памяти. Допустимые значения — от 0 до 100.

MAXDOP <integer_value>

Применимо к: SQL Server (начиная с SQL Server 2008) и База данных SQL Azure.

Переопределяет параметр конфигурации, задающий максимальный уровень параллелизма, в sp_configure. Также переопределяет Resource Governor для запроса, в котором указан этот параметр. Указание запроса MAXDOP может превысить значение, заданное с помощью процедуры sp_configure. Если MAXDOP превышает значение, настроенное с помощью Resource Governor, ядро СУБД использует значение MAXDOP из Resource Governor, как описано в разделе ALTER WORKLOAD GROUP (Transact-SQL). Все семантические правила, используемые параметром конфигурации max degree of parallelism, применимы при использовании подсказки в запросе MAXDOP. Дополнительные сведения см. в разделе Настройка параметра конфигурации сервера max degree of parallelism.

Предупреждение

Если значение MAXDOP равно нулю, сервер выбирает максимальную степень параллелизма.

MAXRECURSION <integer_value>

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

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

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

Дополнительные сведения см. в статье WITH common_table_expression (Transact-SQL).

NO_PERFORMANCE_SPOOL

Применимо к: SQL Server (начиная с SQL Server 2016 (13.x);) и База данных SQL Azure.

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

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. Дополнительные сведения см. в разделе Перекомпиляция хранимой процедуры.

OPTIMIZE FOR UNKNOWN

Указывает, что во время компиляции и оптимизации запроса оптимизатор запросов должен использовать среднюю избирательность предиката по всем значениям столбцов вместо значения параметра времени выполнения.

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

PARAMETERIZATION { SIMPLE | FORCED }

Указывает правила параметризации, которые оптимизатор запросов SQL Server применяет к запросу при его компиляции.

Важно!

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

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

QUERYTRACEON <integer_value>

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

Примечание

Но этот параметр не возвращает сообщение об ошибке или предупреждение, если используется неподдерживаемый номер флага трассировки. Если указанный флаг трассировки не относится к плану выполнения запроса, параметр будет игнорироваться без уведомления.

Примечание

Чтобы использовать в запросе несколько флагов трассировки, укажите одно указание QUERYTRACEON для каждого номера флага трассировки.

RECOMPILE

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

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

ROBUST PLAN

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

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

USE HINT ( 'hint_name' )

Применимо к: SQL Server (начиная с SQL Server 2016 (13.x); с пакетом обновления 1 (SP1)) и База данных SQL Azure.

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

Поддерживаются следующие имена подсказок:

  • 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'
    Побуждает SQL Server создать план запроса с допущением простого вложения вместо допущения базового вложения по умолчанию для соединений в модели оценки кратности оптимизатора запросов версии SQL Server 2014 (12.x) или более поздних. Это указание эквивалентно флагу трассировки 9476.

  • 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'
    Побуждает SQL Server создавать план с минимальной избирательностью при оценке предикатов AND для фильтров в случае полной корреляции. Это указание эквивалентно флагу трассировки 4137 при использовании модели оценки кратности в SQL Server 2012 (11.x) и более ранних версиях и имеет тот же эффект, что и при использовании флага трассировки 9471 с моделью оценки кратности в SQL Server 2014 (12.x) или более поздних версиях.

  • 'ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES'
    Побуждает SQL Server создавать план с максимальной избирательностью при оценке предикатов AND для фильтров в случае полной независимости. Это указание эквивалентно поведению по умолчанию при использовании модели оценки кратности в SQL Server 2012 (11.x) и более ранних версиях и аналогично флагу трассировки 9472 при использовании с моделью оценки кратности в SQL Server 2014 (12.x) или более поздних версиях.
    Область применения: База данных SQL Azure

  • 'ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES'
    Побуждает SQL Server создавать план с использованием от наибольшей до наименьшей избирательности при оценке предикатов AND для фильтров в случае частичной корреляции. Это указание эквивалентно поведению по умолчанию при использовании модели оценки кратности в SQL Server 2014 (12.x) или более поздних версиях.
    Область применения: База данных SQL Azure

  • "DISABLE_BATCH_MODE_ADAPTIVE_JOINS"
    Отключает адаптивные соединения в пакетном режиме. Дополнительные сведения: Адаптивные соединения в пакетном режиме.
    Применимо к: SQL Server (начиная с SQL Server 2017 (14.x);) и База данных SQL Azure

  • "DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK"
    Отключает обратную связь по временно предоставляемому буферу памяти в пакетном режиме. Дополнительные сведения см. в разделе Обратная связь по временно предоставляемому буферу памяти в пакетном режиме.
    Применимо к: SQL Server (начиная с SQL Server 2017 (14.x);) и База данных SQL Azure

  • "DISABLE_DEFERRED_COMPILATION_TV"
    Отключает отложенную компиляцию табличных переменных. См. дополнительные сведения об отложенной компиляции табличных переменных.
    Применимо к: SQL Server (начиная с SQL Server 2019 (15.x)) и База данных SQL Azure

  • "DISABLE_INTERLEAVED_EXECUTION_TVF"
    Отключает выполнение с чередованием для функций с табличным значением с несколькими инструкциями. Дополнительные сведения см. в разделе о выполнении с чередованием для функций с табличным значением с несколькими инструкциями.
    Применимо к: SQL Server (начиная с SQL Server 2017 (14.x);) и База данных SQL Azure

  • 'DISABLE_OPTIMIZED_NESTED_LOOP'
    Заставляет обработчик запросов не использовать операцию сортировки (сортировки пакетов) для оптимизации соединений вложенного цикла при формировании плана запроса. Это указание эквивалентно флагу трассировки 2340.

  • 'DISABLE_OPTIMIZER_ROWGOAL'
    Указывает, что SQL Server должен создать план без использования изменений целей строк с запросами, содержащими следующие ключевые слова:

    • В начало
    • OPTION (FAST N);
    • IN
    • EXISTS

    Это указание эквивалентно флагу трассировки 4138.

  • 'DISABLE_PARAMETER_SNIFFING'
    Указывает, что оптимизатор запросов должен использовать среднее распределение данных при компиляции запроса с одним или несколькими параметрами. Эта инструкция делает план запроса независимым от значения параметра, которое было использовано при первой компиляции запроса. Это указание эквивалентно флагу трассировки 4136 или параметру конфигурации области баз данныхPARAMETER_SNIFFING = OFF.

  • "DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK"
    Отключает обратную связь по временно предоставляемому буферу памяти в строковом режиме. Дополнительные сведения см. в разделе Обратная связь по временно предоставляемому буферу памяти в строковом режиме.
    Применимо к: SQL Server (начиная с SQL Server 2019 (15.x)) и База данных SQL Azure

  • "DISABLE_TSQL_SCALAR_UDF_INLINING"
    Отключает встраивание скалярных пользовательских функций. Дополнительные сведения: Встраивание скалярной функции, определяемой пользователем.
    Применимо к: SQL Server (начиная с SQL Server 2019 (15.x)) и База данных SQL Azure

  • "DISALLOW_BATCH_MODE"
    Отключает выполнение в пакетном режиме. Дополнительные сведения см. в разделе Режимы выполнения.
    Применимо к: SQL Server (начиная с SQL Server 2019 (15.x)) и База данных SQL Azure

  • 'ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS'
    Позволяет использовать автоматически созданную быструю статистику (поправку к гистограмме) для любого начального столбца индекса, для которого требуется оценить кратность. Гистограмма, используемая для оценки кратности, будет так откорректирована во время компиляции запроса, чтобы учитывать фактическое максимальное или минимальное значение этого столбца. Это указание эквивалентно флагу трассировки 4139.

  • 'ENABLE_QUERY_OPTIMIZER_HOTFIXES'
    Включает исправления в оптимизаторе запросов, выпущенные в накопительных пакетах обновления и пакетах обновления SQL Server. Это указание эквивалентно флагу трассировки 4199 или параметру конфигурации области баз данныхQUERY_OPTIMIZER_HOTFIXES = ON.

  • 'FORCE_DEFAULT_CARDINALITY_ESTIMATION'
    Заставляет оптимизатор запросов использовать модель оценки кратности, которая соответствует текущему уровню совместимости базы данных. Используйте это указание для переопределения параметра конфигурации области баз данныхLEGACY_CARDINALITY_ESTIMATION = ON или флага трассировки 9481.

  • 'FORCE_LEGACY_CARDINALITY_ESTIMATION'
    Заставляет оптимизатор запросов использовать модель оценки кратности для SQL Server 2012 (11.x) и более ранних версий. Это указание эквивалентно флагу трассировки 9481 или параметру конфигурации области баз данныхLEGACY_CARDINALITY_ESTIMATION = ON.

  • 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n'
    Принудительно изменяет поведение оптимизатора запросов на уровне запроса. Оптимизация выполняется так, как если бы запрос компилировался с уровнем совместимости базы данных n, где n — максимальный поддерживаемый уровень совместимости базы данных (например, 100, 130 и т. д.). Список значений, поддерживаемых сейчас для n, см. здесь: sys.dm_exec_valid_use_hints.
    Применимо к: SQL Server (начиная с SQL Server 2017 (14.x); с накопительным пакетом обновления 10 (CU10)) и База данных SQL Azure.

    Примечание

    Указание QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n не переопределяет значение по умолчанию или унаследованное значение параметра оценки кратности, если оно указано в конфигурации области базы данных, с помощью флага трассировки или другого указания запроса, например QUERYTRACEON.
    Это указание влияет только на поведение оптимизатора запросов. Оно не влияет на другие функции SQL Server, которые могут зависеть от уровня совместимости базы данных, в том числе на доступность определенных функций базы данных.
    Дополнительные сведения об этом указании см. в разделе Developer’s Choice: Hinting Query Execution model (Выбор разработчика: модель выполнения запроса указания).

  • 'QUERY_PLAN_PROFILE'
    Включает упрощенное профилирование для запроса. Когда завершается запрос, содержащий это новое указание, вызывается новое расширенное событие query_plan_profile. Это расширенное событие предоставляет статистику выполнения и фактический план выполнения XML (подобно расширенному событию query_post_execution_showplan, но только для запросов, содержащих новое указание).
    Применимо к: SQL Server (начиная с SQL Server 2016 (13.x); с пакетом обновления 2 (SP2) и накопительным пакетом обновления 3 (CU3) и SQL Server 2017 (14.x); с накопительным пакетом обновления 11 (CU11)).

    Примечание

    Если включен сбор расширенных событий query_post_execution_showplan, в каждый запрос, который выполняется на сервере, будет добавлена стандартная инфраструктура профилирования. Это может повлиять на общую производительность сервера.
    Если вместо этого вы включите сбор расширенных событий query_thread_profile для использования упрощенной инфраструктуры профилирования, издержки производительности будут гораздо ниже, но по-прежнему могут влиять на производительность сервера.
    Если вы включите расширенное событие query_plan_profile, упрощенная инфраструктура профилирования будет применяться только к запросам, которые выполняются с указанием QUERY_PLAN_PROFILE, и не повлияет на другие рабочие нагрузки на сервере. Используйте это указание для профилирования конкретного запроса, не влияя на другие части рабочей нагрузки сервера. Дополнительные сведения об облегченном профилировании см. в разделе Инфраструктура профилирования запросов.

Список всех поддерживаемых имен USE HINT можно запросить с помощью динамического административного представления sys.dm_exec_valid_use_hints.

Совет

В именах указаний учитывается регистр.

Важно!

Некоторые указания USE HINT могут конфликтовать с флагами трассировки, включенными на глобальном уровне или уровне сеанса, или параметрами конфигурации области баз данных. В этом случае приоритет всегда имеет указание уровня запроса (USE HINT). Если USE HINT конфликтует с другим указанием запроса или флагом трассировки, включенным на уровне запроса (например, с помощью QUERYTRACEON), при попытке выполнить запрос SQL Server выведет ошибку.

USE PLAN N'<xml_plan>'

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

Полученный при применении этой возможности план выполнения будет таким же, как принудительно применяемый план, или очень близким к нему. Так как итоговый план может не совпадать с планом, указанным в инструкции USE PLAN, производительность этих планов может различаться. В редких случаях возможна значительная негативная разница в производительности, и тогда администратору следует удалить принудительный план.

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, если нет возможности изменить первоначальный запрос. См. пример К.

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

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

Внимание!

Указав FORCESEEK с параметрами, вы ограничите число планов, которые может использовать оптимизатор запросов. Из-за этого может чаще возникать ошибка "Невозможно сформировать план". В будущем выпуске внутренние изменения оптимизатора запросов могут привести к увеличению числа этих планов.

Remarks

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

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

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

Табличные указания INDEX, FORCESCAN или FORCESEEK рекомендуется использовать в качестве указаний запроса только в контексте структуры плана. Структуры планов полезны, когда нет возможности изменить первоначальный запрос, например, если он является приложением стороннего разработчика. Указание запроса, заданное в структуре плана, добавляется к запросу перед его компиляцией и оптимизацией. В автоматизированных запросах предложение TABLE HINT используется только при тестировании инструкций структуры планов. Для всех других нерегламентированных запросов рекомендуется задавать эти указания только как табличные.

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

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

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

Табличные указания, за исключением INDEX, FORCESCAN и FORCESEEK, не могут использоваться как указания запроса, кроме тех случаев, когда в запросе уже содержится предложение WITH, задающее табличное указание. В этом случае следует создать аналогичное указание в качестве указания запроса. Чтобы задать аналогичное указание в качестве указания запроса, включите TABLE HINT в предложение OPTION. Эта спецификация сохраняет семантику запроса. Например, если запрос содержит табличное указание NOLOCK, то предложение OPTION в параметре @hints структуры плана также должно содержать указание NOLOCK. См. пример Л.

Настройка указаний с помощью указаний хранилища запросов

Вы можете принудительно применять указания к запросам, определенным в хранилище запросов, без внесения изменений в код с помощью функции Указания хранилища запросов (предварительная версия). Используйте хранимую процедуру sys.sp_query_store_set_hints, чтобы применить указание к запросу. См. пример N.

Примеры

A. Использование MERGE JOIN

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

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 и среднюю избирательность предиката по всем значениям столбцов для @postal_code. В этом примере используется база данных AdventureWorks2012.

CREATE PROCEDURE dbo.RetrievePersonAddress
@city_name NVARCHAR(30),  
 @postal_code NVARCHAR(15)
AS
SELECT * FROM Person.Address  
WHERE City = @city_name AND PostalCode = @postal_code  
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );  
GO

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

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

--Creates an infinite loop  
WITH cte (CustomerID, PersonID, StoreID) AS  
(  
    SELECT CustomerID, PersonID, StoreID  
    FROM Sales.Customer  
    WHERE PersonID IS NOT NULL  
  UNION ALL  
    SELECT cte.CustomerID, cte.PersonID, cte.StoreID  
    FROM cte   
    JOIN  Sales.Customer AS e   
        ON cte.PersonID = e.CustomerID  
)  
--Uses MAXRECURSION to limit the recursive levels to 2  
SELECT CustomerID, PersonID, StoreID  
FROM cte  
OPTION (MAXRECURSION 2);  
GO  

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

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

В следующем примере используется указание запроса MERGE UNION. В этом примере используется база данных AdventureWorks2012.

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

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

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

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. В этом примере используется база данных AdventureWorks2012.

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

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

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

В следующем примере используется табличное указание FORCESEEK. В предложении TABLE HINT также необходимо указать двухкомпонентное имя, которое совпадает с именем видимого объекта. Укажите имя при применении указания INDEX к таблице, которая использует двухкомпонентное имя. В этом примере используется база данных AdventureWorks2012.

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. В этом примере используется база данных AdventureWorks2012.

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 для замещения существующего табличного указания

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

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    

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

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

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. В этом примере используется база данных AdventureWorks2012.

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  

М. Указание USE HINT

В следующем примере используются указания запросов RECOMPILE и USE HINT. В этом примере используется база данных AdventureWorks2012.

SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (RECOMPILE, USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES', 'DISABLE_PARAMETER_SNIFFING')); 
GO  

Н. Использование QUERYTRACEON HINT

В следующем примере используются указания запроса QUERYTRACEON. В этом примере используется база данных AdventureWorks2012. Можно включить все исправления, влияющие на план, которыми управляет флаг трассировки 4199, для конкретного запроса, используя следующий запрос:

SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (QUERYTRACEON 4199);

Кроме того, вы можете использовать несколько флагов трассировки, как в следующем запросе:

SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION  (QUERYTRACEON 4199, QUERYTRACEON 4137);

О. Использование указаний хранилища запросов (предварительная версия)

Функция Указания хранилища запросов (предварительная версия) в Базе данных SQL Azure предоставляет простой в использовании метод для формирования планов запросов без изменения кода приложения.

Сначала следует выяснить, какой запрос уже выполнялся в представлениях каталога хранилища запросов, например:

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt 
INNER JOIN sys.query_store_query q ON 
    qt.query_text_id = q.query_text_id 
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%'  
  AND query_sql_text not like N'%query_store%';
GO

В следующем примере применяется указание для принудительного использования устаревшей оценки кратности с запросом query_id 39, обнаруженным в хранилище запросов:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

В следующем примере применяется указание для принудительного применения максимального размера памяти в процентах от заданного предела памяти для запроса query_id 39, обнаруженного в хранилище запросов:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)';

В следующем примере несколько указаний запроса применяются к запросу query_id 39, включая RECOMPILE, MAXDOP 1 и поведение оптимизатора запросов SQL 2012:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';

См. также