Интеллектуальная обработка запросов в базах данных SQL

Применимо к: даSQL Server (все поддерживаемые версии) ДаБаза данных SQL Azure ДаУправляемый экземпляр SQL Azure

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

Интеллектуальная обработка запросов

Просмотрите это 6-минутное видео, чтобы получить общие сведения об интеллектуальной обработке запросов:

Рабочие нагрузки можно автоматически сделать подходящими для интеллектуальной обработки запросов, включив для базы данных соответствующий уровень совместимости. Для этого можно использовать Transact-SQL. Пример:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;

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

Функция интеллектуальной обработки запросов Поддерживается в База данных SQL Azure и Управляемый экземпляр SQL Azure Поддерживается в SQL Server Описание
Адаптивные соединения в пакетном режиме Да, при уровне совместимости 140 Да, начиная с SQL Server 2017 (14.x); при уровне совместимости 140 При использовании адаптивных соединений тип соединения выбирается динамически во время выполнения в зависимости от фактических входных строк.
Приблизительный подсчет различных объектов Да Да, начиная с SQL Server 2019 (15.x) Предоставление приблизительного значения COUNT DISTINCT в сценариях обработки больших данных с сохранением производительности и низким потреблением памяти.
Пакетный режим для данных rowstore Да, при уровне совместимости 150 Да, начиная с SQL Server 2019 (15.x), при уровне совместимости 150 Пакетный режим для рабочих нагрузок реляционного хранилища данных, ограниченных производительностью ЦП, без необходимости использовать индексы columnstore.
Выполнение с чередованием Да, при уровне совместимости 140 Да, начиная с SQL Server 2017 (14.x); при уровне совместимости 140 Использование фактической кратности из выходных данных функции с табличным значением с несколькими инструкциями, обнаруженной при первой компиляции, вместо фиксированной оценки.
Обратная связь по временно предоставляемому буферу памяти в пакетном режиме Да, при уровне совместимости 140 Да, начиная с SQL Server 2017 (14.x); при уровне совместимости 140 Если при выполнении запроса в пакетном режиме некоторые операции переносятся на диск, объем памяти для последующих выполнений увеличивается. Если запрос потребляет более 50 % выделенной ему памяти, размер временно предоставляемого буфера памяти для последующих выполнений уменьшается.
Обратная связь по временно предоставляемому буферу памяти в строковом режиме Да, при уровне совместимости 150 Да, начиная с SQL Server 2019 (15.x), при уровне совместимости 150 Если при выполнении запроса в построчном режиме некоторые операции переносятся на диск, объем памяти для последующих выполнений увеличивается. Если запрос потребляет более 50 % выделенной ему памяти, размер временно предоставляемого буфера памяти для последующих выполнений уменьшается.
Встраивание скалярных определяемых пользователем функций Да, при уровне совместимости 150 Да, начиная с SQL Server 2019 (15.x), при уровне совместимости 150 Скалярные пользовательские функции преобразуются в эквивалентные реляционные выражения, которые "встраиваются" в вызывающий запрос, что часто приводит к существенному повышению производительности.
Отложенная компиляция табличных переменных Да, при уровне совместимости 150 Да, начиная с SQL Server 2019 (15.x), при уровне совместимости 150 Использование фактической кратности табличной переменной, обнаруженной при первой компиляции, вместо фиксированной оценки.

Адаптивные соединения в пакетном режиме

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

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

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

Обратная связь по временно предоставляемому буферу памяти в пакетном режиме

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

План после выполнения запроса в SQL Server включает минимальный объем памяти, необходимый для выполнения, а также оптимальный временно предоставляемый буфер памяти, позволяющий уместить в памяти все строки. Если размер временно предоставляемого буфера памяти выбран неправильно, производительность снижается. Избыточные буферы ведут к потере памяти и снижению параллелизма. Недостаточные буферы ведут к затратной временной записи на диск. Обратная связь по временно предоставляемому буферу памяти в пакетном режиме, ориентированная на повторяющиеся рабочие процессы, пересчитывает фактическую требуемую память для запроса и затем обновляет значение временного буфера для кэшированного плана. При выполнении идентичной инструкции запроса используется пересмотренный размер временно предоставляемого буфера памяти, который уменьшает чрезмерные буферы, снижающие параллелизм, исправляет недостаточные буферы, вызывающие затратную временную запись на диск. На следующем графе показан пример использования обратной связи по временно предоставляемому буферу памяти в пакетном режиме. Длительность первого выполнения запроса составила 88 секунд из-за высокого уровня временной записи на диск.

DECLARE @EndTime datetime = '2016-09-22 00:00:00.000';
DECLARE @StartTime datetime = '2016-09-15 00:00:00.000';

SELECT TOP 10 hash_unique_bigint_id
FROM dbo.TelemetryDS
WHERE Timestamp BETWEEN @StartTime AND @EndTime
GROUP BY hash_unique_bigint_id
ORDER BY MAX(max_elapsed_time_microsec) DESC;

Высокий уровень временной записи на диск

С включенной обратной связью по временно предоставляемому буферу памяти длительность второго выполнения составила 1 секунду (ранее 88 секунд), при этом временная запись на диск полностью устранена, а временный буфер увеличен.

Временная запись на диск отсутствует.

Определение размера с помощью обратной связи по временно предоставляемому буферу памяти

Для чрезмерных временно предоставляемых буферов памяти, когда предоставленный объем памяти больше чем в два раза превышает объем фактической используемой памяти, функция обратной связи пересчитывает временно предоставляемый буфер памяти и обновляет кэшированный план. Для планов, у которых размер временно предоставляемого буфера памяти меньше 1 МБ, пересчет по превышению не выполняется. Для недостаточных временно предоставляемых буферов памяти, которые приводят к временной записи на диск для операторов пакетного режима, обратная связь по временно предоставляемому буферу памяти активирует пересчет буфера. События временной записи передаются в функцию обратной связи и могут быть предоставлены с помощью события XEvent spilling_report_to_memory_grant_feedback. Оно возвращает идентификатор узла из плана, а также объем временно записанных данных для этого узла.

Обратная связь по временно предоставляемому буферу памяти и сценарии, зависящие от параметров

Для сохранения оптимальности различным значениям параметров могут также потребоваться разные планы запроса. Такой тип запроса называется "зависящим от параметров". Для планов, зависящих от параметров, функция обратной связи по временно предоставляемому буферу памяти отключается для запроса, имеющего нестабильные требования к памяти. После нескольких повторных выполнений запроса план отключается, что можно наблюдать, отслеживая событие XEvent memory_grant_feedback_loop_disabled. Дополнительные сведения о сканировании и чувствительности параметров см. в разделе Руководство по архитектуре обработки запросов.

Кэширование обратной связи по временно предоставляемому буферу памяти

Обратную связь можно хранить в кэшированном плане для однократного выполнения. Но именно последовательные выполнения этой инструкции позволяют использовать преимущества корректировки обратной связи по временно предоставляемому буферу памяти. Эта функция применяется к повторному выполнению инструкций. Обратная связь по временно предоставляемому буферу памяти изменяет только кэшированный план. Изменения сейчас не сохраняются в хранилище запросов. Обратная связь не сохраняется, если план исключается из кэша. Кроме того, в случае перехода на другой ресурс обратная связь будет утеряна. Инструкция, использующая OPTION (RECOMPILE), создает план и не кэширует его. Из-за отсутствия кэширования обратная связь по временно предоставляемому буферу памяти не создается, а план не сохраняется для компиляции и выполнения. Но если эквивалентная инструкция (т. е. с тем же хэшем запроса), не использовавшая OPTION (RECOMPILE), была кэширована и затем повторно выполнена, последующая инструкция может использовать преимущества обратной связи по временно предоставляемому буферу памяти.

Отслеживание операций обратной связи по временно предоставляемому буферу памяти

Вы можете отслеживать события обратной связи по временно предоставляемому буферу памяти с помощью события xEvent memory_grant_updated_by_feedback. Оно отслеживает текущий журнал подсчета выполнений, количество изменений плана функцией обратной связи, оптимальный дополнительный временно предоставляемый буфер памяти перед изменением и оптимальный буфер после изменения кэшированного плана функцией обратной связи.

Обратная связь по временно предоставляемому буферу памяти, регулятор ресурсов и указания запроса

Фактический объем предоставляемой памяти учитывает лимит памяти запросов, определяемый регулятором ресурсов или указанием запроса.

Отключение сброса данных во временно предоставляемый буфер памяти в пакетном режиме без изменения уровня совместимости

Сброс данных во временно предоставляемый буфер памяти можно отключить в области базы данных или инструкции, сохранив уровень совместимости базы данных 140 или более высокий. Чтобы отключить сброс данных во временно предоставляемый буфер памяти в пакетном режиме для всех запросов, выполняемых из базы данных, выполните следующую команду в контексте соответствующей базы данных:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Когда этот параметр включен, он будет иметь соответствующее состояние в представлении sys.database_scoped_configurations.

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

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

-- Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

Вы также можете отключить сброс данных во временно предоставляемый буфер памяти в пакетном режиме для определенного запроса, назначив DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK в качестве указания запроса USE HINT. Пример:

SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK')); 

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

Обратная связь по временно предоставляемому буферу памяти в строковом режиме

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

Обратная связь с временно предоставляемым буфером памяти в строковом режиме — это расширение функции обратной связи с временно предоставляемым буфером памяти в пакетном режиме путем настройки размеров временно предоставляемого буфера памяти для операторов пакетного и строкового режимов.

Чтобы включить обратную связь с временно предоставляемым буфером памяти в строковом режиме в База данных SQL Azure, активируйте режим совместимости базы данных 150 для базы данных, к которой вы подключаетесь при выполнении запроса.

Действие обратной связи с временно предоставляемым буфером памяти в строковом режиме можно просмотреть с помощью события XEvent memory_grant_updated_by_feedback.

С момента выпуска функции обратной связи с временно предоставляемым буфером памяти в строковом режиме для фактических планов после выполнения будут отображаться два новых атрибута плана запроса: IsMemoryGrantFeedbackAdjusted _ и _LastRequestedMemory_. Они добавляются в XML-элемент плана запроса _MemoryGrantInfo.

LastRequestedMemory позволяет просмотреть предоставленный в результате выполнения предыдущего запроса объем памяти в килобайтах (КБ). Атрибут IsMemoryGrantFeedbackAdjusted позволяет проверить состояние обратной связи с временно предоставляемым буфером памяти для инструкции в рамках фактического плана выполнения запроса. Ниже приведены значения, отображаемые в этом атрибуте:

Значение IsMemoryGrantFeedbackAdjusted Описание
Нет: первое выполнение Обратная связь с временно предоставляемым буфером памяти не настраивает память для первой компиляции и связанной с ней операции выполнения.
Нет: точное предоставление Если не выполняется временная запись на диск и в инструкции используется не менее 50 % объема предоставленной памяти, обратная связь с временно предоставляемым буфером памяти не активируется.
Нет: обратная связь отключена Если обратная связь с временно предоставляемым буфером памяти непрерывно активируется и сопровождается постоянным увеличением и уменьшением объема памяти, мы отключим обратную связь с временно предоставляемым буфером памяти для этой инструкции.
Да: настройка Обратная связь с временно предоставляемым буфером памяти применена, и ее можно дополнительно настроить для следующего выполнения.
Да: объем стабилен Обратная связь с временно предоставляемым буфером памяти применена, и объем памяти теперь стабилен. Это означает, что предоставленный для предыдущего выполнения объем эквивалентен предоставленному для текущего выполнения.

Отключение сброса данных во временно предоставляемый буфер памяти в строчном режиме без изменения уровня совместимости

Сброс данных во временно предоставляемый буфер памяти в строчном режиме можно отключить в области базы данных или инструкции, сохранив уровень совместимости базы данных 150 и выше. Чтобы отключить сброс данных во временно предоставляемый буфер памяти в строчном режиме для всех запросов, выполняемых из базы данных, выполните следующую команду в контексте соответствующей базы данных:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

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

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

Вы также можете отключить сброс данных во временно предоставляемый буфер памяти в строковом режиме для определенного запроса, назначив DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK в качестве указания запроса USE HINT. Пример:

SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK')); 

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

Выполнение с чередованием для MSTVF

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

При выполнении с чередованием используется фактическое количество строк из функции для принятия обоснованного решения о плане запроса. См. дополнительные сведения о функциях с табличным значением с несколькими инструкциями (MSTVF).

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

Функции MSTVF имеют фиксированное предполагаемое значение кратности 100 начиная с SQL Server 2014 (12.x) и 1 в более ранних версиях SQL Server. Выполнение с чередованием помогает устранить проблемы с производительностью рабочих нагрузок, вызванные фиксированными оценками кратности, которые связаны с функциями MSTVF. Дополнительные сведения о функциях MSTVF см. в разделе Создание определяемых пользователем функций (ядро СУБД).

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

  1. Сканирование таблицы MSTVF имеет фиксированную оценку в 100 строк. Однако в данном примере через это сканирование таблицы MSTVF передается 527 597 строк, как видно в статистике активных запросов — 527597 из 100, то есть фиксированная оценка имеет значительное отклонение.
  2. Для операции вложенных циклов предполагается, что наружная часть соединения возвращает всего 100 строк. Учитывая, как много строк функция MSTVF возвращает на самом деле, лучше всего перейти на другой алгоритм соединения.
  3. Для операции хэш-совпадений обратите внимание на небольшой предупреждающий символ, который в данном случае указывает на временную запись на диск.

Поток строк и предполагаемые строки

Сравним предыдущий план с фактическим планом, созданным при включенном выполнении с чередованием:

План с чередованием

  1. Обратите внимание, что сканирование таблицы MSTVF отражает точную оценку кратности. Также обратите внимание на переупорядочение этого сканирования таблицы и других операций.
  2. Что касается алгоритмов соединения, мы перешли от операции вложенных циклов Loop к операции хэш-совпадений, которая лучше подходит для такого большого числа строк.
  3. Также обратите внимание, что прекратились предупреждения о временной записи, так как мы выделяем больше памяти на основе истинного количества строк, поступающих из сканирования таблицы MSTVF.

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

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

Преимущества выполнения с чередованием

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

  1. Имеется большое отклонение между предполагаемым и фактическим числом строк для промежуточного результирующего набора (в данном случае — MSTVF).
  2. Весь запрос чувствителен к изменению размера промежуточного результата. Обычно это происходит, когда над поддеревом в плане запроса имеется сложное дерево. Выполнение с чередованием не принесет никакой выгоды для простой инструкции SELECT * из MSTVF.

Издержки выполнения с чередованием

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

Выполнение с чередованием и последовательные выполнения

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

Отслеживание операций выполнения с чередованием

Вы можете просмотреть атрибуты использования в фактическом плане выполнения запроса:

Атрибут плана выполнения Описание
ContainsInterleavedExecutionCandidates Применяется к узлу QueryPlan. Значение true означает, что план содержит кандидаты на выполнение с чередованием.
IsInterleavedExecuted Атрибут элемента RuntimeInformation под RelOp для узла TVF. Если значение равно true, значит, операция была материализована как часть операции выполнения с чередованием.

Вы также можете отслеживать случаи выполнения с чередованием с помощью следующих событий xEvents:

xEvent Описание
interleaved_exec_status Это событие возникает, когда происходит выполнение с чередованием.
interleaved_exec_stats_update Это событие описывает оценки кратности, обновленные выполнением с чередованием.
Interleaved_exec_disabled_reason Это событие возникает, когда запрос с кандидатом на выполнение с чередованием не получает такое выполнение.

Чтобы разрешить выполнению с чередованием пересматривать оценки кратности MSTVF, нужно выполнить запрос. При этом предполагаемый план выполнения по-прежнему сообщает о наличии кандидатов на выполнение с чередованием с помощью атрибута showplan ContainsInterleavedExecutionCandidates.

Кэширование выполнения с чередованием

Если план удаляется или извлекается из кэша, при выполнении запроса появляется новая компиляция, в которой используется исполнение чередованием. Инструкция с использованием OPTION (RECOMPILE) создаст план с использованием выполнения с чередованием, но без кэширования.

Взаимодействие выполнения с чередованием и хранилища запросов

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

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

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

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = ON;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET INTERLEAVED_EXECUTION_TVF = OFF;

Когда этот параметр включен, он будет иметь соответствующее состояние в представлении sys.database_scoped_configurations. Чтобы снова включить выполнение с чередованием для всех запросов, выполняемых из базы данных, выполните следующую команду в контексте соответствующей базы данных:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = OFF;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET INTERLEAVED_EXECUTION_TVF = ON;

Вы также можете отключить выполнение с чередованием для определенного запроса, назначив DISABLE_INTERLEAVED_EXECUTION_TVF в качестве указания запроса USE HINT. Пример:

SELECT [fo].[Order Key], [fo].[Quantity], [foo].[OutlierEventQuantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Fact].[WhatIfOutlierEventQuantity]('Mild Recession',
                            '1-01-2013',
                            '10-15-2014') AS [foo] ON [fo].[Order Key] = [foo].[Order Key]
                            AND [fo].[City Key] = [foo].[City Key]
                            AND [fo].[Customer Key] = [foo].[Customer Key]
                            AND [fo].[Stock Item Key] = [foo].[Stock Item Key]
                            AND [fo].[Order Date Key] = [foo].[Order Date Key]
                            AND [fo].[Picked Date Key] = [foo].[Picked Date Key]
                            AND [fo].[Salesperson Key] = [foo].[Salesperson Key]
                            AND [fo].[Picker Key] = [foo].[Picker Key]
OPTION (USE HINT('DISABLE_INTERLEAVED_EXECUTION_TVF'));

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

Отложенная компиляция табличных переменных

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

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

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

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

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

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

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

Отключение отложенной компиляции табличной переменной без изменения уровня совместимости

Отключите отложенную компиляцию табличной переменной в области базы данных или инструкции, сохранив уровень совместимости базы данных 150 и выше. Чтобы отключить отложенную компиляцию табличной переменной для всех запросов, поступающих из базы данных, выполните следующий пример в контексте соответствующей базы данных:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;

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

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON;

Вы также можете отключить отложенную компиляцию табличной переменной для определенного запроса, назначив DISABLE_DEFERRED_COMPILATION_TV в качестве указания запроса USE HINT. Пример:

DECLARE @LINEITEMS TABLE 
    (L_OrderKey INT NOT NULL,
     L_Quantity INT NOT NULL
    );

INSERT @LINEITEMS
SELECT L_OrderKey, L_Quantity
FROM dbo.lineitem
WHERE L_Quantity = 5;

SELECT O_OrderKey,
    O_CustKey,
    O_OrderStatus,
    L_QUANTITY
FROM    
    ORDERS,
    @LINEITEMS
WHERE    O_ORDERKEY    =    L_ORDERKEY
    AND O_OrderStatus = 'O'
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));

Встраивание скалярных пользовательских функций

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

Функция встраивания скалярных определяемых пользователем функций позволяет автоматически преобразовать скалярные определяемые пользовательские функции в реляционные выражения. Затем они внедряются в вызывающий SQL-запрос. Такое преобразование повышает производительность рабочих нагрузок, которые используют скалярные определяемые пользователем функции. Встраивание скалярных определяемых пользователем функций способствует оптимизации с учетом стоимости операций, выполняемых внутри определяемых пользователем функций. Это обеспечивает эффективные планы выполнения с поддержкой наборов и параллелизма вместо неэффективных, итеративных и последовательных планов. Эта функция включена по умолчанию на уровне совместимости базы данных 150.

Дополнительные сведения см. в разделе Встраивание скалярных функций, определяемых пользователем.

Приблизительная обработка запросов

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

Приблизительная обработка запросов — это новое семейство функций. Оно позволяет агрегировать большие наборы данных, для которых скорость реагирования намного важнее абсолютной точности. В качестве примера предположим, что нам нужно вычислить выражение COUNT(DISTINCT()) по 10 миллиардам строк, чтобы отобразить результат на панели мониторинга. Абсолютная точность здесь не требуется, но критически важна скорость реагирования. Новая агрегатная функция APPROX_COUNT_DISTINCT возвращает приблизительное количество содержащихся в группе уникальных значений, не равных NULL.

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

Пакетный режим для данных rowstore

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

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

Историческая справка

В SQL Server 2012 (11.x) появилась новая функция для ускорения аналитических рабочих нагрузок — индексы columnstore. С каждым последующим выпуском мы расширяли возможности использования индексов columnstore и улучшали их производительность. До этого момента мы представляли и документировали все эти возможности как одну функцию. Индексы columnstore создаются на уровне таблиц. Благодаря этому быстрее выполняются аналитические рабочие нагрузки. Но на самом деле здесь применяются два связанных, но различных набора технологий.

  • Индексы columnstore позволяют аналитическим запросам получать доступ к данным только в требуемых столбцах. Кроме того, сжатие в формате columnstore намного эффективнее, чем для традиционных индексов rowstore.
  • При обработке в пакетном режиме операторы запросов выполняются более эффективно. Они работают по пакету строк, а не по одной строке за раз. С обработкой в пакетном режиме также связан ряд других улучшений масштабируемости. Дополнительные сведения о пакетном режиме см. в разделе Режимы выполнения.

Эти два набора функций взаимодействуют, чтобы оптимизировать скорость выполнения операций ввода-вывода и использование ЦП.

  • Благодаря индексам columnstore в памяти помещается больше данных. Это снижает нагрузку на подсистему ввода-вывода.
  • При обработке в пакетном режиме ресурсы ЦП используются более эффективно.

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

Но важно понимать, что эти две функции не зависят друг от друга.

  • Можно создать план со строковым режимом, который использует индексы columnstore.
  • Можно создать план с пакетным режимом, который использует только индексы rowstore.

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

Индексы columnstore могут не подходить для некоторых приложений. Приложение может использовать другие функции, которые не совместимы с индексами columnstore. Например, изменения на месте не совместимы со сжатием columnstore. Поэтому таблицы с кластеризованными индексами columnstore не поддерживают триггеры. Что еще важнее, индексы columnstore повышают затраты на выполнение инструкций DELETE и UPDATE.

Для некоторых гибридных транзакционно-аналитических рабочих нагрузок издержки на транзакционную нагрузку перевешивают преимущества от использования индексов columnstore. В таких сценариях можно улучшить использование ЦП, применяя исключительно режим пакетной обработки. Поэтому функция "Пакетный режим для данных rowstore" позволяет применять пакетный режим для всех запросов независимо от типа используемых индексов.

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

Пакетный режим для данных rowstore предоставляет преимущества для рабочих нагрузок со следующими характеристиками:

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

Примечание

Использование пакетного режима для данных rowstore помогает только сократить потребление ресурсов ЦП. Если же узким местом являются операции ввода-вывода и данные не кэшируются ("холодный" кэш), использование пакетного режима для rowstore не сократит время, затраченное на выполнение запроса. Аналогичным образом, если на компьютере не хватает памяти для кэширования всех данных, повышение производительности маловероятно.

Что изменяется при использовании пакетного режима для данных rowstore?

Задайте для базы данных уровень совместимости 150. Другие изменения не требуются.

Даже если запрос не обращается к таблицам с индексами columnstore, обработчик запросов использует эвристику, чтобы решить, следует ли рассматривать пакетный режим. Этот эвристический анализ включает следующее:

  1. Первоначальной проверки размеров таблиц, используемых операторов и предполагаемого количества элементов во входном запросе.
  2. Дополнительных проверок, так как оптимизатор обнаруживает новые, более дешевые планы для запроса. Если эти альтернативные планы используют пакетный режим незначительно, оптимизатор прекратит изучение альтернатив с пакетным режимом.

Если для данных rowstore используется пакетный режим, фактический режим выполнения будет обозначен как batch mode (пакетный режим) в плане запроса. Оператор сканирования использует пакетный режим для кучи на диске и индексов сбалансированного дерева. При этом сканировании пакетного режима можно оценить фильтры растрового изображения в пакетном режиме. Вы можете заметить в плане и другие операторы пакетного режима. Например, хэш-соединения, статические операции на основе хэша, сортировки, статистические операции с окнами, фильтры, объединение и операторы вычисления скалярного значения.

Remarks

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

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

Новый режим сканирования пакетного режима для данных rowstore может изменять планы для запросов, в которых сочетаются индексы columnstore и rowstore.

Для нового сканирования пакетного режима для данных rowstore действует ряд ограничений:

  • Сканирование не будет использоваться для таблиц OLTP, выполняющейся в памяти, или для любых других индексов, отличных от индексов в виде куч на диске и сбалансированных деревьев.
  • Также оно не применяется, если LOB-столбец извлекается или фильтруется. Это ограничение относится и к наборам разреженных столбцов и XML-столбцам.

Есть запросы, для которых пакетный режим не применяется даже с индексами columnstore. В качестве примера можно назвать запросы с курсорами. Эти исключения относятся и к пакетному режиму для индексов rowstore.

Настройка пакетного режима для данных rowstore

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

-- Disabling batch mode on rowstore
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF;

-- Enabling batch mode on rowstore
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON;

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

SELECT [Tax Rate], [Lineage Key], [Salesperson Key], SUM(Quantity) AS SUM_QTY, SUM([Unit Price]) AS SUM_BASE_PRICE, COUNT(*) AS COUNT_ORDER
FROM Fact.OrderHistoryExtended
WHERE [Order Date Key]<=DATEADD(dd, -73, '2015-11-13')
GROUP BY [Tax Rate], [Lineage Key], [Salesperson Key]
ORDER BY [Tax Rate], [Lineage Key], [Salesperson Key]
OPTION(RECOMPILE, USE HINT('ALLOW_BATCH_MODE'));

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

SELECT [Tax Rate], [Lineage Key], [Salesperson Key], SUM(Quantity) AS SUM_QTY, SUM([Unit Price]) AS SUM_BASE_PRICE, COUNT(*) AS COUNT_ORDER
FROM Fact.OrderHistoryExtended
WHERE [Order Date Key]<=DATEADD(dd, -73, '2015-11-13')
GROUP BY [Tax Rate], [Lineage Key], [Salesperson Key]
ORDER BY [Tax Rate], [Lineage Key], [Salesperson Key]
OPTION(RECOMPILE, USE HINT('DISALLOW_BATCH_MODE'));

См. также раздел

Центр производительности для базы данных SQL Azure и ядра СУБД SQL Server
Руководство по архитектуре обработки запросов
Справочник по логическим и физическим операторам Showplan
Соединения
Демонстрация интеллектуальной обработки запросов