Использование APPLY в T-SQL

Работа с базами данных в .NET Framework --- Оконные функции T-SQL --- Использование APPLY

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

Если для целей тестирования вы хотите имитировать среду с разным числом процессоров, есть несколько способов решения этой задачи. Один из вариантов — воспользоваться параметром запуска -Pn, где n — число планировщиков которые надо запустить при старте SQL Server. Допустим, у вас на машине четыре логических процессора и вы запустили службу SQL Server с параметром -P8. SQL Server запустится с восемью планировщиками, а оптимизатор будет делать планы на основе этого числа, как если бы он работал в среде с восемью логическими процессорами. Уровень параллелизма в параллельных планах обычно будет равен восьми.

О втором способе я узнал у Эладио Ринкона. Можно использовать недокументированную команду DBCC, которая называется DBCC OPTIMIZER_WHATIF. В качестве первого аргумента я указал 1, а в качестве второго задал число процессоров, которое оптимизатор будет использовать при создании планов. Например, команда DBCC OPTIMIZER_WHATIF(1,8) заставляет оптимизатор при создании плана считать, что в системе восемь процессов. Имейте в виду, что эта команда не меняет число планировщиков, запускаемых вместе с SQL Server, поэтому уровень параллелизма не поменяется и останется равным реальному числу планировщиков. Но оптимизатор будет создавать план так, как будто в на машине восемь процессоров. Может потребоваться также добавить OPTION(RECOMPILE), чтобы заставить SQL Server после выполнения этой команды создать новый план.

Допустим, для какого-то запроса SQL Server обычно генерирует последовательный план при наличии четырех процессоров и параллельный — при наличии восьми процессоров. На данный момент на вашей машине четыре процессора. При использовании параметра запуска -P8 сервер SQL Server создаст параллельный план с уровнем параллелизма 8. Если задать команду DBCC OPTIMIZER_WHATIF(1,8), SQL Server сгенерирует параллельный план с уровнем параллелизма 4. Параметр запуска оказывает влияние на весь экземпляр, а команда DBCC — только на текущий сеанс. В любом случае помните, что эти параметры не задокументированы официально и могут использоваться только для тестовых целей.

Вернемся к параллельному методу с использованием APPLY; он обычно полезен, если используется предложение секционирования окна, а встроенный параллелизм не дает оптимального результата или попросту не используется. Хороший пример, когда встроенные параллельные вычисления оконной функции не всегда оптимальны, тот, в котором используются итераторы Sort. В качестве примера посмотрите на такой запрос:


SELECT actid, tranid, val,
  ROW_NUMBER() OVER(PARTITION BY actid ORDER BY val) AS rownumasc,
  ROW_NUMBER() OVER(PARTITION BY actid ORDER BY val DESC) AS rownumdesc
FROM dbo.Transactions;

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

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

В параллельных планах выполнения оконных функций приходится секционировать строки по тем же элементам, что и элементы секционирования окна, если итераторы Segment и Sequence Project находятся в параллельной зоне. Если посмотреть на свойства итератора Parallelism (Redistribute Streams), мы увидим, что в нем используется секционирование по хешу и строки разбиваются по actid. Этот итератор перераспределяет строки исходных потоков, используемых для параллельного чтения данных для целевых потоков, которые собственно вычисляют результат первой оконной функции. Затем строки сортируются в соответствии с определением упорядочения во второй оконной функции. Итератор Parallelism (Gather Streams) отвечает за сбор потоков. Наконец, вычисляется результат второй оконной функции.

В таком плане есть несколько узких мест:

Повторное секционирование потоков: Перенос данных между потоками является дорогой операцией. В данном случае было бы даже лучше, если бы ядро СУБД использовала последовательный просмотр и только потом напрямую распределяла бы потоки.
Сортировка: Сейчас количество строк, обрабатываемых потоком определяется уровнем параллелизма. Например, в запросе с уровнем параллелизма 8 каждый поток обрабатывает примерно 250 тыс. строк. С другой стороны, если поток будет обрабатывать только строки, относящиеся к одному счету, то речь будет идти о 20 тыс. строк на одну сортировку. (Как вы помните, у нас 100 счетов, на каждом из которых 20 тыс. транзакций.) Это делает существующие сортировки примерно на 20% менее эффективными, чем они могли бы быть: (((20000 * log(20000)) * 100) / ((250000 * log(250000)) * 8)).
Вторые итераторы Segment и Sequence Project: Они находятся в последовательной зоне. Хотя это не самые дорогие итераторы, они не бесплатны, а закон Амдала никто не отменял. (Этот закон говорит, что общая производительность параллельного алгоритма ограничивается последовательными участками.)

От всех этих узких мест избавляет использование параллельного метода с использованием APPLY, который реализуется следующим образом:

Запрос таблицы, в которой хранятся конкретные значения секционирования (в данном случае это Accounts).
Использование оператора APPLY для применения к каждому из оставшихся строк логики исходного запроса (в нашем случае по отношению к Transactions) с фильтрацией по уникальным значениям, по которым выполняется секционирование.

В качестве примера перепишем предыдущий запрос:

SELECT C.actid, A.*
FROM dbo.Accounts AS C
  CROSS APPLY (SELECT tranid, val,
                 ROW_NUMBER() OVER(ORDER BY val) AS rownumasc,
                 ROW_NUMBER() OVER(ORDER BY val DESC) AS rownumdesc
               FROM dbo.Transactions AS T
               WHERE T.actid = C.actid) AS A;

Заметьте, что из-за того, что производная таблица A содержит строки только одной секции, в определении окна отсутствует предложение секционирования.

На моей машине этот запрос выполняется три секунды — менее чем за половину времени выполнения предыдущего запроса. План нового запроса показан на рисунке:

План начинается с чтения кластеризованного индекса таблицы Accounts. Затем итератор обмена Parallelism (Distribute Streams) используются для распределения строки на несколько потоков, используя для секционирования механизм циклического обслуживания (следующий пакет в следующий поток). Поэтому каждому потоку в нижней части итератора соединения Nested Loops приходится работать только на подмножестве строк одной секции, но без описанных ранее узких мест. При этом приходится жертвовать числом операций поиска в индексе (и соответствующих операций логического чтения), которые необходимы для выполнения запроса. Если плотность столбцов секционирования очень низка (например, 200 тыс. секций по 10 строк в каждой), возникает большое число операций поиска и эффективность использования APPLY больше недостаточна.

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