По материалам статьи Joe
Chang SQL
Server Parallel Execution Plans
Перевод Виталия
Степаненко
Начиная с версии 7.0, Microsoft SQL Server представляет возможность параллельной обработки запросов. Целью параллельного выполнения запроса, использующего большое количество данных, является ускорение его выполнения на многопроцессорном компьютере, чем это возможно при помощи одного потока. Books Online и различные документы Microsoft описывают принципы параллельного выполнения и использование настроек, влияющих на параллельное выполнение. Однако очень мало внимания уделяется объяснению планов выполнения с использованием параллелизма. Сравнение характеристик нескольких запросов в режиме распараллеливания плана исполнения и в режиме, когда параллельность отключена, поможет понять смысл некоторых характеристик параллельных планов, включая значения оценочных затрат. Исследование запросов с использованием и без использования параллельных планов исполнения дает дополнительную информацию о тех случаях, когда параллельное исполнение более предпочтительно, и когда его лучше отключать.
Параллельная обработка запроса
Рис.1 ниже показывает часть планов выполнения для запроса с использованием и без использования параллельных операций. Запрос - это простой SELECT с поисковым аргументом WHERE (SARG), агрегатами в списке SELECT, и выражением GROUP BY. Для непараллельного плана выполнения задано выражение OPTION (MAXDOP 1).
Рис.1. Непараллельные и параллельные части плана выполнения.
Заметим, что символы обычных операций SQL - таких, как Index Seek, Hash Match, и Compute Scalar - имеют желтый круг со стрелками в нижнем правом углу, в случае, когда в этом операции используется параллельное выполнение. Символ Parallelism/Gather Streams на рис.1 является специфическим для параллельных планов выполнения. Другими специфическими для параллельных планов выполнения символами являются Parallelism/Broadcast, Parallelism/Distribute Streams и Parallelism Repartition Streams.
Рис.2 ниже показывает левую часть двух планов выполнения из рис.1. Верхний запрос с относительными по отношению к пакету (запрос 1 и запрос 2) затратами в 66.65% - это непараллельный план выполнения, а нижний запрос с затратами в 33.35% - это параллельный план выполнения.
Рис.2. Относительные затраты непараллельного (вверху) и параллельного (внизу) планов выполнения.
Рис.3 ниже показывает окна детализации для символов SELECT непараллельного (слева) и параллельного (справа) планов выполнения. Непараллельный план выполнения имеет общие оценочные затраты в 121, а параллельный план - 60.7, из которых получаются относительные затраты в 66.65% и 33.35% на рис.2.
Рис.3. Окна детализации непараллельного (слева) и параллельного (справа) планов выполнения.
Нигде в общедоступной документации SQL Server не описана единица измерения затрат плана выполнения. На самом деле, вся документация Microsoft SQL Server по этому вопросу достаточно расплывчата.Являются ли единицы измерения затрат плана выполнения единицами времени или загрузки процессора? Если это загрузка процессора, то относится ли она к одному процессору или ко всем? Рассмотрение непараллельных и параллельных планов выполнения дает подсказку, что единицей измерения является скорее всего время, и что меньшие затраты плана выполнения показывают меньшее время выполнения, а не меньшую загрузку процессора.
Рис.4 ниже показывает окна детализации для двух главных компонентов каждого плана выполнения, рассмотренных ранее. Рис.4а - это непараллельный поиск по кластерному индексу, а рис.4b - это параллельный поиск по кластерному индексу. В обоих случаях оценочное количество обработанных строк одинаково, при этом затраты параллельного плана выполнения поиска по кластерному индексу равны 47.1095, что равняется примерно половине затрат на выполнение непараллельного поиска по кластерному индексу, равных 94.2190.
Рис.4а. Окно детализации непараллельного поиска по кластерному индексу.
Рис.4b. Окно детализации параллельного поиска по кластерному индексу.
Тот же случай наблюдается и для непараллельных и параллельных операций Hash Match/Aggregate, как показано на рис.4c и 4d.
Рис.4с. Окно детализации непараллельного выполнения операции Hash Match/Aggregate.
Рис.4d. Окно детализации параллельного выполнения операции Hash Match/Aggregate.
Нельзя поверить, что наличие 2 или более потоков, отдельно обрабатывающих части операции поиска по индексу или операции hash match, уменьшает общее количество циклов процессора на обработку всей операции, кроме некоторых исключений. На самом деле параллельная операция должна быть еще более затратной, учитывая затраты на слияние результатов работы потоков. Однако есть основания полагать, что параллельная операция выполняется быстрее, чем непараллельная операция, подразумевая, что все нужные ресурсы доступны. Поэтому логично заключить, что единицей измерения затрат плана выполнения SQL Server скорее всего является время, а не загрузка процессора.
Похоже, что затраты параллельных планов выполнения отражают время выполнения запроса, основанное на доступности двух процессоров, даже если доступны более чем 2 процессора. В SQL Server Book Online утверждается, что уровень параллелизма определяется во время выполнения в зависимости от доступности и загруженности ресурсов. Если ресурсов недостаточно, SQL Server может даже построить однопоточный план выполнения. Поэтому параллельный план только показывает, что параллельное выполнение возможно и отображает затраты в единицах времени, основанные на использовании двух процессоров.
Рис.5 ниже показывает часть параллельного плана выполнения для другого запроса. Рис.6а показывает окно детализации непараллельного плана выполнения для сканирования кластерного индекса таблицы Customer, а рис.6b показывает окно детализации параллельного плана выполнения для сканирования кластерного индекса таблицы Customer.
Рис.5. Параллельный план выполнения для второго примера.
Рис.6а. Окно детализации непараллельного сканирования кластерного индекса.
Рис.6b. Окно детализации параллельного сканирования кластерного индекса.
Непараллельную версию достаточно легче объяснить. Оценочные затраты ввода-вывода равны 2.45, а загрузка процессора равна 0.165 из общих затрат в 2.61, хотя сумма цифр и не равна в точности общим затратам. Параллельную версию объяснить сложнее. Затраты ввода-вывода составляют примерно половину непараллельных затрат ввода-вывода, а загрузка процессора - только одну четверть, но общие затраты параллельного плана в 2.529 ненамного меньше общих затрат непараллельного плана, равных 2.651.
В других случаях сканирование таблицы или индекса затраты ввода-вывода примерно одинаковы для параллельной и непараллельной версии, но загрузка процессора для параллельного плана составляет половину от загрузки непараллельного плана. В этих случаях общие затраты параллельного плана могут быть меньше, чем затраты непараллельного плана, но будут все же превышать половину затрат непараллельного плана. Странно, что план выполнения предполагает, что сканирование таблицы или индекса не выигрывает от параллелизма так же, как и операции поиска по индексу или hash match.
В некоторых запросах с параллельными планами выполнения затраты на выполнение некоторых операций параллельности, обычно Parallel/Repartition Stream, могут быть настолько высокими по сравнению с другими операциями, что общие затраты плана выполнения всего запроса будут выше, чем затраты непараллельного плана. Однако это не мешает использовать параллельный план выполнения.
Рис.7 показывает другой аспект параллельных планов выполнения. Таблица Nation в верхнем правом углу небольшая и сканируется при помощи непараллельной операции. Результаты сканирования распределены по нескольким потокам операцией Parallelism/Distribute Streams.
Рис.7. Параллельный план выполнения с непараллельным компонентом.
Анализ запроса TPC-H в SQL Server
Простым методом исследования параллельных планов выполнения является использование данных и генераторов кода из теста TPC-H. Transaction Processing Council (tpc.org) поставляет исходный код для программы dgen, используемой для создания набора данных для теста TPC-H и программы qgen для создания запросов. Большинство недавно опубликованных тестов TPC-H используют наборы данных в 100 гигабайт как минимум и гораздо большие. Однако даже набор данных размером в 1 гигабайт подходит для создания параллельных планов выполнения в SQL Server. Если набор данных достаточно маленький, чтобы полностью размещаться в памяти, то при этом дисковая система не становится узким местом, что позволяет для простых тестовых конфигураций сосредоточиться на исследовании загрузки процессора.
Табл.1 ниже показывает оценочные затраты плана выполнения для 22 запросов TPC-H с гигабайтной (только данные) таблицей LineItem, содержащей 6 миллионов строк. В этом случае 18 из 22 запросов сгенерировали параллельный план выполнения. В некоторых случаях (запросы 1 и 6) затраты параллельного плана выполнения составляют примерно половину затрат непараллельного плана. В большинстве случаев параллельный план получается несколько менее затратным, чем непараллельный план, но не на 50%. В одном случае, в запросе 5, параллельный план более затратный, чем непараллельный, несмотря даже на то, что план выполнения по умолчанию (без хинтов) параллельный.
|
Query |
Non-Parallel |
Parallel Plan |
|
1 |
121.3 |
60.7 |
|
2 |
5.0 |
N/A |
|
3 |
111.8 |
77.0 |
|
4 |
109.0 |
103.8 |
|
5 |
132.3 |
145.6 |
|
6 |
15.8 |
7.9 |
|
7 |
72.2 |
65.7 |
|
8 |
138.3 |
N/A |
|
9 |
199.3 |
N/A |
|
10 |
112.1 |
107.8 |
|
11 |
33.9 |
N/A |
|
12 |
136.1 |
127.9 |
|
13 |
32.4 |
29.5 |
|
14 |
6.4 |
6.0 |
|
15 |
5.2 |
4.0 |
|
16 |
19.5 |
17.5 |
|
17 |
20.5 |
20.3 |
|
18 |
177.8 |
152.0 |
|
19 |
48.5 |
47.7 |
|
20 |
24.8 |
20.7 |
|
21 |
367.6 |
360.6 |
|
22 |
15.8 |
15.7 |
Табл.1. Затраты плана выполнения для запросов TPC-H на гигабайтном наборе данных LineItem.
Табл.2 показывает лучшие измеренные значения времени выполнения для SQL Server 2000 и Windows Server 2003 на сервере с двумя процессорами Xeon 2.4 Гц с включенной технологией Hyper-Threading (HT) и с 2 гигабайтами памяти. Каждый запрос был последовательно запущен несколько раз, чтобы набор данных хранился в памяти, что можно определить по минимальной активности диска. Лучшее время из нескольких последовательных выполнений запроса выбирается, чтобы исключить оптимизированные затраты на выполнение запроса, которые сами по себе являются очень интересным предметом исследования. Есть случайные вариации продолжительности времени выполнения, даже между вторым, третьим и четвертым выполнениями. Продолжительность времени выполнения была отслежена в SQL Profiler вместе с загрузкой процессора и уровнем параллелизма (DOP), который определяет количество используемых потоков.
|
Query |
DOP 1 |
DOP 2 |
DOP 4 (HT) |
|
1 |
20,703 |
12,686 |
13,563 |
|
2 |
126 |
N/A |
N/A |
|
3 |
3,153 |
2,686 |
3,016 |
|
4 |
4,140 |
2,983 |
2,610 |
|
5 |
4,423 |
3,796 |
9,953 |
|
6 |
546 |
343 |
373 |
|
7 |
4,110 |
2,486 |
3,060 |
|
8 |
3,376 |
N/A |
N/A |
|
9 |
11,500 |
N/A |
N/A |
|
10 |
2,610 |
2,250 |
2,080 |
|
11 |
876 |
N/A |
N/A |
|
12 |
3,516 |
2,733 |
2,410 |
|
13 |
9,343 |
7,813 |
5,436 |
|
14 |
436 |
516 |
576 |
|
15 |
390 |
610 |
783 |
|
16 |
3,173 |
2,390 |
2,470 |
|
17 |
203 |
373 |
716 |
|
18 |
11,720 |
7,860 |
7,516 |
|
19 |
560 |
360 |
406 |
|
20 |
453 |
1,280 |
1,470 |
|
21 |
14,500 |
9,486 |
9,250 |
|
22 |
2,373 |
1,420 |
2,440 |
Табл.2. Лучшее измеренное время (мс) на гигабайтном наборе данных.
Когда DOP равен 2, то похоже, что операционная система или SQL Server знают, что нужно использовать два разных физических процессора, а не два логических процессора на одном физическом процессоре. Возможно, что это достигается полностью при помощи упорядочивания физических и логических процессоров. При DOP, равном 4, есть только 2 физических процессора, поэтому это не является тестированием способности SQL Server производить параллельное выполнение на 4 физических процессорах, а скорее является тестированием преимущества HT в параллельных планах выполнения.
Синий шрифт в столбце DOP 2 показывает случаи, когда время выполнения запроса уменьшалось более чем на 30%. В целом 7 из 18 запросов с параллельным планом выполнения показали уменьшение времени более чем на 30% при 4 запросах, которые оказались медленнее. Суммарное время выполнения для всех 18 запросов с параллельными планами выполнения оказалось 29% меньше, чем для тех же 18 запросов без параллельных планов выполнения. Запросы, которые оказались медленнее, не были предсказаны как более медленные планом выполнения, а запросы, которые были предсказаны как более медленные, оказались на самом деле быстрее при использовании параллельного выполнения. Похоже, что параллельное выполнение сканирования таблицы и индекса значительно уменьшает время выполнения, несмотря на то, что оценочные затраты плана выполнения этого никак не отражают.
Во всех запросах, кроме одного, загрузка процессора была выше при использовании параллельного плана выполнения по сравнению с непараллельным планом. Для 18 запросов с параллельными планами общий средний прирост загрузки процессора составил 52%. По этой причине параллельные планы выполнения не рекомендованы для приложений, где желательно очень большое количество транзакций, обычно это OLTP приложения. Параллельные планы выполнения очень полезны, когда скорость выполнения нужна для одного или нескольких пользователей, обычно это операции обслуживания и приложения DSS.
В пяти запросах параллельное выполнение на всех логических процессорах обеспечивает дальнейшее улучшение производительности, но в пяти других запросах использование дополнительных логических процессоров ухудшает производительность по сравнению с непараллельным или параллельным выполнением с двумя физическими процессорами. Hyper-threading - это потенциально очень полезная особенность. Однако её не следует использовать вслепую. К счастью, Microsoft очень тщательно исследует использование HT в Yukon и разработает подходящие алгоритмы для определения, когда HT нужно использовать, а когда - нет.
Исследование планов выполнения для набора запросов определенно предполагает, что единицей измерения в затратах плана выполнения является время, а не загрузка процессора. Поэтому параллельный план выполнения может показывать более низкие затраты, чем непараллельный план. Более того, SQL Server оценивает параллельное выполнение определенных операций, таких, как поиск по индексу и hash match, в 2 раза быстрее с двумя потоками по сравнению с одним потоком. По некоторым причинам SQL Server не показывает для сканирования таблицы и индекса ощутимого преимущества от параллельного выполнения, несмотря даже на то, что действительная оценка параллельного выполнения запроса показывает такое преимущество.
Параллельные планы выполнения требуют дополнительных операций, из-за которых дополнительные затраты могут свести на нет преимущества параллельного плана выполнения, но это можно определить с помощью выполнения тестов. Не было определено, какую единицу времени отражают затраты плана выполнения. Разумным предположением может быть то, что затраты плана выполнения измеряются в секундах процессора, что было типичным при разработке SQL Server 7.0. Это могло быть что угодно от Pentium 100MHz до Pentium Pro 200MHz. Возможно, что реальное время выполнения было в среднем в 20 раз быстрее на системах с процессором Xeon 2.4 Гц, чем отражалось в затратах плана выполнения, если единица измерения является секундой.