Сценарий: после первого выполнения запроса сортировка получила недостаточно памяти и обратилась к диску, но при следующих запусках объём предоставленной памяти изменился без изменения SQL. Какой механизм это объясняет?
Это может объясняться обратной связью по предоставлению памяти (memory grant feedback). Система анализирует фактическое потребление памяти после выполнения и корректирует размер памяти для последующих запусков, чтобы уменьшить риск повторного обращения к временному хранилищу или избыточного резервирования.
Механизм обычно корректирует именно объём memory grant, а не логическую структуру запроса. Поэтому сам план может выглядеть почти так же, хотя фактическое выполнение станет быстрее или снизится давление на память.
Оптимизатор заранее оценивает, сколько памяти понадобится операторам сортировки, хеш-агрегации и hash join. Эта оценка нужна, чтобы оператор выполнился в памяти, но ошибка кардинальности или неудачная оценка ширины строк может привести к недостаточному либо чрезмерному резервированию.
Недостаточный grant вызывает spill: часть промежуточных данных записывается во временное хранилище. Чрезмерный grant не обязательно замедляет один запрос, но уменьшает объём памяти, доступный другим запросам. Обратная связь появилась как способ исправлять такие ошибки по результатам уже выполненного запроса, не полагаясь только на модель оптимизатора.
Если оператору выдали слишком мало памяти, сортировка или хеш-операция разбивается на этапы с обращением к диску или другому временному хранилищу. Это увеличивает задержку и создаёт дополнительный ввод-вывод.
Обратная ситуация также опасна: большой запрос может получить существенно больше памяти, чем ему нужно. При параллельной нагрузке несколько таких запросов способны вызвать ожидания памяти, вытеснение полезных данных из кэша или общее падение пропускной способности.
Важно отличать эту проблему от устаревшей статистики, плохой оценки числа строк и параметрической чувствительности. Они могут быть исходной причиной неверного grant, но сама обратная связь — механизм адаптации после наблюдения фактического выполнения.
При выполнении система сравнивает выделенный объём памяти с фактической потребностью оператора. Если произошёл spill, следующий запуск может получить больший grant. Если памяти было выделено значительно больше необходимого, последующие запуски могут получить меньший grant.
В SQL Server memory grant feedback первоначально применялся для некоторых пакетных планов, а затем поддержка расширялась, включая сценарии построчного выполнения. Конкретное поведение зависит от версии, режима выполнения, типа оператора и настроек базы данных; это не универсальное свойство любого SQL-движка.
Обычно механизм меняет параметры выполнения уже выбранного плана, но не устраняет причину ошибочной оценки кардинальности. Если план неверно оценивает количество строк из-за статистики, корреляции предикатов или параметров, нужно отдельно проверять план, статистику и распределение данных.
У обратной связи есть ограничения. Для запроса могут отсутствовать подходящие условия применения, изменение может не успеть проявиться при однократном выполнении, а частое чередование маленьких и больших наборов данных способно приводить к колебаниям корректируемого grant. Очистка кэша плана, перекомпиляция или изменение формы запроса также могут сбросить накопленный эффект.
Проверять результат следует по нескольким выполнениям: изменились ли фактический grant и его использование, исчезли ли предупреждения о spill, сократился ли временный ввод-вывод и не выросли ли ожидания памяти у других запросов. Нельзя считать сам факт изменения grant доказательством ускорения: меньший grant может помочь конкуренции за память, но привести к новому spill.
Аналитический запрос периодически обрабатывает либо несколько тысяч, либо десятки миллионов строк. При первом большом запуске хеш-агрегация получила слишком мало памяти и записала промежуточные данные во временное хранилище. После включения подходящего режима memory grant feedback последующие большие запуски стали получать больший grant, а число spill уменьшилось.
Рассматривались три варианта. Обновление статистики могло улучшить исходную оценку, но не гарантировало точного результата при резко меняющемся размере выборки. Принудительное увеличение памяти на уровне запроса могло убрать spill, однако повышало риск дефицита памяти при параллельных запусках. Переписывание запроса или добавление индекса могло уменьшить объём обрабатываемых данных, но требовало проверки стоимости поддержки индекса и влияния на другие запросы.
Выбрали сочетание: исправили устаревшую статистику, оставили обратную связь включённой как адаптивный механизм и проверили нагрузку по ожиданиям памяти. Это уменьшило число spill без безусловного резервирования большого объёма памяти для каждого выполнения. При этом для сильно параметризованных сценариев потребовался отдельный анализ чувствительности плана.
1. Вопрос: Меняет ли обратная связь по памяти сам алгоритм соединения или сортировки?
Ответ: Обычно нет. Она прежде всего корректирует объём памяти, выделяемый уже выбранному плану. Если оптимизатор выбрал hash join вместо nested loop из-за неверной оценки кардинальности, увеличение grant может уменьшить spill, но не сделает автоматически сам алгоритм соединения оптимальным. Для смены плана нужны условия повторной оптимизации, изменение статистики, параметров или текста запроса.
2. Вопрос: Может ли устранение spill ухудшить общую производительность системы?
Ответ: Может. Увеличенный grant способен ускорить конкретный запрос, но одновременно занять память, необходимую другим запросам. При высокой конкуренции суммарная пропускная способность иногда падает, даже если один запрос перестал обращаться к временному хранилищу. Поэтому оценивают не только длительность запроса, но и ожидания памяти, параллелизм, количество одновременных выполнений и общий объём предоставленной памяти.
3. Вопрос: Почему обратная связь не всегда стабилизирует запрос с параметрами, дающими разные объёмы данных?
Ответ: Один кэшируемый план может последовательно использоваться для очень разных наборов строк. Коррекция, подходящая для большого набора, избыточна для малого, а коррекция для малого приводит к spill на большом. В результате механизм может менять grant между запусками или достигать защитных ограничений. При устойчивой параметрической чувствительности нужно дополнительно рассматривать разделение планов, перекомпиляцию, корректировку статистики или изменение формы запроса, оценивая стоимость каждого решения.