Подсказки запросов (Transact-SQL)
Область применения: SQL Server
База данных SQL Azure Управляемый экземпляр SQL Azure
Указания запросов определяют, что выбранные указания используются в области запроса. Они влияют на все операторы в инструкции. Если UNION
он участвует в основном запросе, то только последний запрос UNION
с участием операции может иметь OPTION
предложение. Подсказки в запросе указываются как часть предложения OPTION. Если оптимизатор запросов не сформирует допустимый план из-за одного или нескольких указаний запроса, возникает ошибка 8622.
Внимание
Так как оптимизатор запросов SQL Server обычно выбирает оптимальный план выполнения для запроса, мы рекомендуем использовать только указания в качестве последнего средства для опытных разработчиков и администраторов баз данных.
Относится к:
Соглашения о синтаксисе Transact-SQL
Синтаксис
<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 ] ] )
| FOR TIMESTAMP AS OF '<point_in_time>'
}
<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 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
{ HASH |ORDER } GROUP
Указывает, что агрегаты, описываемые запросом GROUP BY
или DISTINCT
предложением, должны использовать хэширование или упорядочивание.
{ MERGE | HASH | CONCAT } UNION
Указывает, что все UNION
операции выполняются путем объединения, хэширования или объединения UNION
наборов. Если указано несколько UNION
подсказок, оптимизатор запросов выбирает наименьшую дорогостоящую стратегию из указанных подсказок.
{ LOOP | MERGE | HASH } JOIN
Указывает, что все операции соединения выполняются LOOP JOIN
MERGE JOIN
или HASH JOIN
в целом запросе. Если задано больше одного указания соединения, оптимизатор запросов выбирает наименее затратную стратегию из допустимых.
Если указать указание соединения в предложении одного запроса FROM
для определенной пары таблиц, это указание соединения имеет приоритет при присоединении двух таблиц. Но указания запроса при этом все равно должны соблюдаться. Указание соединения для пары таблиц может ограничить только выбор разрешенных методов соединения в подсказке запроса. Дополнительные сведения см. в указаниях на присоединение.
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 2012 (11.x) с пакетом обновления 3, SQL Server 2014 (12.x) с пакетом обновления 2 и База данных SQL Azure.
Максимальный размер предоставления памяти в PERCENT
настроенном ограничении памяти. Запрос не будет превышать это ограничение, если запрос выполняется в определяемом пользователем пуле ресурсов. В этом случае, если запрос не имеет минимальной требуемой памяти, система вызывает ошибку. Если запрос выполняется в системном пуле (по умолчанию), он получает как минимум память, необходимую для выполнения. Реальное ограничение может быть ниже, если значение параметра Resource Governor ниже значения в этом указании. Допустимые значения — от 0 до 100.
Указание предоставления памяти недоступно для создания индекса или перестроения индекса.
MIN_GRANT_PERCENT = <numeric_value>
Область применения: SQL Server (начиная с SQL Server 2012 (11.x) с пакетом обновления 3, SQL Server 2014 (12.x) с пакетом обновления 2 и База данных SQL Azure.
Минимальный размер предоставления памяти в PERCENT
настроенном ограничении памяти. Запрос гарантированно получает MAX(required memory, min grant)
, так как для запуска запроса требуется определенный минимальный объем памяти. Допустимые значения — от 0 до 100.
Параметр предоставления памяти min_grant_percent переопределяет sp_configure
параметр (минимальная память на запрос (КБ)независимо от размера. Указание предоставления памяти недоступно для создания индекса или перестроения индекса.
MAXDOP <integer_value>
Применяется к: SQL Server (начиная с SQL Server 2008 (10.0.x)) и База данных SQL Azure.
Переопределяет параметр конфигурации sp_configure
максимальной степени параллелизма. Также переопределяет Resource Governor для запроса, в котором указан этот параметр. Указание MAXDOP
запроса может превышать значение, настроенное с sp_configure
помощью . Если MAXDOP
значение превышает значение, настроенное с помощью регулятора ресурсов, ядро СУБД использует значение регулятора MAXDOP
ресурсов, описанное в ALTER WORKLOAD GROUP. Все семантические правила, используемые с параметром конфигурации максимальной степени параллелизма , применимы при использовании MAXDOP
указания запроса. Дополнительные сведения см. в разделе Настройка параметра конфигурации сервера max degree of parallelism.
Предупреждение
Если MAXDOP
задано равно нулю, сервер выбирает максимальную степень параллелизма.
MAXRECURSION <integer_value>
Указывает максимальное число рекурсий, допустимых для данного запроса. число является положительным целым числом от 0 до 32 767. Если указано значение 0, ограничения не применяются. Если этот параметр не указан, для сервера используется ограничение по умолчанию 100.
Когда указанное или стандартное число для MAXRECURSION
ограничения достигается во время выполнения запроса, запрос заканчивается и возвращается ошибка.
Из-за этой ошибки все действия инструкции откатываются. Если оператор является оператором, частичные SELECT
результаты или не могут быть возвращены. Любые частичные результаты, возвращаемые, могут не включать все строки на уровнях рекурсии за пределы указанного максимального уровня рекурсии.
Дополнительные сведения см. в разделе WITH common_table_expression.
NO_PERFORMANCE_SPOOL
Область применения: SQL Server (начиная с SQL Server 2016 (13.x)) и База данных SQL Azure.
Запрещает добавление оператора очередей в планы запроса (за исключением тех планов, когда очередь необходима для гарантированного обеспечения допустимой семантики обновления). Оператор spool может снизить производительность в некоторых сценариях. Например, очередь использует базу данных tempdb
, и может возникнуть состязание за 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
запроса можно указать только в руководстве по плану, чтобы переопределить текущий параметр PARAMETERIZATION
базы данных SET
. Оно не может быть определено напрямую в запросе.
Дополнительные сведения см. в разделе Указание механизма параметризации запросов с помощью структур плана.
SIMPLE
Указывает оптимизатору запросов попытаться выполнить простую параметризацию. FORCED
Указывает оптимизатору запросов попытаться принудительной параметризации. Дополнительные сведения см. в разделах Принудительная параметризация и Простая параметризация в руководстве по архитектуре обработки запросов.
QUERYTRACEON <integer_value>
Этот параметр позволяет включить флаг трассировки, влияющий на план, только во время компиляции с одним запросом. Как и другие параметры уровня запроса, его можно использовать вместе со структурами плана, чтобы обеспечить соответствие тексту запроса, выполняемого из любого сеанса, и автоматически применять флаг трассировки, влияющий на план, при компиляции этого запроса. Этот QUERYTRACEON
параметр поддерживается только для флагов трассировки оптимизатора запросов. Дополнительные сведения см. в статье о флагах трассировки.
Использование этого параметра не возвращает никаких ошибок или предупреждений, если используется неподдерживаемый номер флага трассировки. Если указанный флаг трассировки не влияет на план выполнения запроса, параметр автоматически игнорируется.
Чтобы использовать несколько флагов трассировки в запросе, укажите одно QUERYTRACEON
указание для каждого разного номера флага трассировки.
RECOMPILE
Указывает SQL Server ядро СУБД создать новый временный план для запроса и немедленно отменить этот план после завершения выполнения запроса. Созданный план запроса не заменяет план, хранящийся в кэше, если тот же запрос выполняется без RECOMPILE
указания. Без указания RECOMPILE
ядро СУБД кэширует планы запросов и повторно использует их. При компиляции RECOMPILE
планов запросов указание запроса использует текущие значения всех локальных переменных в запросе. Если запрос находится в хранимой процедуре, всем параметрам присваиваются текущие значения.
RECOMPILE
является полезной альтернативой созданию хранимой процедуры. RECOMPILE
WITH RECOMPILE
использует предложение, если необходимо перекомпилировать только подмножество запросов внутри хранимой процедуры вместо всей хранимой процедуры. Дополнительные сведения см. в разделе Перекомпиляция хранимой процедуры. RECOMPILE
также полезно при создании руководств по плану.
ROBUST PLAN
Заставляет оптимизатор запросов использовать план, который работает со строками наибольшего потенциального размера, возможно, с потерей производительности. При обработке запроса промежуточные таблицы и операторы могут хранить и обрабатывать строки, которые шире, чем любая из входных строк при обработке запроса. Строки могут быть настолько широкими, что иногда конкретный оператор не может обработать строку. Если строки являются широкими, ядро СУБД создает ошибку во время выполнения запроса. С помощью ROBUST PLAN
вы указываете оптимизатору запросов не учитывать планы запросов, которые могут возникнуть в этой проблеме.
Если такой план невозможен, оптимизатор запросов возвращает ошибку сразу, не откладывая обнаружение ошибок на момент выполнения запроса. Строки могут содержать столбцы переменной длины; ядро СУБД позволяет определить строки, имеющие максимальный потенциальный размер за пределами возможности обработки ядро СУБД. Как правило, несмотря на максимальный потенциальный размер, приложение сохраняет строки с фактическими размерами в пределах, которые могут обрабатываться ядро СУБД. Если ядро СУБД сталкивается со строкой, которая слишком длинна, возвращается ошибка выполнения.
USE HINT ( 'hint_name' )
Область применения: SQL Server (начиная с SQL Server 2016 (13.x) с пакетом обновления 1 (SP1) и База данных SQL Azure.
Предоставляет один или несколько дополнительных подсказок обработчику запросов. Дополнительные подсказки указываются с именем подсказки внутри отдельных кавычки.
Совет
В именах указаний учитывается регистр.
Поддерживаются следующие имена подсказок:
Подсказка | Description |
---|---|
'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 2017 (14.x) и более поздних версий и База данных SQL Azure |
'DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK' |
Отключает обратную связь по временно предоставляемому буферу памяти в пакетном режиме. Дополнительные сведения см. в разделе Обратная связь по временно предоставляемому буферу памяти в пакетном режиме. Область применения: SQL Server 2017 (14.x) и более поздних версий и База данных SQL Azure |
'DISABLE_DEFERRED_COMPILATION_TV' |
Отключает отложенную компиляцию табличных переменных. См. дополнительные сведения об отложенной компиляции табличных переменных. Область применения: SQL Server 2019 (15.x) и более поздних версий и База данных SQL Azure |
'DISABLE_INTERLEAVED_EXECUTION_TVF' |
Отключает выполнение с чередованием для функций с табличным значением с несколькими инструкциями. Дополнительные сведения см. в разделе о выполнении с чередованием для функций с табличным значением с несколькими инструкциями. Область применения: SQL Server 2017 (14.x) и более поздних версий и База данных SQL Azure |
'DISABLE_OPTIMIZED_NESTED_LOOP' |
Заставляет обработчик запросов не использовать операцию сортировки (сортировки пакетов) для оптимизации соединений вложенного цикла при формировании плана запроса. Это имя подсказки эквивалентно флагу трассировки 2340. Это указание также относится к явным сортировкам и пакетным сортировкам. |
'DISABLE_OPTIMIZER_ROWGOAL' |
Указывает, что SQL Server должен создать план без использования изменений целей строк с запросами, содержащими следующие ключевые слова: - TOP - OPTION (FAST N) - IN - EXISTS Это имя подсказки эквивалентно флагу трассировки 4138. |
'DISABLE_PARAMETER_SNIFFING' |
Указывает, что оптимизатор запросов должен использовать среднее распределение данных при компиляции запроса с одним или несколькими параметрами. Эта инструкция делает план запроса независимым от значения параметра, которое было использовано при первой компиляции запроса. Это имя подсказки эквивалентно параметру конфигурации флага трассировки 4136 или базы PARAMETER_SNIFFING = OFF данных. |
'DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK' |
Отключает обратную связь по временно предоставляемому буферу памяти в строковом режиме. Дополнительные сведения см. в разделе Обратная связь по временно предоставляемому буферу памяти в строковом режиме. Область применения: SQL Server 2019 (15.x) и более поздних версий и База данных SQL Azure |
'DISABLE_TSQL_SCALAR_UDF_INLINING' |
Отключает встраивание скалярных пользовательских функций. Дополнительные сведения: Встраивание скалярной функции, определяемой пользователем. Область применения: SQL Server 2019 (15.x) и более поздних версий и База данных SQL Azure |
'DISALLOW_BATCH_MODE' |
Отключает выполнение в пакетном режиме. Дополнительные сведения см. в разделе Режимы выполнения. Область применения: 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' 1 |
Принудительно изменяет поведение оптимизатора запросов на уровне запроса. Оптимизация выполняется так, как если бы запрос компилировался с уровнем совместимости базы данных n, где n— максимальный поддерживаемый уровень совместимости базы данных. Список поддерживаемых в настоящее время значений для n см. в sys.dm_exec_valid_use_hints. Область применения: SQL Server 2017 (14.x) CU 10 и более поздних версий и База данных SQL Azure |
'QUERY_PLAN_PROFILE' 2 |
Включает упрощенное профилирование для запроса. После завершения запроса, содержащего это новое указание, запускается новое расширенное событие query_plan_profile . Это расширенное событие предоставляет статистику выполнения и фактический XML-код плана выполнения, query_post_execution_showplan аналогичный расширенному событию, но только для запросов, содержащих новое указание.Область применения: SQL Server 2016 (13.x) с пакетом обновления 2 (SP 3), SQL Server 2017 (14.x) CU 11 и более поздних версий |
1 Указание QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n
не переопределяет параметр оценки кратности по умолчанию или устаревшей версии, если вы принудительно используете конфигурацию базы данных, флаг трассировки или другое указание запроса, например QUERYTRACEON
. Это указание влияет только на поведение оптимизатора запросов. Это не влияет на другие функции SQL Server, которые могут зависеть от уровня совместимости базы данных, например доступности определенных функций базы данных. Дополнительные сведения см. в разделе "Выбор разработчика: указание модели выполнения запросов".
2 . Если вы включите сбор расширенного 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
ней, производительность планов может отличаться. В редких случаях разница производительности может быть значительной и отрицательной; В этом случае администратор должен удалить принудительный план.
ТАБЛИЧНОЕ УКАЗАНИЕ ( 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
указания, если не удается изменить исходный запрос. См . пример J.
<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 | МОМЕНТАЛЬНЫЙ СНИМОК | SPATIAL_WINDOW_MAX_CELLS = integer_value | TABLOCK | TABLOCKX | UPDLOCK | XLOCK
Указание таблицы, применяемое к таблице или представлению, которое соответствует exposed_object_name в качестве указания запроса. Описание этих подсказок см. в описании подсказок таблицы.
Табличные подсказки, отличные INDEX
от , FORCESCAN
и FORCESEEK
запрещены в качестве подсказок запроса, если запрос уже не содержит WITH
предложение, указывающее подсказку таблицы. Дополнительные сведения см. в разделе с примечаниями.
Внимание
Указание FORCESEEK
параметров ограничивает количество планов, которые можно учитывать оптимизатором запросов больше, чем при указании FORCESEEK
без параметров. Это может привести к возникновению ошибки "План не может быть создано" в большем случае.
ДЛЯ МЕТКИ ВРЕМЕНИ ПО СОСТОЯНИЮ НА "POINT_IN_TIME"
Область применения: хранилище в Microsoft Fabric
TIMESTAMP
Используйте синтаксис в OPTION
предложении, чтобы запросить данные, которые существовали в прошлом, часть функции перемещения по времени в Хранилище данных Synapse в Microsoft Fabric.
Укажите point_in_time в формате yyyy-MM-ddTHH:mm:ss[.fff]
для возврата данных, как это было показано в то время. Часовой пояс всегда находится в формате UTC. CONVERT
Используйте синтаксис для необходимого формата datetime с стилем 126.
Указание TIMESTAMP AS OF
можно указать только один раз с помощью OPTION
предложения. Дополнительные сведения и ограничения см. в разделе "Запрос данных" по мере его существования в прошлом.
Замечания
Указания запросов не могут быть указаны в 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
указание. См . пример K.
Определение указаний с помощью указаний хранилища запросов
Вы можете принудительно применять указания к запросам, определенным через хранилище запросов, не внося изменения в код, с помощью возможности Указания хранилища запросов. Используйте хранимую процедуру sys.sp_query_store_set_hints, чтобы применить указание к запросу. См. пример N.
Примеры
А. Использование MERGE JOIN
В следующем примере указывается, что MERGE JOIN
выполняется JOIN
операция в запросе. В этом примере используется база данных AdventureWorks2022
.
SELECT *
FROM Sales.Customer AS c
INNER JOIN Sales.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID
WHERE TerritoryID = 5
OPTION (MERGE JOIN);
GO
B. Использование OPTIMIZE FOR
В следующем примере оптимизатору запросов дается указание, что при оптимизации запроса следует использовать значение 'Seattle'
для @city_name
и среднюю избирательность предиката по всем значениям столбцов для @postal_code
. В этом примере используется база данных AdventureWorks2022
.
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
C. Использование MAXRECURSION
MAXRECURSION
можно использовать для предотвращения плохо сформированного рекурсивного общего табличного выражения в бесконечном цикле. В следующем примере преднамеренно формируется бесконечный цикл и используется указание MAXRECURSION
для ограничения числа уровней рекурсии двумя. В этом примере используется база данных AdventureWorks2022
.
--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
ошибки кодирования больше не требуется.
D. Использование MERGE UNION
В следующем примере используется указание запроса MERGE UNION
. В этом примере используется база данных AdventureWorks2022
.
SELECT *
FROM HumanResources.Employee AS e1
UNION
SELECT *
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);
GO
Е. Использование HASH GROUP и FAST
В следующем примере используются HASH GROUP
подсказки и FAST
указания запросов. В этом примере используется база данных AdventureWorks2022
.
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
F. Использование MAXDOP
В следующем примере используется указание запроса MAXDOP
. В этом примере используется база данных AdventureWorks2022
.
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
G. Использование INDEX
В следующих примерах используется INDEX
указание. В первом примере задан один индекс. Во втором примере указывается несколько индексов для одной табличной ссылки. В обоих примерах, так как вы применяете INDEX
указание к таблице, использующую псевдоним, TABLE HINT
предложение также должно указывать тот же псевдоним, что и имя предоставленного объекта. В этом примере используется база данных AdventureWorks2022
.
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
H. Использование FORCESEEK
В следующем примере используется FORCESEEK
указание таблицы. Предложение TABLE HINT
должно также указать одно и то же имя двух частей, что и имя предоставленного объекта. Укажите имя при применении INDEX
подсказки к таблице, которая использует имя двух частей. В этом примере используется база данных AdventureWorks2022
.
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
I. Использование нескольких подсказок таблицы
В следующем примере подсказка INDEX
применяется к одной таблице и подсказке FORCESEEK
к другой. В этом примере используется база данных AdventureWorks2022
.
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
J. Использование TABLE HINT для переопределения существующего указания таблицы
В следующем примере показано, как использовать подсказку TABLE HINT
. Указание можно использовать без указания указания для переопределения INDEX
поведения указания таблицы, указанного в FROM
предложении запроса. В этом примере используется база данных AdventureWorks2022
.
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
K. Указание подсказок таблицы семантики, влияющих на семантику
В следующем примере содержатся два указания таблицы в запросе: NOLOCK
который влияет на семантику и INDEX
не семантику влияет. Чтобы сохранить семантику запроса, NOLOCK
указание указывается в OPTIONS
предложении руководства по плану. Наряду с NOLOCK
указанием укажите и FORCESEEK
намеки INDEX
и замените неемантические подсказки INDEX
в запросе во время компиляции и оптимизации инструкций. В этом примере используется база данных AdventureWorks2022
.
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
указания. В этом примере используется база данных AdventureWorks2022
.
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
L. Использование ПОДСКАЗКИ USE
В следующем примере используются RECOMPILE
подсказки и USE HINT
указания запросов. В этом примере используется база данных AdventureWorks2022
.
SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (RECOMPILE, USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES', 'DISABLE_PARAMETER_SNIFFING'));
GO
M. Использование ЗАПРОСАTRACEON HINT
В следующем примере используются QUERYTRACEON
подсказки запроса. В этом примере используется база данных AdventureWorks2022
. Можно включить все исправления, влияющие на план, которыми управляет флаг трассировки 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, определенного в PERCENT
хранилище запросов:
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 Server 2012 (11.x):
EXEC sys.sp_query_store_set_hints @query_id= 39,
@query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';
O. Запрос данных в момент времени
Область применения: хранилище в Microsoft Fabric
TIMESTAMP
Используйте синтаксис в OPTION
предложении, чтобы запросить данные, которые существовали в прошлом, в Хранилище данных Synapse в Microsoft Fabric. Следующий пример запроса возвращает данные, как он появился 13 марта 2024 г. в 7:39:35.28 ВЕЧЕРА UTC. Часовой пояс всегда находится в формате UTC.
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales
FROM FactInternetSales
GROUP BY OrderDateKey
ORDER BY OrderDateKey
OPTION (FOR TIMESTAMP AS OF '2024-03-13T19:39:35.28');--March 13, 2024 at 7:39:35.28 PM UTC