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

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

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

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

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

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

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

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

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

Чтобы включить обратную связь о предоставлении памяти в режиме строк в База данных SQL Azure, включите уровень совместимости базы данных 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 позволяет проверить состояние отзыва о предоставлении памяти для инструкции в рамках фактического плана выполнения запроса.

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

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

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

Эта функция была представлена в 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), но не влияет, если хранилище запросов не включено или когда хранилище запросов не находится в состоянии "чтение записи".
• Процентиль и сохраняемость для отзыва о предоставлении памяти доступны в База данных SQL Azure и включены по умолчанию для всех баз данных, как существующих, так и новых.
• Процентиль и сохраняемость для отзывов о предоставлении памяти в настоящее время недоступны в Управляемый экземпляр SQL Azure.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

Связанный контент

• Интеллектуальная обработка запросов в базах данных SQL
• Функции интеллектуальной обработки запросов подробно
• RECONFIGURE (Transact-SQL)
• Наблюдение и настройка производительности
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)