Всем привет! В преддверии старта курса «MS SQL Server разработчик», подготовили для вас еще один интересный перевод.
Обратная связь по грантам памяти (Memory Grant Feedback) в более ранних версиях SQL Server (до SQL Server 2019 или 15.x) была реализована только для запросов, выполняющихся в пакетном режиме (batch mode). Запросы в пакетном режиме выполняют сканирование и вычисление до 900 строк одновременно, в отличие от запросов в строковом режиме (row mode), когда за раз обрабатывается только одна строка. В версии 15.x обратная связь по грантам памяти была расширена для поддержки запросов в строковом режиме.
Что такое обратная связь по грантам памяти? Это процесс корректировки вычисления памяти, необходимой для запроса с учетом того, сколько памяти было использовано при его предыдущих выполнениях. Это означает, что если кэшированный запрос использовал слишком много памяти при последнем выполнении, то SQL Server уменьшит выделение памяти при его следующем выполнении. Или если SQL Server обнаружил запрос, использующий диск из-за того, что в последний раз ему было выделено недостаточно памяти, то он увеличит память для запроса. Целью обратной связи по грантам памяти является корректировка требований к памяти при каждом выполнении запроса до тех пор, пока запрос не будет использовать объем памяти, соответствующий количеству обрабатываемых строк.
Функциональность Memory Grant Feedback (и в пакетном, и в строковом режимах) является частью семейства функций Intelligent Query Processing (IQP, интеллектуальная обработка запросов). На рисунке 1 представлена диаграмма, показывающая все средства IQP, присутствующие в Azure SQL Database и SQL Server 2019, а также функции, которые изначально были частью Adaptive Query Processing (адаптивной обработки запросов), включенной в предыдущие версии Azure SQL Database и SQL Server 2017.
Рисунок 1. Intelligent Query Processing (интеллектуальная обработка запросов)
Чтобы лучше понять, как работает обратная связь по грантам памяти, давайте посмотрим на выделение памяти для запроса при его первом и последующих выполнениях.
Чтобы посмотреть, как изменяется выделение памяти со временем, будем выполнять один и тот же запрос несколько раз. При каждом запуске будем смотреть сколько памяти было выделено и сколько использовано, а также дополнительную информацию обратной связи, помогающую Database Engine корректировать требования к памяти. Для теста будем использовать базу данных WideWorldImporters, которую можно скачать отсюда. Тестовый запрос для проверки обратной связи по грантам памяти в строковом режиме приведен в листинге 1.
Листинг 1. Тестовый запрос
Перед тем как выполнить этот запрос, позвольте мне рассказать, почему оценка необходимой памяти может быть неправильной, и еще сделать некоторую предварительную настройку.
Существует много факторов, влияющих на требуемый объем памяти для запроса. При вычислении общего необходимого объема памяти оптимизатор запросов должен учитывать требования к памяти для каждой операции в плане выполнения. Вот некоторые параметры, которые учитываются оптимизатором при вычислении памяти, необходимой для выполнения запроса:
Для корректного вычисления необходимой памяти SQL Server должен учитывать множество аспектов того, где при выполнении запроса может потребоваться память. Если выделяется слишком много памяти, то SQL Server тратит память впустую, когда она могла бы быть использована другими процессами. Или если выделяется недостаточно памяти, то запрос будет сливать данные на диск, что является дорогостоящей операцией.
SQL Server не всегда хорошо справляется с вычислением объема памяти с первого раза. Устаревшая или неточная статистика — наиболее распространенная причина, из-за которой SQL Server вычисляет неправильный объем памяти. Но даже если статистика верна, SQL Server может переоценить или недооценить необходимый объем памяти по какой-либо другой причине. Одна из этих причин заключается в том, что SQL Server неправильно оценивает размер строки (Estimated Row Size) для возвращаемых столбцов. Далее на примере мы увидим как завышение размера строки приводит к тому, что запрос получает предупреждение об избыточном выделении памяти (excessive grant warning) из-за трех столбцов NVARCHAR (MAX), которые всегда равны NULL.
Но теперь, если SQL Server неправильно вычисляет необходимую память при первом выполнении запроса, то для последующих выполнений используется обратная связь по грантам памяти. При каждом выполнении запроса анализируется информация о выделенной памяти для предыдущего выполнения этого запроса. Если при предыдущем запуске выделение памяти было неправильным, то Database Engine корректирует выделение памяти.
Для того чтобы продемонстрировать, как со временем выделение памяти подстраивается под запрос, нужно сделать некоторые подготовительные работы. Первое — изменить уровень совместимости базы данных WideWorldImporters на 150. Это нужно сделать, так как обратная связь по грантам памяти в строковом режиме (Memory Grant Feedback – Row Mode) доступна только для баз данных с уровнем совместимости 150. Код в листинге 2 изменяет уровень совместимости базы данных на 150.
Листинг 2. Установка уровня совместимости на 150
Следующее, что нужно сделать, — это очистить кэш процедур, чтобы при запуске тестового запроса для него был создан новый план.
Листинг 3. Очистка кэша процедур
После предварительной настройки можно выполнить запрос первый раз (листинг 1).
Посмотреть объем памяти, используемый запросом, и обратную связь, которую предоставляет Database Engine, можно в фактическом плане выполнения.
При первом запуске получаем план выполнения, показанный на рисунке 2.
Рисунок 2. План выполнения для первого запуска запроса из листинга 1
Как вы видите, на операторе SELECT есть предупреждающий знак. При наведении курсора мыши на SELECT отображается информация, показанная на рисунке 3.
Рисунок 3. Свойства SELECT при первом запуске первого исполнения.
Для SELECT есть предупреждение “ExcessiveGrant”. Запросу было выделено 1 234 880 КБ памяти, но он использовал только 11 296 КБ. Это означает, что запросу было выделено гораздо больше памяти, чем требовалось.
В SQL Server 15.x в свойствах оператора SELECT в раздел MemoryGrantInfo были добавлены два новых атрибута: IsMemoryGrantFeedbackAdjusted и LastRequestedMemory. Эти два атрибута обеспечивают обратную связь, необходимую для корректировки выделяемой памяти при последующих выполнениях запроса. Вы можете увидеть эти два новых атрибута, просмотрев MemoryGrantInfo в графическом плане выполнения. Для этого в плане выполнения наведите курсор мыши на значок SELECT, щелкните правой кнопкой мыши и выберите Properties (Свойства) в появившемся меню. Эти новые свойства запроса отображаются в разделе MemoryGrantInfo, показанном на рисунке 4.
Рисунок 4. Информация о выделении памяти
Свойство IsMemoryGrantFeedbackAdjusted при первом выполнении равно NoFirstExecution, а свойство LastRequestedMemory равно нулю. Параметр RequestedMemory имеет значение 1 234 880. Это объем памяти, который был запрошен при выполнении запроса в первый раз. Для того чтобы разобраться как работают новые свойства обратной связи, давайте посмотрим на значения, которые они могут принимать.
Эти два новых свойства обратной связи устанавливаются каждый раз при выполнении запроса. Значения этих свойств, основываются на том, что Database Engine узнал во время выполнения запроса. Эта информация сохраняется в кэшированном плане для использования при последующих выполнениях этого запроса для определения необходимости корректировки выделения памяти.
Свойство IsMemoryGrantFeedbackAdjusted может принимать пять различных значений, которые перечислены таблице 1.
Таблица 1. Возможные значения IsMemoryGrantFeedbackAdjusted
По таблице 1 видно, что наше предыдущее выполнение запроса было первым, так как значение равно FirstExecution.
Свойство LastRequestedMemory показывает объем запрошенной памяти в килобайтах (КБ) при предыдущем выполнении запроса. Поскольку запрос выполнялся впервые, то значение было равным нулю.
Давайте запустим запрос второй раз и посмотрим как будут использоваться эти свойства для улучшения выделения памяти.
Как видно на рисунке 5, после второго запуска исчез предупреждающий знак, присутствовавший при первом выполнении.
Рисунок 5. План для второго выполнения запроса
При втором запуске MemoryGrantInfo показывает, что изменились некоторые значения свойств обратной связи, а также значение RequestedMemory. Свойства MemoryGrantInfo для второго запуска показаны на рисунке 6.
Рисунок 6. Свойства MemoryGrantInfo для второго выполнения
Свойство IsMemoryGrantFeedbackAdjusted теперь принимает значение YesAdjusting. Этот статус говорит о том, что для второго выполнения была применена обратная связь. Кроме того, LastRequestedMemory теперь принимает значение 1 234 880 — это запрашиваемая память (RequestedMemory) для первого выполнения запроса. Свойство RequestedMemory показывает, что при втором выполнении запроса запрашивается только 21 120 КБ памяти. Это значение значительно меньше объема памяти, запрашиваемого при первом выполнении.
Перед повторным выполнением запроса следует отметить один нюанс, с которым я столкнулся. Я обнаружил, что если между повторными выполнениями запросов проходит много времени, то план выполнения может быть удален из кэша процедур. Когда это происходит, свойство IsMemoryGrantFeedbackAdjusted будет принимать значение NoFirstExecution. Это указывает на то, что запрос только что был помещен в кэш. Поэтому чтобы увидеть как работает обратная связь, нужно довольно быстро запускать его во второй раз для избежания проблем с вытеснением кэшированного плана.
На рисунке 7 показано значение MemoryGrantInfo для третьего выполнения запроса.
Рисунок 7. Третье выполнение
IsMemoryGrantFeedbackAdjusted теперь равно YesStable. Это означает, было выделено столько же памяти, как и при втором выполнении. Это можно проверить, сравнив значения LastRequestedMemory и RequestedMemory. Они совпадают. Для нашего запроса обратная связь о грантах памяти позволила запросу скорректировать необходимый объем памяти после трех выполнений.
Бывают запросы для которых каждое выполнение требует разного объема памяти. Если такое происходит, то SQL Server прекращает попытки корректировать размер выделяемой памяти и отключает обратную связь для такого запроса. Одна из ситуаций, когда это происходит, — запуск хранимой процедуры, в которую при каждом выполнении передаются разные значения параметров.
Чтобы воспроизвести эту ситуацию, когда SQL Server отключает обратную связь по грантам памяти, выполним несколько раз хранимую процедуру с разными параметрами. Таким образом, при каждом выполнении запрос внутри процедуры будет требовать разного объема памяти из-за разного количества возвращаемых записей, зависящих от параметра. Код хранимой процедуры приведен в листинге 4.
Листинг 4. Тестовая хранимая процедура
Хранимая процедура принимает параметр с именем
Листинг 5. Код для вызова хранимой процедуры
Взглянув на код в листинге 5, вы видите, что хранимая процедура выполняется 35 раз. При каждом запуске хранимой процедуры значение параметра
Запустим код из листинга 5 в SSMS и посмотрим фактический план выполнения. Видно, что для запусков со второго до 32-го значение IsMemoryGrantFeedbackAdjust было YesAdjusting. Но при 33-ем выполнении SQL Server прекратил попытки корректировать память для хранимой процедуры и отключил обратную связь. На рисунке 8 вы можете увидеть свойства MemoryGrantInfo для 33-го выполнения хранимой процедуры.
Рисунок 8. Обратная связь по выделению памяти отключена при 33-ем выполнении
Как видно, параметр IsMemoryGrantFeedbackAdjusted имеет значение NoFeedbackDisabled. Это означает, что Database Engine не смог использовать обратную связь для получения стабильного выделения памяти для хранимой процедуры и было решено отключить обратную связь для этого запроса. Для всех последующих выполнений хранимой процедуры (после 33-го запуска) обратная связь отключена.
Иногда SQL Server правильно определяет объем памяти с первого раза, поэтому обратная связь не используется. Давайте посмотрим на примере кода в листинге 6.
Листинг 6. Пример с правильным выделением памяти
При запуске получаем следующий план выполнения.
Рисунок 9. План выполнения при запуске кода из листинга 6.
Свойство IsMemoryGrantFeedback показывает, что это первое выполнение. Кроме того, запрос запросил 1024 КБ памяти, что видно в RequestedMemory (рисунок 10).
Рисунок 10. MemoryGrantInfo для первого выполнения запроса
Информация о выделении памяти для второго выполнения показана на рисунке 11.
Рисунок 11. MemoryGrantInfo для второго выполнения
Для второго выполнения IsMemoryGrantFeedbackAdjusted имеет значение NoAccurateGrant. Это означает, что для второго выполнения не потребовалось большее количество памяти, поэтому обратная связь не используется. Это можно проверить, посмотрев на то, что RequestedMemory одинаковая на рисунках 10 и 11.
Если по какой-либо причине вы хотите отключить обратную связь о грантах памяти в строковом режиме (Memory Grant Feedback – Row Mode), то для этого есть несколько способов. Первый — установить для базы данных уровень совместимости менее 150. С этим способом только одна проблема: также будут отключены все другие функции, которые появились в версии 15.x. Или, вы можете отключить обратную связь на уровне базы данных через опцию
Листинг 7. Отключение обратной связи по выделению памяти для базы данных
Здесь отключается обратная связь о грантах памяти на уровне базы данных. Теперь запросы для WideWorldImporters не будут использовать обратную связь в строковом режиме. Но это не единственный способ отключить обратную связь. Вы также можете отключить обратную связь для конкретного запроса, используя HINT, как показано в листинге 8.
Листинг 8. Использование HINT для отключения обратной связи
В листинге 8 используется хинт
Обратная связь по грантам памяти в пакетном режиме (batch mode) существует уже довольно давно. Но только в версии 15.x эта обратная связь стала доступна для запросов в строковом режиме (row mode). Все, что нужно для включения обратной связи для запросов в строковом режиме — это установить уровень совместимости базы данных в 150. Это, конечно, заработает, если вы используете текущую версию Azure SQL Database или SQL Server 2019. Когда включен функционал обратной связи о грантах памяти в строковом режиме, то SQL Server использует информацию о выделении памяти при предыдущих выполнениях кэшированного запроса, чтобы скорректировать выделение памяти для текущего выполнения. Корректировка необходимой памяти с помощью обратной связи при каждом выполнении запроса рано или поздно автоматически приведет к вычислению правильного объема памяти.
Узнать подробнее о курсе.
Если оптимизатор неправильно вычисляет необходимый объем памяти для выполнения запроса, то это будет либо пустая трата памяти, которую мог бы использовать другой процесс, либо будет слив данных на диск (disk spill). Для решения этой проблемы Microsoft добавила обратную связь по грантам памяти (Memory Grant Feedback). В этой статье Грег Ларсен (Greg Larsen) объясняет, как это работает.
Обратная связь по грантам памяти (Memory Grant Feedback) в более ранних версиях SQL Server (до SQL Server 2019 или 15.x) была реализована только для запросов, выполняющихся в пакетном режиме (batch mode). Запросы в пакетном режиме выполняют сканирование и вычисление до 900 строк одновременно, в отличие от запросов в строковом режиме (row mode), когда за раз обрабатывается только одна строка. В версии 15.x обратная связь по грантам памяти была расширена для поддержки запросов в строковом режиме.
Что такое обратная связь по грантам памяти? Это процесс корректировки вычисления памяти, необходимой для запроса с учетом того, сколько памяти было использовано при его предыдущих выполнениях. Это означает, что если кэшированный запрос использовал слишком много памяти при последнем выполнении, то SQL Server уменьшит выделение памяти при его следующем выполнении. Или если SQL Server обнаружил запрос, использующий диск из-за того, что в последний раз ему было выделено недостаточно памяти, то он увеличит память для запроса. Целью обратной связи по грантам памяти является корректировка требований к памяти при каждом выполнении запроса до тех пор, пока запрос не будет использовать объем памяти, соответствующий количеству обрабатываемых строк.
Функциональность Memory Grant Feedback (и в пакетном, и в строковом режимах) является частью семейства функций Intelligent Query Processing (IQP, интеллектуальная обработка запросов). На рисунке 1 представлена диаграмма, показывающая все средства IQP, присутствующие в Azure SQL Database и SQL Server 2019, а также функции, которые изначально были частью Adaptive Query Processing (адаптивной обработки запросов), включенной в предыдущие версии Azure SQL Database и SQL Server 2017.
Рисунок 1. Intelligent Query Processing (интеллектуальная обработка запросов)
Чтобы лучше понять, как работает обратная связь по грантам памяти, давайте посмотрим на выделение памяти для запроса при его первом и последующих выполнениях.
Смотрим как гранты памяти изменяются со временем
Чтобы посмотреть, как изменяется выделение памяти со временем, будем выполнять один и тот же запрос несколько раз. При каждом запуске будем смотреть сколько памяти было выделено и сколько использовано, а также дополнительную информацию обратной связи, помогающую Database Engine корректировать требования к памяти. Для теста будем использовать базу данных WideWorldImporters, которую можно скачать отсюда. Тестовый запрос для проверки обратной связи по грантам памяти в строковом режиме приведен в листинге 1.
USE WideWorldImporters;
GO
SELECT * FROM Sales.Orders
ORDER BY OrderDate;Листинг 1. Тестовый запрос
Перед тем как выполнить этот запрос, позвольте мне рассказать, почему оценка необходимой памяти может быть неправильной, и еще сделать некоторую предварительную настройку.
Почему оценка необходимой памяти может быть ошибочной
Существует много факторов, влияющих на требуемый объем памяти для запроса. При вычислении общего необходимого объема памяти оптимизатор запросов должен учитывать требования к памяти для каждой операции в плане выполнения. Вот некоторые параметры, которые учитываются оптимизатором при вычислении памяти, необходимой для выполнения запроса:
- Кардинальность таблиц, участвующих в запросе
- Выбираемые колонки
- Ориентировочный размер строки
- Есть ли необходимость в сортировке и / или соединении (join) данных
- Выполняется ли запрос параллельно
Для корректного вычисления необходимой памяти SQL Server должен учитывать множество аспектов того, где при выполнении запроса может потребоваться память. Если выделяется слишком много памяти, то SQL Server тратит память впустую, когда она могла бы быть использована другими процессами. Или если выделяется недостаточно памяти, то запрос будет сливать данные на диск, что является дорогостоящей операцией.
SQL Server не всегда хорошо справляется с вычислением объема памяти с первого раза. Устаревшая или неточная статистика — наиболее распространенная причина, из-за которой SQL Server вычисляет неправильный объем памяти. Но даже если статистика верна, SQL Server может переоценить или недооценить необходимый объем памяти по какой-либо другой причине. Одна из этих причин заключается в том, что SQL Server неправильно оценивает размер строки (Estimated Row Size) для возвращаемых столбцов. Далее на примере мы увидим как завышение размера строки приводит к тому, что запрос получает предупреждение об избыточном выделении памяти (excessive grant warning) из-за трех столбцов NVARCHAR (MAX), которые всегда равны NULL.
Но теперь, если SQL Server неправильно вычисляет необходимую память при первом выполнении запроса, то для последующих выполнений используется обратная связь по грантам памяти. При каждом выполнении запроса анализируется информация о выделенной памяти для предыдущего выполнения этого запроса. Если при предыдущем запуске выделение памяти было неправильным, то Database Engine корректирует выделение памяти.
Настройка для первого запуска
Для того чтобы продемонстрировать, как со временем выделение памяти подстраивается под запрос, нужно сделать некоторые подготовительные работы. Первое — изменить уровень совместимости базы данных WideWorldImporters на 150. Это нужно сделать, так как обратная связь по грантам памяти в строковом режиме (Memory Grant Feedback – Row Mode) доступна только для баз данных с уровнем совместимости 150. Код в листинге 2 изменяет уровень совместимости базы данных на 150.
ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 150;
GOЛистинг 2. Установка уровня совместимости на 150
Следующее, что нужно сделать, — это очистить кэш процедур, чтобы при запуске тестового запроса для него был создан новый план.
USE WideWorldImporters;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GOЛистинг 3. Очистка кэша процедур
После предварительной настройки можно выполнить запрос первый раз (листинг 1).
Первое выполнение запроса
Посмотреть объем памяти, используемый запросом, и обратную связь, которую предоставляет Database Engine, можно в фактическом плане выполнения.
При первом запуске получаем план выполнения, показанный на рисунке 2.
Рисунок 2. План выполнения для первого запуска запроса из листинга 1
Как вы видите, на операторе SELECT есть предупреждающий знак. При наведении курсора мыши на SELECT отображается информация, показанная на рисунке 3.
Рисунок 3. Свойства SELECT при первом запуске первого исполнения.
Для SELECT есть предупреждение “ExcessiveGrant”. Запросу было выделено 1 234 880 КБ памяти, но он использовал только 11 296 КБ. Это означает, что запросу было выделено гораздо больше памяти, чем требовалось.
В SQL Server 15.x в свойствах оператора SELECT в раздел MemoryGrantInfo были добавлены два новых атрибута: IsMemoryGrantFeedbackAdjusted и LastRequestedMemory. Эти два атрибута обеспечивают обратную связь, необходимую для корректировки выделяемой памяти при последующих выполнениях запроса. Вы можете увидеть эти два новых атрибута, просмотрев MemoryGrantInfo в графическом плане выполнения. Для этого в плане выполнения наведите курсор мыши на значок SELECT, щелкните правой кнопкой мыши и выберите Properties (Свойства) в появившемся меню. Эти новые свойства запроса отображаются в разделе MemoryGrantInfo, показанном на рисунке 4.
Рисунок 4. Информация о выделении памяти
Свойство IsMemoryGrantFeedbackAdjusted при первом выполнении равно NoFirstExecution, а свойство LastRequestedMemory равно нулю. Параметр RequestedMemory имеет значение 1 234 880. Это объем памяти, который был запрошен при выполнении запроса в первый раз. Для того чтобы разобраться как работают новые свойства обратной связи, давайте посмотрим на значения, которые они могут принимать.
Новые свойства обратной связи MemoryGrantInfo
Эти два новых свойства обратной связи устанавливаются каждый раз при выполнении запроса. Значения этих свойств, основываются на том, что Database Engine узнал во время выполнения запроса. Эта информация сохраняется в кэшированном плане для использования при последующих выполнениях этого запроса для определения необходимости корректировки выделения памяти.
Свойство IsMemoryGrantFeedbackAdjusted может принимать пять различных значений, которые перечислены таблице 1.
Таблица 1. Возможные значения IsMemoryGrantFeedbackAdjusted
| Значение IsMemoryGrantFeedbackAdjusted | Описание |
| No: firstexecution | Обратная связь не используется для первой компиляции и связанным с ней выполнением. |
| No: accurate grant | Если слива данных на диск нет и запрос использует не менее 50% выделенной памяти, то обратная связь по грантам памяти не используется. |
| No: feedback disabled | Если обратная связь непрерывно активируется и происходит постоянное увеличение и уменьшение памяти, то обратная связь для данного запроса отключается. |
| Yes: adjusting | Была применена обратная связь и она может быть подкорректирована при последующих выполнениях. |
| Yes: stable | Была применена обратная связь и объем выделенной памяти стабилен. это означает, что выделенная память для предыдущего запуска совпадает с выделенной памятью для текущего выполнения. |
По таблице 1 видно, что наше предыдущее выполнение запроса было первым, так как значение равно FirstExecution.
Свойство LastRequestedMemory показывает объем запрошенной памяти в килобайтах (КБ) при предыдущем выполнении запроса. Поскольку запрос выполнялся впервые, то значение было равным нулю.
Давайте запустим запрос второй раз и посмотрим как будут использоваться эти свойства для улучшения выделения памяти.
Второе выполнение запроса
Как видно на рисунке 5, после второго запуска исчез предупреждающий знак, присутствовавший при первом выполнении.
Рисунок 5. План для второго выполнения запроса
При втором запуске MemoryGrantInfo показывает, что изменились некоторые значения свойств обратной связи, а также значение RequestedMemory. Свойства MemoryGrantInfo для второго запуска показаны на рисунке 6.
Рисунок 6. Свойства MemoryGrantInfo для второго выполнения
Свойство IsMemoryGrantFeedbackAdjusted теперь принимает значение YesAdjusting. Этот статус говорит о том, что для второго выполнения была применена обратная связь. Кроме того, LastRequestedMemory теперь принимает значение 1 234 880 — это запрашиваемая память (RequestedMemory) для первого выполнения запроса. Свойство RequestedMemory показывает, что при втором выполнении запроса запрашивается только 21 120 КБ памяти. Это значение значительно меньше объема памяти, запрашиваемого при первом выполнении.
Перед повторным выполнением запроса следует отметить один нюанс, с которым я столкнулся. Я обнаружил, что если между повторными выполнениями запросов проходит много времени, то план выполнения может быть удален из кэша процедур. Когда это происходит, свойство IsMemoryGrantFeedbackAdjusted будет принимать значение NoFirstExecution. Это указывает на то, что запрос только что был помещен в кэш. Поэтому чтобы увидеть как работает обратная связь, нужно довольно быстро запускать его во второй раз для избежания проблем с вытеснением кэшированного плана.
Третье выполнение запроса
На рисунке 7 показано значение MemoryGrantInfo для третьего выполнения запроса.
Рисунок 7. Третье выполнение
IsMemoryGrantFeedbackAdjusted теперь равно YesStable. Это означает, было выделено столько же памяти, как и при втором выполнении. Это можно проверить, сравнив значения LastRequestedMemory и RequestedMemory. Они совпадают. Для нашего запроса обратная связь о грантах памяти позволила запросу скорректировать необходимый объем памяти после трех выполнений.
Когда необходимый объем памяти не стабилен
Бывают запросы для которых каждое выполнение требует разного объема памяти. Если такое происходит, то SQL Server прекращает попытки корректировать размер выделяемой памяти и отключает обратную связь для такого запроса. Одна из ситуаций, когда это происходит, — запуск хранимой процедуры, в которую при каждом выполнении передаются разные значения параметров.
Чтобы воспроизвести эту ситуацию, когда SQL Server отключает обратную связь по грантам памяти, выполним несколько раз хранимую процедуру с разными параметрами. Таким образом, при каждом выполнении запрос внутри процедуры будет требовать разного объема памяти из-за разного количества возвращаемых записей, зависящих от параметра. Код хранимой процедуры приведен в листинге 4.
USE WideWorldImporters;
GO
CREATE OR ALTER PROC TestProc (@KeyValue int)
AS
SELECT * FROM Sales.Orders
WHERE OrderID > 1 and OrderID < @KeyValue
ORDER BY OrderDateЛистинг 4. Тестовая хранимая процедура
Хранимая процедура принимает параметр с именем
@KeyValue. Код для тестирования хранимой процедуры приведен в листинге 5.DECLARE @I INT = 1;
DECLARE @TestKeyValue INT;
WHILE @I < 35
BEGIN
IF @I % 2 = 0
EXEC TestProc @KeyValue = 20000;
ELSE
EXEC TestProc @KeyValue = 100;
SET @I = @I + 1;
ENDЛистинг 5. Код для вызова хранимой процедуры
Взглянув на код в листинге 5, вы видите, что хранимая процедура выполняется 35 раз. При каждом запуске хранимой процедуры значение параметра
@KeyValue чередуется между 20 000 и 100. Когда для параметра установлено значение 20 000, запрос возвращает 19 998 строк, но если значение параметра равно 100, то возвращается только 98 строк. Как вы можете догадаться, при каждом выполнении будет требоваться разный объем памяти, потому что количество строк сильно отличается.Запустим код из листинга 5 в SSMS и посмотрим фактический план выполнения. Видно, что для запусков со второго до 32-го значение IsMemoryGrantFeedbackAdjust было YesAdjusting. Но при 33-ем выполнении SQL Server прекратил попытки корректировать память для хранимой процедуры и отключил обратную связь. На рисунке 8 вы можете увидеть свойства MemoryGrantInfo для 33-го выполнения хранимой процедуры.
Рисунок 8. Обратная связь по выделению памяти отключена при 33-ем выполнении
Как видно, параметр IsMemoryGrantFeedbackAdjusted имеет значение NoFeedbackDisabled. Это означает, что Database Engine не смог использовать обратную связь для получения стабильного выделения памяти для хранимой процедуры и было решено отключить обратную связь для этого запроса. Для всех последующих выполнений хранимой процедуры (после 33-го запуска) обратная связь отключена.
Не всем запросам нужна обратная связь
Иногда SQL Server правильно определяет объем памяти с первого раза, поэтому обратная связь не используется. Давайте посмотрим на примере кода в листинге 6.
USE WideWorldImporters;
GO
SELECT * FROM Sales.Orders
WHERE OrderID < 3
ORDER BY OrderDate;Листинг 6. Пример с правильным выделением памяти
При запуске получаем следующий план выполнения.
Рисунок 9. План выполнения при запуске кода из листинга 6.
Свойство IsMemoryGrantFeedback показывает, что это первое выполнение. Кроме того, запрос запросил 1024 КБ памяти, что видно в RequestedMemory (рисунок 10).
Рисунок 10. MemoryGrantInfo для первого выполнения запроса
Информация о выделении памяти для второго выполнения показана на рисунке 11.
Рисунок 11. MemoryGrantInfo для второго выполнения
Для второго выполнения IsMemoryGrantFeedbackAdjusted имеет значение NoAccurateGrant. Это означает, что для второго выполнения не потребовалось большее количество памяти, поэтому обратная связь не используется. Это можно проверить, посмотрев на то, что RequestedMemory одинаковая на рисунках 10 и 11.
Отключение Memory Grant Feedback
Если по какой-либо причине вы хотите отключить обратную связь о грантах памяти в строковом режиме (Memory Grant Feedback – Row Mode), то для этого есть несколько способов. Первый — установить для базы данных уровень совместимости менее 150. С этим способом только одна проблема: также будут отключены все другие функции, которые появились в версии 15.x. Или, вы можете отключить обратную связь на уровне базы данных через опцию
ROW_MODE_MEMORY_GRANT_FEEDBACK.USE WideWorldImporters;
GO
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;
GOЛистинг 7. Отключение обратной связи по выделению памяти для базы данных
Здесь отключается обратная связь о грантах памяти на уровне базы данных. Теперь запросы для WideWorldImporters не будут использовать обратную связь в строковом режиме. Но это не единственный способ отключить обратную связь. Вы также можете отключить обратную связь для конкретного запроса, используя HINT, как показано в листинге 8.
USE WideWorldImporters;
GO
SELECT * FROM Sales.Orders
ORDER By OrderDate
OPTION
(USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));Листинг 8. Использование HINT для отключения обратной связи
В листинге 8 используется хинт
DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK, который отключает обратную связь о грантах памяти для одиночного запроса.Оптимизация вычисления необходимой памяти для запроса
Обратная связь по грантам памяти в пакетном режиме (batch mode) существует уже довольно давно. Но только в версии 15.x эта обратная связь стала доступна для запросов в строковом режиме (row mode). Все, что нужно для включения обратной связи для запросов в строковом режиме — это установить уровень совместимости базы данных в 150. Это, конечно, заработает, если вы используете текущую версию Azure SQL Database или SQL Server 2019. Когда включен функционал обратной связи о грантах памяти в строковом режиме, то SQL Server использует информацию о выделении памяти при предыдущих выполнениях кэшированного запроса, чтобы скорректировать выделение памяти для текущего выполнения. Корректировка необходимой памяти с помощью обратной связи при каждом выполнении запроса рано или поздно автоматически приведет к вычислению правильного объема памяти.
Узнать подробнее о курсе.
