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

Применимо к: SQL Server 2017 (14.x) и более поздних версий База данных SQL AzureAzure SQL Управляемый экземпляр SQL база данных в Microsoft Fabric

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

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

Примечание.

Сведения о других функциях обратной связи для запросов см. в статьях обратная связь по оценке кратности (CE) и обратная связь по степени параллелизма (DOP).

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

Применяется к: SQL Server 2017 (14.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure (уровень совместимости базы данных 140 и выше).

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

На следующем графике показан один из примеров использования адаптивной обратной связи по выделению памяти в пакетном режиме. При первом выполнении запроса длительность составила 88 секунд из-за значительного объема сброса на диск.

DECLARE @EndTime AS DATETIME = '2016-09-22 00:00:00.000';
DECLARE @StartTime AS 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 секунд), сбросы на диск полностью устранены, а объём выделяемой памяти больше:

Снимок экрана: граф предоставленных и разливаемых MOB памяти, указывающий на отсутствие разливов.

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

При чрезмерном выделении памяти, если выделенный объём памяти более чем в два раза превышает объём фактически используемой памяти, механизм обратной связи по выделению памяти пересчитывает объём выделяемой памяти и обновляет кэшированный план. Планы с выделением памяти менее 1 МБ не будут пересчитываться при превышении этого объёма.

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

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

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

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

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

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

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

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

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

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

В SQL Server 2017 (14.x):

ALTER DATABASE SCOPED CONFIGURATION
SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

В SQL Server 2019 (15.x) и более поздних версиях и в базе данных SQL Azure:
```
ALTER DATABASE SCOPED CONFIGURATION
SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;
```

Если этот параметр включен, он будет отображаться как включенный в представлении sys.database_scoped_configurations.

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

В SQL Server 2017 (14.x):

ALTER DATABASE SCOPED CONFIGURATION
SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

В SQL Server 2019 (15.x) и более поздних версиях и в базе данных SQL Azure:
```
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 2019 (15.x) и более поздних версий, Базы данных SQL Azure и Управляемого экземпляра SQL Azure (уровень совместимости базы данных 150 и выше).

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

Чтобы включить обратную связь по выделению памяти для построчного режима в базе данных Azure SQL, установите для базы данных, к которой вы подключены при выполнении запроса, уровень совместимости 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 % предоставленной памяти, то обратная связь о предоставлении памяти не активируется.
Нет: обратная связь отключена	Если обратная связь по выделению памяти постоянно срабатывает и чередуется между увеличением и уменьшением объёма выделяемой памяти, компонент Database Engine отключит обратную связь по выделению памяти для этого оператора.
Да: настройка	Применён механизм обратной связи для выделения памяти; при следующем выполнении он может быть дополнительно скорректирован.
Да: корректировка процентиля	Обратная связь по выделению памяти применяется с использованием алгоритма процентильного выделения, который учитывает более длительную историю, а не только самое последнее выполнение.
Да: стабильно	Обратная связь по выделению памяти была применена, и объём выделяемой памяти теперь стабилен, то есть объём памяти, который был в последний раз выделен для предыдущего выполнения, совпадает с объёмом памяти, выделенным для текущего выполнения.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Обратную связь по выделению памяти для построчного режима можно отключить на уровне базы данных или отдельной инструкции, при этом сохранив уровень совместимости базы данных 150 или выше. Чтобы отключить обратную связь о предоставлении памяти в режиме строк для всех выполнений запросов, исходящих из базы данных, выполните инструкции Transact-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 Azure и SQL Server 2022 (16.x).

Используйте для базы данных, к которой вы подключены при выполнении запроса, уровень совместимости 140 или выше. Это можно изменить с помощью ALTER DATABASE:

ALTER DATABASE <database_name>
SET COMPATIBILITY LEVEL = 140; -- or a higher value

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

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

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

ALTER DATABASE SCOPED CONFIGURATION
SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = OFF;

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

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

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

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

ALTER DATABASE SCOPED CONFIGURATION
SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;

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

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