Функции обратной связи по обработке запросов

В этой статье подробно описаны различные функции обратной связи интеллектуальной обработки запросов (IQP). Функции обратной связи по обработке запросов входят в семейство функций интеллектуальной обработки запросов. Обратная связь по обработке запросов — это процесс, с помощью которого обработчик запросов в SQL Server, базе данных Azure SQL и Управляемый экземпляр SQL Azure использует исторические данные о выполнении запроса, чтобы решить, может ли запрос получить помощь от одного или нескольких изменений в способе его компиляции и выполнения. Данные о производительности собираются в хранилище запросов с различными предложениями для улучшения выполнения запросов. В случае успешного выполнения мы сохраняем эти изменения на диске в памяти и (или) в хранилище запросов для использования в будущем. Если предложения не дают достаточного улучшения, они отклоняются, и запрос продолжает выполняться без этой обратной связи.

Функции обратной связи, рассмотренные в этой статье:

• Обратная связь о предоставлении памяти
  • Пакетный режим
  • Режим строки
  • Режим процентиля и сохраняемости
• Степень обратной связи параллелизма
• Отзывы об оценке кратности

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

Иногда запрос выполняется с слишком большим или слишком маленьким объемом памяти. Если объем памяти слишком велик, мы препятствуем параллелизму на сервере. Если он слишком мал, мы можем перекинуться на диск, что является дорогостоящей операцией. Обратная связь с предоставлением памяти пытается запомнить потребности в памяти для предыдущего выполнения (начиная с SQL Server 2022 г. (16.x), несколько выполнений) запроса и соответствующим образом настроить предоставленный запросу грант. Эта функция была выпущена в три волны. Обратная связь о предоставлении памяти в пакетном режиме, за которой следует обратная связь о предоставлении памяти в режиме строки, а в SQL Server 2022 (16.x) мы представляем обратную связь о предоставлении памяти на диске с помощью хранилище запросов и улучшенного алгоритма, известного как предоставление процентиля.

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

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

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

На следующем графе показан пример использования обратной связи по временно предоставляемому буферу памяти в пакетном режиме. Длительность первого выполнения запроса составила 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 МБ не будут пересчитаны для превышения.

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

Скорректированное выделение памяти отображается в фактическом (после выполнения) плане через GrantedMemory свойство .

Это свойство можно увидеть в корневом операторе графического showplan или в выходных данных showplan XML:

<MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="10336" RequiredMemory="1024" DesiredMemory="10336" RequestedMemory="10336" GrantWaitTime="0" GrantedMemory="10336" MaxUsedMemory="9920" MaxQueryMemory="725864" />

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

Пример

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;

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

Для сохранения оптимальности различным значениям параметров могут также потребоваться разные планы запроса. Такой тип запроса называется "зависящим от параметров".

Для планов, зависящих от параметров, функция обратной связи по временно предоставляемому буферу памяти отключается для запроса, имеющего нестабильные требования к памяти. Функция обратной связи о предоставлении памяти отключается после нескольких повторных выполнений запроса, и это можно наблюдать, отслеживая расширенное memory_grant_feedback_loop_disabled событие. Это условие устраняется с помощью режима сохраняемости и процентиля для обратной связи о предоставлении памяти, появившиеся в SQL Server 2022 (16.x). Функция сохраняемости обратной связи о предоставлении памяти требует, чтобы хранилище запросов была включена в базе данных и включена в режим чтения записи.

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

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

Обратную связь можно хранить в кэшированном плане для однократного выполнения. Однако это последовательные выполнения этой инструкции, которые извлекают выгоду из корректировки обратной связи для предоставления памяти. Эта функция применяется к повторному выполнению инструкций. Обратная связь по временно предоставляемому буферу памяти изменяет только кэшированный план. До SQL Server 2022 г. (16.x) изменения не регистрировались в хранилище запросов.

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

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

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

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

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

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

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

-- 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 в контексте соответствующей базы данных:

-- 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

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

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

Пример

ALTER DATABASE [<database name>] SET COMPATIBILITY_LEVEL = 150;

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

Обратная связь о предоставлении памяти не требует хранилище запросов, однако улучшения сохраняемости, появившиеся в SQL Server 2022 г. (16.x), требуют включения хранилище запросов для базы данных и в состоянии записи на чтение. Дополнительные сведения о сохраняемости см. в разделе Процентиль и отзыв о предоставлении памяти в режиме сохраняемости далее в этой статье.

Действие обратной связи предоставления памяти в режиме строки отображается через расширенное memory_grant_updated_by_feedback событие.

Начиная с обратной связи о предоставлении памяти в режиме строки для фактических планов после выполнения отображаются два новых атрибута плана запроса: IsMemoryGrantFeedbackAdjusted и LastRequestedMemory, которые добавляются в MemoryGrantInfo XML-элемент плана запроса.

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

Ниже приведены значения, отображаемые в этом атрибуте:

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

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

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

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

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

Область применения: SQL Server 2022 (16.x) и более поздних версий

Эта функция появилась в SQL Server 2022 г. (16.x), однако это повышение производительности доступно для запросов, работающих на уровне совместимости базы данных 140 (введен в SQL Server 2017 г.) или выше, или для QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n подсказки 140 и выше, а также при включении хранилище запросов для базы данных и состоянии записи на чтение.

• В SQL Server 2022 (16.x) включена обратная связь по процентилям памяти по умолчанию, но не действует, если хранилище запросов не включена и находится в состоянии записи для чтения.
• Сохраняемость для предоставления памяти, CE и обратной связи DOP включена по умолчанию в SQL Server 2022 (16.x), но не действует, если хранилище запросов не включена и находится в состоянии "чтение записи".
• Обратная связь о предоставлении Azure SQL памяти в настоящее время недоступна в базе данных Azure SQL и Управляемый экземпляр SQL Azure.
• Сохраняемость в настоящее время недоступна в базе данных Azure SQL и Управляемый экземпляр SQL Azure.

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

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

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

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

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

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

Сохраняемость также применяется к отзывам DOP и обратной связи CE, которые также описаны в этой статье.

Включение обратной связи по временно предоставляемой памяти: сохраняемость и процентиль

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

ALTER DATABASE <DATABASE NAME> SET COMPATIBILITY LEVEL = 140; -- OR HIGHER

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

Отключение процентиля

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

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE = OFF;

По умолчанию для параметра MEMORY_GRANT_FEEDBACK_PERCENTILE задано значение OFF.

Отключение сохраняемости

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

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

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;

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

По умолчанию для параметра MEMORY_GRANT_FEEDBACK_PERSISTENCE задано значение ON.

Рекомендации по обратной связи с предоставлением памяти

Вы можете просмотреть текущие параметры, выполнив запрос sys.database_scoped_configurations.

Примечание

Эта функция не будет работать, если для обоих BATCH_MODE_MEMORY_GRANT_FEEDBACK и ROW_MODE_MEMORY_GRANT_FEEDBACK задано значение OFF.

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

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

Начиная с SQL Server 2022 г. (16.x) при включении хранилище запросов для вторичных реплик обратная связь о предоставлении памяти реплика учитывается для вторичных реплик в группах доступности. Обратная связь по временно предоставляемому объему памяти может по-разному применять отзывы на основной реплика и на дополнительном реплика. Однако обратная связь по временно предоставляемому объему памяти не сохраняется на вторичных репликах, а при отработке отказа обратная связь о предоставлении памяти от старой основной реплика применяется к новой первичной реплика. Любая обратная связь, применяемая к вторичному реплика, когда она становится основной реплика, теряется. Дополнительные сведения см. в разделе хранилище запросов для вторичных реплик.

Обратная связь по степени параллелизма (DOP)

Применимо к: SQL Server 2022 (16.x) и более поздних версий, Управляемый экземпляр SQL Azure, база данных Azure SQL (предварительная версия)

SQL Server 2022 (16.x) появилась новая функция, называемая обратной связью по степени параллелизма (DOP), которая повышает производительность запросов за счет выявления неэффективности параллелизма для повторяющихся запросов на основе затраченного времени и ожиданий. Обратная связь DOP является частью семейства функций обработки запросов и устраняет неоптимальное использование параллелизма для повторяющихся запросов. Этот сценарий помогает оптимизировать использование ресурсов и повысить масштабируемость рабочих нагрузок, когда чрезмерный параллелизм может вызвать проблемы с производительностью. Вместо того, чтобы выполнять всеохватывающую настройку по умолчанию или вручную для каждого запроса, обратная связь DOP самостоятельно корректирует DOP, чтобы избежать описанных выше проблем.

Примечание

Эта функция в настоящее время доступна в базе данных Azure SQL в качестве ограниченной предварительной версии. Дополнительные сведения и сведения о том, как подать заявку на предварительную версию, см. в разделе Объявление о степени обратной связи с ограниченной предварительной версией Parallelism.

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

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

• Чтобы включить обратную связь DOP, включите конфигурацию DOP_FEEDBACKобласти базы данных в базе данных.

• Для каждой базы данных, в которой используется обратная связь DOP, и в состоянии "Чтение записи" необходимо включить хранилище запросов. Отзывы будут сохраняться в представлении каталога sys.query_store_plan_feedback , когда мы достигаем стабильной степени значения обратной связи параллелизма.

• Отзывы о DOP доступны для запросов, которые работают на уровне совместимости базы данных 160 (появился в SQL Server 2022 (16.x)) или более поздней версии.

• Сохраняется только проверенный отзыв. Если скорректированная DOP приводит к регрессии производительности, обратная связь DOP вернется к последней известной работоспособной DOP. В этом контексте отмененный пользователем запрос также воспринимается как регрессия. Обратная связь DOP не перекомпилировать планы.

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

• Чтобы отключить обратную связь DOP на уровне базы данных, используйте конфигурацию с областьюALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = OFF базы данных.

• Чтобы отключить обратную связь DOP на уровне запроса, используйте указание запроса DISABLE_DOP_FEEDBACK.

• Начиная с SQL Server 2022 (16.x) при включении хранилище запросов для вторичных реплик обратная связь DOP также реплика учитывается для вторичных реплик в группах доступности. Отзывы DOP могут по-разному применять отзывы на основной реплика и на дополнительном реплика. Однако обратная связь DOP не сохраняется на вторичных репликах, а при отработке отказа обратная связь DOP от старой основной реплика не применяется к новым первичным реплика. При отработки отказа обратная связь, примененная к первичной или вторичной реплике, теряется. Дополнительные сведения см. в разделе хранилище запросов для вторичных реплик.

Реализация обратной связи DOP

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

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

Сохраняется только проверенный отзыв. Если скорректированная DOP приводит к регрессии производительности, обратная связь DOP вернется к последней известной работоспособной DOP. В этом контексте отмененный пользователем запрос также воспринимается как регрессия.

Примечание

Отзывы DOP не перекомпилировать планы.

Рекомендации по обратной связи DOP

Минимальное значение DOP для любого запроса, скорректированного с помощью обратной связи DOP, равно 2. Последовательные выполнения не область для обратной связи DOP.

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

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

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

Расширенные события для обратной связи DOP

Для этой функции доступны следующие расширенные события.

• dop_feedback_eligible_query: происходит, когда план запроса становится допустимым для обратной связи DOP. Дополнительные события могут возникать, если происходит повторная компиляция или перезапуск экземпляра SQL Server.
• dop_feedback_provided: происходит, когда обратная связь DOP предоставила данные для заданного запроса. Это событие содержит базовую статистику при первом предоставлении обратной связи и предыдущую статистику обратной связи при предоставлении последующей обратной связи.
• dop_feedback_validation: происходит при проверке статистики среды выполнения запроса относительно базовой или предыдущей статистики обратной связи.
• dop_feedback_stabilized: происходит при стабилизации обратной связи DOP для запроса.
• dop_feedback_reverted: происходит при отмене обратной связи DOP. Событие срабатывает, если проверка отзывов завершается сбоем при первом предоставлении обратной связи. Система вернется к состоянию отсутствия обратной связи.
• dop_feedback_analysis_stopped: происходит при остановке анализа обратной связи DOP для запроса.

Отзывы об оценке кратности (CE)

Область применения: SQL Server 2022 (16.x) и более поздних версий.

Начиная с SQL Server 2022 (16.x)), обратная связь оценки кратности (CE) входит в семейство функций интеллектуальной обработки запросов и обращается к неоптимальным планам выполнения запросов для повторяющихся запросов, когда эти проблемы являются результатом неправильных предположений модели CE. Этот сценарий помогает снизить риски регрессии, связанные с оценкой кратности по умолчанию при обновлении предыдущих версий ядра СУБД.

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

• Начиная с SQL Server 2022 г. (16.x) при включении хранилище запросов для вторичных реплик отзывы ce не реплика для вторичных реплик в группах доступности. Отзывы CE в настоящее время полезны только для первичных реплик. Дополнительные сведения см. в разделе хранилище запросов для вторичных реплик.

Общие сведения об оценке кратности

Оценка кратности (CE) — это средство, благодаря которому оптимизатор запросов может оценить общее количество строк, обработанных на каждом уровне плана запроса. Оценка кратности в SQL Server главным образом определяется на основе гистограмм, которые создаются автоматически или вручную после создания индексов или статистик. Иногда для определения количества элементов SQL Server также использует сведения об ограничениях и логической реструктуризации запросов.

В разных версиях ядра СУБД используются разные предположения модели CE в зависимости от того, как распределяются и запрашиваются данные. Дополнительные сведения см. в статье о версиях CE.

Реализация обратной связи CE

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

1. Обратная связь CE определяет предположения, связанные с моделью, и оценивает, точны ли они для повторяющихся запросов.

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

3. Если это улучшает качество плана, старый план запроса заменяется планом запроса, который использует соответствующее указание запроса USE HINT, корректирующее модель оценки, реализованную с помощью механизма Указание хранилища запросов.

Сохраняется только проверенный отзыв. Обратная связь CE не используется для этого запроса, если скорректированное предположение модели приводит к снижению производительности. В этом контексте отмененный пользователем запрос также воспринимается как регрессия.

Сценарии обратной связи CE

Обратная связь CE устраняет распознанные проблемы регрессии, возникающие из-за неправильных предположений модели CE при использовании стандартной CE (CE120 или более поздней версии), и может выборочно использовать различные предположения модели. К таким сценариям относятся корреляция, присоединение и цель строки оптимизатора.

Correlation

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

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

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

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

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

USE AdventureWorks2016_EXT;
GO
SELECT AddressID, AddressLine1, AddressLine2
FROM Person.Address
WHERE StateProvinceID = 79 AND City = N'Redmond';
GO

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

Дополнительные сведения см. в статье о версиях CE.

Присоединение к автономному объекту

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

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

• Базовая автономность (по умолчанию для CE120 и более поздних версий) предполагает отсутствие корреляции между предикатами соединения и подчиненными фильтрами,

где сначала вычисляется избирательность соединения, а затем учитывается коэффициент выбора фильтра.

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

USE AdventureWorksDW2016_EXT;
GO
SELECT *
FROM dbo.FactCurrencyRate AS f
INNER JOIN dbo.DimDate AS d ON f.DateKey = d.DateKey
WHERE d.MonthNumberOfYear = 7 AND f.CurrencyKey = 3 AND f.AverageRate > 1;
GO

Дополнительные сведения см. в статье о версиях CE.

Цель строки оптимизатора

Когда оптимизатор запросов оценивает кратность плана выполнения, он обычно предполагает, что должны быть обработаны все подходящие строки из всех таблиц. Однако из-за наличия некоторых шаблонов запросов оптимизатор запросов выполняет поиск плана, который будет возвращать меньшее количество строк для сокращения операций ввода-вывода. Если в запросе указано целевое количество строк (цель строки), которое можно ожидать во время выполнения с помощью ключевых слов TOP, IN или EXISTS, указания запроса FAST или оператора SET ROWCOUNT, цель строки будет использоваться как часть процесса оптимизации запроса, как показано в следующем примере:

USE AdventureWorks2016_EXT;
GO
SELECT TOP 1 soh.*
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID;
GO

Когда применяется план цели строки, расчетное количество строк в плане запроса уменьшается, так как оптимизатор запросов предполагает, что для достижения цели строки необходимо обработать меньшее количество строк.

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

В плане выполнения нет атрибута, специфичного для обратной связи CE, но для указания хранилище запросов будет указан атрибут. Найдите , чтобы иметь QueryStoreStatementHintSource значение CE feedback.

Рекомендации по обратной связи ce

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

Действия обратной связи CE отображаются с помощью XEvents query_feedback_analysis и query_feedback_validation.

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

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

Чтобы отключить обратную связь CE на уровне базы данных, используйте конфигурацию с областьюALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF базы данных.

Чтобы отключить обратную связь CE на уровне запроса, используйте указание запроса DISABLE_CE_FEEDBACK.

Если у запроса есть план запроса, принудительно выполненный через хранилище запросов, обратная связь CE не будет использоваться для этого запроса.

Если запрос использует жестко закодированные указания запроса или использует указания хранилища запросов, установленные пользователем, обратная связь CE не будет использоваться для этого запроса. Дополнительные сведения см. в разделах Указания (Transact-SQL) — Запрос и Указание хранилища запросов.

Отзывы и сообщения о проблемах

Для получения отзывов или вопросов по электронной почте CEFfeedback@microsoft.com

Дальнейшие действия

• Интеллектуальная обработка запросов
• Сведения об интеллектуальной обработке запросов
• Настройка параметра конфигурации сервера max degree of parallelism
• Оценка кратности
• RECONFIGURE (Transact-SQL)
• Наблюдение и настройка производительности
• Настройка параллельных операций с индексами
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)