SQL-Ex blog

Установка

Тестовые данные очень просты. Вставьте десять миллионов последовательных целых чисел в единственный столбец таблицы с кластеризованным индексом:

CREATE TABLE dbo.ParallelTest (

Id BIGINT NOT NULL,

CONSTRAINT PK_ParallelTest PRIMARY KEY (Id)

);

INSERT INTO dbo.ParallelTest (Id)

SELECT [value]

FROM GENERATE_SERIES(1, 10000000);

Теперь рассмотрим следующий запрос:

SELECT SUM(a.ID + b.ID) s

FROM dbo.ParallelTest a

INNER JOIN dbo.ParallelTest b ON a.ID = b.ID

OPTION (USE HINT ('DISALLOW_BATCH_MODE'));

Оптимизатор запросов естественно выбирает соединение слиянием для этого запроса (merge join). Самосоединяющиеся таблицы очевидно имеют одинаковое число строк и уже отсортированные данные. Это делает запрос идеальным кандидатом для последовательного соединения слиянием с точки зрения оптимизатора. USE HINT в запросе не особенно важен, этот пример работает и без него, но его наличие будет объяснено позже. У меня план этого запроса выглядит так:



Запрос занимает 2721 мс времени ЦП и времени выполнения на моей машине. Здесь понятно, почему время ЦП и прошедшее время совпадают, т.к. запросу не приходится ничего ожидать. Только один пользовательский запрос выполняется на экземпляре SQL. Затем я принудительно вызову план запроса с параллельным соединением слиянием, изменив хинт запроса на следующий:

SELECT SUM(a.ID + b.ID) s

FROM dbo.ParallelTest a

INNER JOIN dbo.ParallelTest b ON a.ID = b.ID

OPTION (MAXDOP 8, MERGE JOIN, USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE', 'DISALLOW_BATCH_MODE'));

Я выбираю MAXDOP 8, поскольку моя тестовая машина имеет 8 физических ядер ЦП. Это неестественный план запроса с более высокой стоимостью параллельного выполнения, чем стоимость последовательного выполнения. Оптимизатор запросов выбрал его только потому, что мы навязали его. Отметьте наличие операторов обмена с относительно высокой оценочной стоимостью:



Параллельный запрос занимает 6465 мс времени ЦП и 1723 мс времени на выполнение на моей машине. Он отработал на 33% быстрей, чем последовательный запрос, но время ЦП увеличилось более чем вдвое. Операторы обмена добавили большие накладные расходы для этого запроса. Затем я выполню тот же запрос снова, но теперь я сделаю один из своих процессоров более загруженным, чем раньше. Рассмотрите следующий бесконечный цикл, закодированный в хранимой процедуре:

CREATE OR ALTER PROCEDURE #p AS

SET NOCOUNT ON;

DECLARE @i INT;

WHILE 1 = 1

SET @i = 0;

GO

EXEC #p;

Этот код ничего не выполняет, за исключением интенсивного использования ресурсов ЦП, в точности то, что мне требовалось для этого теста. Я выполняю запрос слияния при MAXDOP 8 на машине с 8 ядрами ЦП, так что я почти наверняка увижу конкуренцию планировщика для некоторых рабочих процессов параллельного запроса. Они будут бороться за ресурсы ЦП с бесконечным циклом.

Попробуйте предсказать падение производительности, которое параллельный запрос испытает из-за планировщика загрузки. В 2 раза медленней? В три раза? Хуже? В качестве подсказки напомню, что операторы распределенного обмена хэшем обычно пытаются заставить каждый поток выполнять одинаковый объем работы. Также напомню, что параллельное соединение слиянием требует операторов обмена, сохраняющих порядок. Я включил 8 фотографий Эрика при MAXDOP 8, чтобы надежно предохранить вас от того, чтобы случайно не увидеть ответ прежде, чем вы закончите свои предсказания.

(я не буду их включать в перевод )

С планировщиком загрузки запрос теперь использует 6818 мс времени ЦП и 31745 мс времени на исполнение на моей машине. Один и тот же код более чем в 18 раз медленнее только лишь благодаря высокоинтенсивной хранимой процедуре, которая выполняется на том же экземпляре SQL. Вот статистика ожиданий запроса в интересующем нас случае:


<Wait WaitType=”CXPACKET” WaitTimeMs=”454673″ WaitCount=”5262″ />
<Wait WaitType=”CXSYNC_PORT” WaitTimeMs=”32539″ WaitCount=”41″ />
<Wait WaitType=”CXSYNC_CONSUMER” WaitTimeMs=”43″ WaitCount=”14″ />

scheduler_ring_buffer_recorded

Это неоправданно короткое введение в сложную логику, но рабочие потоки объединяются, совместно используя время в планировщиках. При нормальных условиях потоки будут выполнять код в планировщике до 4 миллисекунд перед выходом. Поток выходит в случае, когда имеется другой поток, ожидающий выполнения в планировщике. Если такого нет, исходный поток сразу снова начинает выполнение кода. Примером того, когда рабочий поток завершается раньше, является необходимость ожидания операции ввода-вывода.

scheduler_ring_buffer_recorded - это имя расширенного события отладки, которое вы, вероятно, никогда не должны использовать. Зачастую я не следую моим собственным советам, поэтому я использовал его, чтобы получить представление, насколько часто рабочие потоки уступали планировщику при запросах на соединение слиянием. Я отслеживал только типы активности “SCHEDULER_NONPREEMPTIVE_RESUME” и допускаю, что этот анализ довольно приблизителен. Для запроса с MAXDOP 1 имелось всего 705 событий за 2872 миллисекунды. Другими словами, среднее время, проведенное в планировщике перед выходом близко к программному максимуму в 4 мс. Этого следовало ожидать для последовательного запроса, если все данные находятся в буферном пуле. Тут нет выделения памяти запросу (причина для USE HINT) и действительно нет никаких причин для раннего выхода.

Параллельный запрос имеет значительно больше выходов. Глядя на один рабочий поток в качестве примера, видим 14862 события за 1714 миллисекунд. Другими словами, он примерно в 35 раз больше выходил из планировщика по сравнению с запросом с MAXDOP 1. Более высокое число выходов может ухудшить производительность при конкуренции с потоками других запросов на том же планировщике. Хранимая процедура с бесконечным циклом доминирует над потоками параллельного запроса и потребляет примерно 97% времени ЦП в планировщике. Это основная причина, почему параллельный запрос стал выполняться в 18 раз медленнее.

Заметим, что вы можете получить эту информацию, просматривая столбец yield_count динамического представления sys.dm_of_schedulers, но эти числа относятся к серверу, а не ограничиваются одним запросом. При этом я получил весьма схожие результаты при сравнении этого столбца с результатами расширенного события.

Подводя итог, можно сказать, что накладные расходы на параллельные запросы не так просты, как прямые накладные расходы операторов обмена. Переход запроса от последовательного к параллельному может привести к совершенно отличным взаимодействиям между рабочим потоками и планировщиком. Последовательный запрос выполнял свой код сразу за 4 миллисекунды. Параллельный запрос должен был выходить значительно более часто. Этот тип изменений нельзя непосредственно наблюдать в плане запроса.

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

Имеется много причин, почему параллельный запрос может оказаться не столь эффективен, как вы надеялись. Я считаю эти причины наиболее общими:

1. Имеются накладные расходы, связанные параллельными операторам обмена, которые отсутствуют в последовательных запросах. Перемещение данных между рабочими потоками требуют дополнительного времени ЦП, которое добавляется к общему времени выполнения запроса. Я полагаю, что это то, что описывается следующей цитатой из документации:
   

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



2. Имеется некий вид дополнительной координации между потоками, который откладывает выполнение запроса. Например, оператор перераспределения потоков, сохраняющий порядок, обычно хуже с точки зрения производительности по сравнению с тем, который не требует предварительного упорядочивания. Другим примером может служить моя публикация в блоге о медленном параллельном сканировании, вызванном облачным хранилищем.


3. Работа может быть неравномерно распределена между рабочими потоками. Например, eager index spool может быть построен только для одного потока. Другие потоки могут находиться в состоянии ожидания, пока этот временный индекс не будет построен. В другом примере параллельное сканирование таблицы с фильтрацией может не дать на выходе одинаковое число строк для всех потоков. При прочих равных параллельный запрос, который не может использовать все ЦП, разрешенные MAXDOP, не так эффективен, как тот, который может.


4. Работа может быть равномерно распределена между рабочими потокам, но некоторые ЦП оказываются более занятыми, чем другие. Некоторые параллельные запросы медленны как их самый занятый ЦП, даже когда запросы равномерно используют разделяемое время планировщика. Конечно, драматическое падение производительности можно наблюдать, когда параллельный запрос получает неравномерное количество времени в планировщике (как в демонстрационном примере этой статьи).

Смягчения

Производительность отдельного параллельного запроса может деградировать из-за конкуренции на единственном планировщике, который используется одним или большим числом его рабочих потоков. Наиболее вероятно, что вы столкнетесь с проблемами при более высоком DOP или при более высоком числе конкурирующих запросов. Понижение MAXDOP уменьшает шансы, что вы столкнетесь с наихудшим типом конкуренции, поскольку запрос использует меньшее число планировщиков. Уменьшение числа запросов уменьшает конкуренцию на планировщике, что тоже помогает.

Говоря иначе, простейший способ уменьшить проблемы с параллельными запросам - иметь меньше рабочих потоков на планировщик. Конечно, это вопрос балансировки, но это то, что вы должны выполнять для некоторых рабочих нагрузок. Установите подходящие значения Cost Threshold for Parallelism (стоимостный порог параллелизма) и MAXDOP для сервера/базы данных/регулятора ресурсов. При возможности не запускайте слишком много конкурирующих запросов на своем оборудовании.

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

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



Только время выполнения параллельного запроса в пакетном режиме изменилось незначительно. Это также является замечательным свойством при рассмотрении общей масштабируемости сервера.