Добрый день!

Предлагаю рассмотреть план выполнения следующего простого запроса:

select
code,
model,
avg(price) over (partition by model) avgp
from
pc

(В данной статье используется таблица из базы данных «Компьютеры» обучающего ресурса sql-ex.ru, ссылка на скрипт)

Запрос выводит все строки из таблицы PC (поля code и model) с расчетом средней стоимости по модели:

Пример разбора аналогичного плана запроса я встретил в книге Гранта Фритчи (Grant Fritchey) «SQL Server Execution Plans, Third Edition». Однако мне показалось, что анализ плана в книге изложен довольно кратко, поэтому для более детального изучения пришлось обратиться к справочнику операторов планов выполнения (Execution Plan Operator Reference, https://sqlserverfast.com/epr) Хьюго Корнелиса (Hugo Kornelis), технического редактора этой книги.

Удивительно, но для такого простого запроса сервер построил довольно сложный план выполнения, который выглядит так:

Последовательность вызова операторов в плане запроса:

SELECT -> NodeId 0:

-> NodeId 1-> NodeId 2 -> NodeId 3 -> NodeId 4 (внешний цикл NodeId 0)

-> NodeId 5: (внутренний цикл NodeId 0)

       NodeId 6 -> NodeId 7 > NodeId 8 (внешний цикл NodeId 5)

       NodeId 9 (внутренний цикл NodeId 5)

Поток данных в запросе начинается с оператора Clustered Index Scan (NodeId 4), который в данном случае не требует детального рассмотрения. Далее следует оператор Sort (NodeId 3), выполняющий сортировку по полю «Model» для подготовки данных к обработке оператором Segment (NodeID 2):

Sort является блокирующим оператором - он буферизует и сортирует все входные данные перед выдачей результата. Segment (NodId 2), реализующий конструкцию PARTITION BY запроса, добавляет в набор данных столбец «Segment1003» - флаг, отмечающий изменение значения поля «Model» в отсортированных данных и, соответственно, начало нового сегмента.

Подготовленные данные затем обрабатываются оператором Table Spool (NodeId 1), который создает их копию во временной рабочей таблице. Последующие обращения к этой таблице не требуют повторного выполнения дочерних операторов и могут осуществляться многократно из разных частей плана выполнения.

Различают две роли Table Spool:

• «Построитель» (создает и наполняет рабочую таблицу);

• «Читатель» (использует сохраненные данные).

Роль определяется свойством «Primary Node Id»:

• Отсутствует у «построителя»;

• Содержит NodeID «построителя» у «читателя».

Оператор Table Spool может работать в двух режимах:

1. Eager Spool (блокирующий): Полностью сохраняет все данные перед передачей;

2. Lazy Spool (неблокирующий): Передает данные родительскому оператору параллельно с сохранением в рабочую таблицу.

В данном случае Table Spool (NodeId 1) является «построителем», о чем свидетельствует отсутствие свойства «Primary Node Id». Значение свойства «Logical Operation» - «Lazy Spool», что указывает на неблокирующий режим работы:

Оператор Table Spool способен определять характер получаемых данных - сегментированные (как в нашем случае) или нет. Для сегментированных данных активируется специальный режим работы:

• В рабочей таблице хранится только текущий сегмент;

• Родительскому оператору передается одна (вероятно произвольная) строка сегмента;

• При повторном запросе таблица очищается и заполняется следующим сегментом.

Этот режим представляет собой гибрид между Eager и Lazy Spool:

• В отличие от Lazy: данные не передаются немедленно;

• В отличие от Eager: не материализуется весь набор данных.

Данные таблицы PC показывают, что первый сегмент содержит 3 строки модели 1121. Table Spool сохраняет этот сегмент до следующего запроса от оператора Nested Loops (NodeId 0). Nested Loops получает одну строку сегмента для внешнего цикла, это важно, так как у оператора отсутствуют свойства 'Predicate' и 'Outer Reference':

Отсутствие условий соединения (Predicate/Outer Reference) формально приводит к декартову произведению, но поскольку во внешнем цикле всегда обрабатывается одна строка текущего сегмента, это не нарушает логику запроса.

Согласно схеме обработки запроса, следующие данные запрашиваются у оператора Table Spool (NodeId 8). Наличие свойства «Primary Node Id» со значением 1 идентифицирует этот оператор как «читателя», работающего с результатами оператора NodeId 1.

Table Spool передает текущий сегмент оператору Stream Aggregate (NodeId 7), который:

• Подсчитывает количество строк;

• Вычисляет сумму по полю Price;

• Работает в скалярном режиме (отсутствие свойства GROUP BY) и выдает одну строку родительскому оператору.

Результирующая строка поступает в Compute Scalar (NodeId 6) для:

• Вычисления среднего (деление суммы на количество);

• Неявного преобразования типов;

• Проверки на пустой набор данных.

CASE WHEN [Expr1004]=(0) THEN NULL ELSE [Expr1005]/CONVERT_IMPLICIT(money,[Expr1004],0) END

Таким образом, во внешний цикл оператора Nested Loops (NodeId 5) поступает одна строка со средним значением цены текущего сегмента. Хотя оператор имеет свойство «Predicate», его значение «(1)» (всегда «Истина») означает фактическое отсутствие фильтрации: предикат существует формально, без реальных условий отбора.

Фактически, NodeId 5 работает аналогично NodeId 0 - оба оператора реализуют безусловное соединение. Разница в формальном определении свойств:

• NodeId 0: Полное отсутствие Predicate/Outer References

• NodeId 5: Явный предикат '(1)' (константа True)

Хотя логика Microsoft в выборе разных подходов для идентичных операций неочевидна, на практическое выполнение запроса это не влияет: в обоих случаях происходит полное сопоставление строк внутреннего цикла с единственной строкой внешнего.

Во внутреннем цикле присутствует единственный оператор - Table Spool (NodeID 9), работающий в режиме «читателя». Он повторно использует тот же сегмент данных, который хранится в рабочей таблице Table Spool (NodeId 1).

Оператор Nested Loops (NodeId 5) выполняет сопоставление средней цены со всеми строками текущего сегмента и передачу результатов в Nested Loops (NodeId 0). Оператор Nested Loops (NodeId 0) выполняет соединение с единственной строкой внешнего цикла (где важен только факт наличия строки) и отправку данных клиенту.

Этот процесс повторяется, пока во внешнем цикле NodeId 0 не закончатся данные.

Загадка оператора Table Spool (NodeId 1).

Можно заметить, что рассматриваемом наборе данных 4 разных модели (1121, 1232, 1233 и 1260) и, соответственно, 4 сегмента данных, однако «Actual Number Of Rows For All Executions» оператора Table Spool показывает 5 обработанных строк, см. рисунок "Свойства оператора Table Spool (NodeId 1)".

Даже на пустом наборе данных (например, с условием «WHERE model = 1») метрика «Actual Number Of Rows For All Executions» показывает 1. То есть:

• Значение всегда на 1 больше реального количества сегментов;

• Nested Loops (NodeId 5) выполняется на одну итерацию больше ожидаемого.

Хотя точная причина неясна (возможно, техническая особенность или баг), запрос работает корректно - дополнительные сегменты отсутствуют.

Сравнение производительности (бонус)

Сравнение производительности выполнялось для оригинального запроса (запрос 1) и запроса без использования оконной функции (запрос 2):

select
pc.code, pc.model, a.avgp
from
pc join
(
select
model,
avg(price) avgp
from
pc
group by model
) a on pc.model = a.model

Дополнительно были протестированы варианты с CROSS APPLY и коррелированным подзапросом, во всех случаях получены планы выполнения, идентичные запросу 2.

Тестовые наборы данных:

1. Набор 1: 100К строк, 7К уникальных моделей

2. Набор 2: 1М строк, 50К уникальных моделей

Версия сервера:
Microsoft SQL Server 2022 (RTM-CU16) (KB5048033) - 16.0.4165.4 (X64).

Результаты:

На наборе 1 как для запроса 1, так и для запроса 2 сервер генерировал планы исполнения в Row Execution Mode.

Расчетная стоимость запроса 1 составила 10.0362, что превышает порог стоимости для параллелизма по умолчанию (5) поэтому сервер генерирует многопоточный план:

Статистика по времени (средняя из 3 запусков):

CPU time = 417 ms,  elapsed time = 267 ms.

Сравним статистику с однопоточным планом (maxdop 1):

CPU time = 281 ms,  elapsed time = 314 ms.

Видно, что в данном случае использование параллелизма не дает значительного преимущества во времени исполнения запроса.

Стоимость запроса 2 на наборе данных 1 составляет 2.87194 что не превышает пороговое значение для параллелизма, запрос выполняется в одном потоке и имеет следующий план выполнения:

CPU time = 83 ms,  elapsed time = 269 ms.

Видно, что план проще, время использования процессора значительно меньше при сопоставимом общем времени исполнения.

Что происходит на наборе данных 2?

Для запроса 1 расчетная стоимость составила 51.8815, сервер предложил многопоточный план и уже с использованием ожидаемого для оконных функций оператора Window Aggregate (поддерживает работу только в Batch Execution Mode):

CPU time = 2093 ms,  elapsed time = 7388 ms.

Для однопоточного плана:

CPU time = 1391 ms,  elapsed time = 8303 ms.

Для запроса 2 сервер сгенерировал уже многопоточный план (тоже c Batch Execution Mode):

CPU time = 1165 ms,  elapsed time = 7369 ms.

Статистика для однопоточного плана:

CPU time = 593 ms,  elapsed time = 7427 ms.

Пока все складывается не в пользу оконных функций : )

Если добавить индекс?

create nonclustered index ix_pc_model_price on pc(model) include (price)

Запрос 1 получает однопоточный план с использованием индекса:

CPU time = 296 ms,  elapsed time = 2554 ms.

Запрос 2 также получает однопоточный план с использованием индекса:

CPU time = 610 ms,  elapsed time = 2878 ms.

Дело в том что обнаружив подходящий индекс, оптимизатор выбирает алгоритм Merge Join (тогда как без индекса используется Hash Join) — действительно оптимальный метод для соединения предварительно отсортированных данных. Однако этот выбор имеет важное ограничение: Merge Join не поддерживает Batch Execution Mode. В результате на больших объемах данных запрос с оконной функцией получает двойное преимущество — как от использования индекса, так и от пакетной обработки, тогда как вариант с GROUP BY лишен второго преимущества, что существенно снижает его эффективность.

Что в итоге?

Даже для кажущегося простым запросом с AVG() OVER (PARTITION BY) SQL Server может построить сложный и неочевидный план выполнения. Знакомство с этим паттерном поможет анализировать более сложные планы. Тесты производительности показывают: оптимальный вариант запроса нельзя выбрать «на глаз» — только через проверку на реальных данных и анализ работы СУБД . Надеюсь, этот разбор оказался для вас и интересным, и практически полезным.