Автор: Kendra Little, Adaptive Joins and Memory Grants in SQL Server
Адаптивные соединения (adaptive joins) позволяют оптимизатору выбирать между хэш-соединением (Hash Join) и соединением вложенными циклами (Nested Loop join) во время выполнения, что может быть фантастически полезно для производительности, когда оценки количества строк могут варьироваться. Недавно, когда Эрик Дарлинг (Erik Darling) преподавал двухдневный курс по T-SQL на PASS Community Data Summit, один из студентов спросил, почему план запроса, в котором адаптивное соединение во время выполнения использовало вложенные циклы, всё равно получило большое распределение памяти (memory grant).
Я не помнила ответа на этот вопрос, но замечательная вещь в совместном преподавании в том, что Эрик его знал: адаптивные соединения всегда начинают выполняться как хэш-соединения, а это означает, что они должны получить распределение памяти заранее. Даже если в итоге запрос переключается на вложенные циклы во время выполнения, это распределение памяти уже было выделено. Это имеет реальные последствия для использования памяти, особенно в средах с высокой конкурентностью (high-concurrency environments).
Что такое адаптивные соединения?
Адаптивные соединения были представлены в SQL Server 2017. Это соединение позволяет оптимизатору запросов выбирать между хэш-соединением в пакетном режиме (batch mode Hash Join) и соединением вложенными циклами в построчном режиме (row mode Nested Loops Join) во время выполнения, основываясь на фактическом количестве строк, обработанных на этапе построения хэш-соединения (build phase).
Это отход от традиционной оптимизации запросов, где оптимизатор принимает однократное решение во время компиляции плана. С адаптивными соединениями оптимизатор может «передумать» во время выполнения, если количество строк, с которыми он сталкивается, указывает на то, что хэш-соединение не нужно, и он может сделать что-то более лёгкое для этого выполнения запроса.
Я собираюсь придерживаться темы этой статье, но в конце статьи есть несколько отличных глубоких разборов того, как работают адаптивные соединения.
Понимание фаз построения и проверки хэш-соединения
Давайте поговорим о том, как работают хэш-соединения. Хэш-соединения имеют две фазы: построение (build) и проверка (probe).
Представьте, что Эрик и я проводим учебное мероприятие. Мы нанимаем человека стоять у двери и отмечать входящих. Он точно знает, кто должен быть здесь, и он гораздо суровее, чем выглядит.
- Фаза построения (Build phase): До начала мероприятия мы даём человеку у двери список всех зарегистрированных участников. Информация находится в Excel-таблице (самая популярная база данных в мире). В терминах SQL Server, мы построили хэш-таблицу всех людей, которым разрешён вход.
- Фаза проверки (Probe phase): Когда люди приходят, человек у двери проверяет имя каждого по списку — он выполняет проверку по списку, чтобы увидеть, есть ли у каждого человека совпадающее значение в хэш-таблице. Если ваше имя есть в списке — он вас пропускает. Если нет — вы отправляетесь восвояси.
В хэш-соединении SQL Server делает то же самое:
- Фаза построения: Берёт один входящий набор данных (обычно меньший) и строит хэш-таблицу в памяти, организуя строки по ключу соединения.
- Фаза проверки: Берёт другой набор данных, с которым выполняется соединение, и для каждой строки ищет ключ соединения в хэш-таблице, чтобы найти соответствующие строки.
Фаза построения происходит первой и требует памяти для хранения хэш-таблицы. Фаза проверки происходит второй и использует эту хэш-таблицу для быстрого поиска совпадений / проверки, кто «в списке».
Все адаптивные соединения рождаются как хэш-соединения
Ключевые моменты, которые следует запомнить:
- Хэш-соединения требуют предварительного распределения памяти — оптимизатор должен выделить память для хэш-таблицы до начала выполнения.
- Операции применения в построчном режиме (row mode apply operations) не требуют распределения памяти — они могут работать с минимальным объёмом памяти, обрабатывая строки одну за другой.
- Точка принятия решения находится после завершения построения — после завершения построения хэш-соединения SQL Server оценивает, продолжать ли хэш-соединение в пакетном режиме или переключиться на применение в построчном режиме.
Как подчёркивает Пол Уайт (Paul White) в своей статье о пороге адаптивного соединения:
«Одна вещь, которую я хочу, чтобы вы имели в виду на протяжении этой статьи: адаптивное соединение всегда начинает выполняться как хэш-соединение в пакетном режиме. Это верно, даже если план выполнения указывает, что адаптивное соединение переключится на применение (apply)».
Даже если адаптивное соединение в конечном итоге переключается на стратегию применения в построчном режиме (которая более эффективна для малого количества строк), оно всё равно должно было начать как хэш-соединение и запросить распределение памяти заранее. Расчёт порога (threshold calculation) основан на оценках стоимости и фактическом количестве строк из фазы построения.
Последствия для распределения памяти
Именно здесь адаптивные соединения становятся интересными с точки зрения управления ресурсами.
Каждое выполнение запроса с адаптивным соединением должно запросить распределение памяти для хэш-соединения, даже если в итоге запрос переключается на применение в построчном режиме во время выполнения. Это прямо следует из утверждения Пола Уайта о том, что адаптивные соединения всегда начинают выполняться как хэш-соединения в пакетном режиме — распределение памяти запрашивается до того, как SQL Server узнает, нужно ли будет переключать стратегию.
Это означает:
- Распределения памяти запрашиваются при каждом выполнении — даже если адаптивное соединение переключается на применение в построчном режиме, распределение памяти уже было выделено для хэш-соединения.
- Общее использование памяти может увеличиться — если у вас много запросов, использующих адаптивные соединения, ваше общее использование распределений памяти будет выше, чем если бы эти запросы использовали непосредственно применение в построчном режиме.
- Фактор нагрузки на память (memory pressure considerations) — в средах с ограниченной памятью или высокой конкурентностью адаптивные соединения могут способствовать возникновению нагрузки на память.
Когда придётся оптимизировать с помощью временных таблиц и динамического SQL
Поскольку адаптивные соединения всегда запрашивают распределения памяти, иногда всё же стоит оптимизировать запросы, используя временные таблицы и динамический SQL, чтобы:
- Упростить оптимизацию — разбить сложные запросы на более простые части, с которыми оптимизатор может лучше справиться.
- Уменьшить общие распределения памяти — меньшие, более простые запросы могут не нуждаться в адаптивных соединениях и могут использовать более эффективные стратегии соединения, не требующие распределения памяти.
- Улучшить стабильность плана — временные таблицы со статистикой могут привести к более предсказуемым планам.
Это не означает, что вам следует полностью избегать адаптивных соединений — это мощная функция, которая часто улучшает производительность. Но понимание последствий для распределения памяти помогает вам принимать обоснованные решения о стратегиях оптимизации запросов.
Пример запроса с адаптивным соединением
Вот пример запроса с использованием базы данных StackOverflow2013, который получает адаптивное соединение на моём ноутбуке с SQL Server 2025.
USE StackOverflow2013;
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;
GO
EXECUTE dbo.DropIndexes;
GO
CREATE NONCLUSTERED INDEX
ix_Posts_CreationDate_Score
ON dbo.Posts
(
CreationDate,
Score
)
INCLUDE
(
OwnerUserId,
PostTypeId,
CommentCount
)
WHERE PostTypeId IN (1, 2)
WITH
(
DATA_COMPRESSION = PAGE,
SORT_IN_TEMPDB = ON
);
GO
CREATE OR ALTER PROCEDURE
dbo.Adaptive
(
@Start datetime,
@End datetime
)
AS
BEGIN
SELECT
u.DisplayName,
u.Reputation,
p.Score,
p.CreationDate
FROM dbo.Posts AS p
JOIN dbo.Users AS u
ON u.Id = p.OwnerUserId
WHERE p.PostTypeId IN (1, 2)
AND p.CreationDate >= @Start
AND p.CreationDate < @End
END;
GO
/* Этот запрос генерирует план с адаптивным соединением */
EXECUTE dbo.Adaptive '20130101', '20130514';
GO
/* Этот запрос переиспользует план и выбирает стратегию вложенных циклов
для адаптивного соединения */
EXECUTE dbo.Adaptive '20130101', '20130102';
GO
Фактический план выполнения запроса при втором выполнении выглядит так:
Информация о распределении памяти недоступна в деталях для каждого оператора, но вы можете видеть, сколько было выделено для запроса — в данном случае на операторе SELECT.
Детали о том, сколько из распределения памяти было использовано и что участвовало в «согласовании» запроса, видны в панели свойств оператора или в XML плана.
Полезные свойства оператора адаптивного соединения в плане запроса
Когда вы смотрите на свойства оператора адаптивного соединения (adaptive join operator), вы увидите:
- Actual Join Type — выбрало ли оно вложенные циклы или хэш-соединение
- Adaptive Threshold Rows — количество строк, при котором происходит переключение между вложенными циклами и хэш-соединением
- Hash Keys Build — столбец(цы) из входных данных построения, которые хэшируются в корзины для выполнения соединения
- Hash Keys Probe — столбец(цы) из проверки или вложенных циклов (в зависимости от выбранного), которые используются для выполнения соединения
Ограничения адаптивных соединений
Адаптивные соединения имеют некоторые ограничения:
- Требования к редакции: Адаптивные соединения в пакетном режиме требуют Enterprise Edition (или Developer/Evaluation Edition, которые включают функции Enterprise). Они недоступны в Standard Edition, Web Edition или Express Edition.
- Требования к уровню совместимости: Требуется уровень совместимости базы данных 140 (SQL Server 2017) или выше.
- Только пакетный режим: Адаптивные соединения работают только с пакетным режимом выполнения (batch mode execution), который требует либо:
- Индекса columnstore, или
- Пакетного режима для хранилища строк (Batch Mode on Row Store) (SQL Server 2019+, только Enterprise Edition, уровень совместимости 150 или выше).
- Ограниченные переходы соединений: Адаптивные соединения поддерживают только переходы от хэш-соединений в пакетном режиме к применению в построчном режиме (коррелированные вложенные циклы). Они не поддерживают переходы к другим типам соединений, таким как соединения слиянием (merge joins) или хэш-соединения в построчном режиме.
- Чувствительность вычисления порога: Порог адаптивного соединения зависит от первоначальной оценки кардинальности (cardinality estimate) входных данных построения. Как объясняет Пол Уайт, это может привести к проблемам чувствительности к параметрам (parameter sensitivity issues), когда один и тот же кэшированный план ведёт себя по-разному в зависимости от первоначальной оценки кардинальности, использованной во время оптимизации.
- Требования к структуре запроса: Запрос должен соответствовать требованиям для пакетного режима выполнения, и соединение должно быть подходящим для оптимизации адаптивным соединением. Соединения, которые потребовали бы поиска по ключу (Key Lookup) на внутренней стороне, не подходят. Запросы с определёнными конструкциями, такими как
CROSS APPLYсTOPилиOUTER APPLY, могут не подходить. Как отмечает Эрик Дарлинг в своём подробном ответе, существует расширенное событие (Extended Event)adaptive_join_skipped, которое может помочь отследить, когда и почему адаптивные соединения пропускаются, если вы любите «ракетную науку». - Не поддерживается в собственно скомпилированных модулях: Собственно скомпилированные модули T-SQL (natively compiled modules, In-Memory OLTP) не поддерживают адаптивные соединения.
- Накладные расходы на память: Как мы уже обсуждали, адаптивные соединения всегда запрашивают распределения памяти для хэш-соединения, даже если они переключаются на применение в построчном режиме во время выполнения.
Включение и отключение адаптивных соединений
Адаптивные соединения включены по умолчанию, когда требования выполнены, но вы можете управлять ими на уровне базы данных или запроса.
На уровне базы данных (влияет на все запросы в базе данных):
- SQL Server 2017:
- Отключить:
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON;(да,ONозначает отключить — это двойное отрицание) - Включить заново:
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = OFF;
- Отключить:
- SQL Server 2019+:
- Отключить:
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = OFF; - Включить заново:
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = ON;
- Отключить:
Подсказка запроса (влияет только на конкретный запрос):
OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS'));
Подсказка запроса имеет приоритет над настройкой уровня базы данных.
Узнать больше
Адаптивные соединения — это мощная функция, которая может улучшить производительность запросов, выбирая правильную стратегию соединения во время выполнения.
Однако они не бесплатны: они связаны с последствиями для распределения памяти, которые важно учитывать, особенно в средах с высокой конкурентностью или системах с ограниченной памятью.
Тот факт, что адаптивные соединения всегда начинаются как хэш-соединения в пакетном режиме, означает, что они всегда запрашивают распределения памяти, даже если в итоге переключаются на применение в построчном режиме. Эти соединения могут быть фантастическими, но вам, вероятно, не нужно, чтобы в высококонкурентной системе они работали всё время.
- Прочитайте глубокое погружение Пола Уайта о пороге адаптивного соединения
- Посмотрите видео Эрика Дарлинга об адаптивных соединениях в SQL Server
- Ознакомьтесь со страницей Хьюго Корнелиса (Hugo Kornelis), объясняющей оператор адаптивного соединения
Комментариев нет:
Отправить комментарий