gifts2017

SQL: Добиваемся выполнения параллельного плана запроса

Опубликовал Alexei Zhovner (jan27) в раздел Программирование - Практика программирования

Эта публикация предназначена для разработчиков SQL Сервера, которые испытали особый вид разочарования, который приходит только после часов, затраченных на попытки убедить оптимизатор запросов сгенерировать параллельный план выполнения. Эта ситуация часто возникает, когда сделав очевидно безопасное изменения в тексте умеренно сложного запроса; изменение, которое как-то превращает параллельный план, который выполнялся в десять секунд, в пятиминутного, выполняющегося последовательно монстра.
Авторский перевод

Оригинал статьи

Пол Уайт

SQL Сервер предоставляет ряд подсказок ("hints")для запросов и таблиц, которые позволяют опытным практикам в большей степени контролировать окончательную форму плана запроса. Эти подсказки, как правило, рассматриваются как инструмент крайнего средства, потому что они могут сделать код сложнее, ввести дополнительные зависимости, и могут помешать оптимизатору реагировать на будущие изменения в индексации или распределении данных. Одной из таких подсказок запроса является (более) популярная OPTION(MAXDOP 1), которая не дает оптимизатору предлагать планы, использующие параллелизм. К сожалению, в настоящее время нет соответствующей подсказки,чтобы заставить оптимизатор выбрать параллельный план.

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

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

Всякий раз, когда оптимизатор запросов производит последовательный план вместо "явно лучшего" параллельного плана, всегда есть причина. Оставляя в стороне явно очевидные причины, такие как "max degree ofparallelism" установлен в 1, Resource Governor workload group работает с MAX_DOP = 1, или наличие только одного логического процессора, доступного для SQL сервера, обычными причинами последовательного плана являются параллелизм ингибирующие операции, ошибки мощностной оценки, стоимостное ограничения модели, а также вопросы алгоритма.

Параллелизм ингибирующие компоненты

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

Этот список меняется от версии к версии, например, эти вещи делают весь план последовательным на SQL Server 2008 R2 SP 1:

·        Изменение содержимого переменной (variable) таблицы (чтение допустимо)

·        Любая скалярная функция T-SQL (которые являются злом в любом случае)

·        CLR скалярные функции, помеченные как выполняющие доступ к данным (нормальные допустимы)

·        Случайные встроенные функции, включая OBJECT_NAME, ENCYPTBYCERT и IDENT_CURRENT

·        Доступ к системным таблицам (например sys.tables)

К сожалению, список встроенных функций достаточно длинный и,кажется, не соответствует модели. Error_number и @@TRANCOUNT также вызывают последовательный план, а @@ERROR и @@NESTLEVEL нет. Ограничение скалярных функций T-SQL также немного туманны.Любая ссылка на таблицу (или view) с вычисляемым столбцом (computed column), который использует скалярную функцию T-SQL, приведет к последовательному плану, даже если проблемный столбец отсутствует в запросе.

Эти особенности запросов являются примерами, которые требуют последовательную зону в плане:

·        TOP

·        Последовательный проект (например ROW_NUMBER, RANK)

·        Multi-statementT-SQL table-valued functions

·        Обратный диапазон сканирования (вперед нормально)

·        Глобальные скалярные агрегаты

·        Common sub-expression spools

·        Рекурсивные запросы, использующие обобщенные табличные выражения (Recursive CTEs)

Информация, представленная выше, основана на оригинальном списке, опубликованном Крейгом Фридманом, и обновлена для 2008 R2.

Одним из способов проверить, что запрос не имеет параллелизм ингибирующих компонентов является проверка запроса, используя CPU cost multiplier. Это должно быть проделано исключительно на отдельной тестовой системе, где вы в состоянии очистить весь кэш планов после тестирования. Идея состоит в том, чтобы использовать недокументированную и неподдерживаемую команду DBCC, чтобы временно увеличить процессорную стоимость операторов плана запроса. Это не 100% тест (некоторые редкие параллелизуемые запросы не будет генерировать параллельный план с этой техникой), но довольно надежный:

DBCC FREEPROCCACHE

DBCC SETCPUWEIGHT(1000)

GO

-- Query to test

SELECT

    COUNT_BIG(*)

FROM Production.Product AS p

LEFT JOIN Production.TransactionHistory AS th ON

    p.ProductID= th.ProductID

GO

DBCC SETCPUWEIGHT(1)

DBCC FREEPROCCACHE

Последние команды для сброса весового фактора CPU и очистки кэша планов очень важны.

Если вы получаете оценочный параллельный план для конкретного тестового запроса, это показывает, что параллельный план, по крайней мере, возможен. Варьируя значениями передаваемых команд DBCC можно регулировать множитель (multiplier) для нормальных затрат процессора, так что вы, вероятно, сможете увидеть различные планы для различных значений. Иллюстрированного фактора, установленного в 1000 зачастую достаточно, чтобы получить оценочный параллельный план, но вы, возможно, можете экспериментировать с более высокими значениями. Не рекомендуется использовать оценочные планы, полученные с помощью этого метода напрямую в USE PLAN hints или структуры планов, потому что в этом случае нет планов, которые оптимизатор произвел естественным путем. Чтобы было ясно, прямое использование планов, скорее всего,сделает продуктивную систему неподдерживаемой и виновное лицо может быть уволено, застрелено, или, возможно, и то и другое.

Ошибки мощностной оценки

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

·        Много-колоночная статистика (Multi-column statistics) (для корреляций)

·        Фильтрованная статистика или индексы (для больших шагов гистограммы)

·        Подсчитанные колонки (Computed columns) на фильтрующих выражениях в запросе (во избежание мощностных догадок)

·        Хорошая информация ограничения (внешние ключи и проверочные ограничения)

·        Реализация части запроса во временных таблицах (более точные статистические данные в глубоких планах)

·        Регулярные подсказки (hints), такие как OPTIMIZE FOR

В общем, все, что вы можете сделать - это обеспечить, что оцениваемое количество строк близко к реальным значениям и это поможет оптимизатору оценить стоимость последовательной и параллельной альтернативы более точно. Многие неудачи выбора параллельного плана вызваны неточным подсчетом строк.

Ограничения модели

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

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

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

Стоимостные ограничения

Когда SQL сервер оценивает параллельный план, это в целомснижает стоимость процессора для параллельного итератора по множителю, равномуожидаемому времени выполнения DOP. Например, предыдущий запрос может произвестиследующие последовательные и параллельные планы:



Принимая оператора слияния (Merge Join) в качестве примера, параллельная версия имеет снижение стоимости процессора на коэффициент 4, когда ожидаемое время выполнения DOP = 4 (последовательный план на левой стороне, параллельный справа):


С другой стороны, сканирование индекса (Index Scan) не показывает снижения стоимости ввода / вывода, при том, что стоимость процессор снова снижается в четыре раза:


Как упоминалось ранее, разные операторы оценивают себяпо-разному (например, слияние многие-ко-многим также имеет компонент стоимостиввода / вывода, что может быть уменьшено в четыре раза). Эти детали такженесколько различаются между релизами, так мы показываем это здесь, чтобы датьвам представление общего подхода, а не слишком углубляться в особенности.

Глядя снова на последовательные и параллельные планы, ясно,что, какой из двух планов стоит дешевле, зависит от того, сэкономит липараллельный план достаточно за счет снижения затрат процессора и ввода / выводана эксплуатацию в различных операторах, чтобы увеличить затраты надополнительных операторов в плане . В этом случае, три лишних оператора обмена -два Перераспределение Потоков (Repartition Streams) перераспределяют строки дляправильных результатов при слиянии, и один Собрать Потоки (Gather Streams)чтобы объединить потоки к единому конечному результату.

Тогда, когда цифры работают означает, что это частонапряженная борьба между лучшим вариантом параллельной или последовательнойальтернативой плана. Во многих реальных случаях, разница между ними может бытьочень небольшой - что делает её еще более неприятной, когда последовательнаяверсия может выполниться пятьдесят раз, в то время пока выполняетсяпараллельная версия. Еще один момент, который необходимо упомянуть это то, чтооценка DOP ограничена количеством логических процессоров, видимых SQL серверу,деленное на два. Моя тестовая машина имеет восемь ядер, доступных SQL серверу,но оценка DOP используемая для калькуляции расчетов ограничена до четырех. Этоимеет очевидные последствия для расчетов, где затраты на CPU и I / O, какправило, делятся на расчетной DOP из четырех, а не восьми.

Заметка о параллельных вложенных циклах (Parallel Nested Loops)

Планы с соединенными вложенными циклами могут статьсерьезной проблемой, потому что внутренняя сторона почти всегда работает внесколько потоков последовательно. Значки параллелизм по-прежнему присутствуют,но они показывают, что есть DOP-независимыепоследовательные потоки. Различие, пожалуй, тонкое, но это (а) объясняет,почему операторы, которые обычно заставляют последовательную зону работать'параллельно' на внутренней стороне циклами; и (б) оптимизатор не снижаетзатраты процессора на внутренней стороне на предполагаемое время выполнения DOP. Это ставит вложенныециклы в несправедливо невыгодное положение, когда дело доходит до определениястоимости параллельности, по сравнению с Hash и Merge Joins.

Вопросы алгоритмов

Эта последняя категория касается того, что оптимизатор можетвообще не дойти до оценки параллельного плана. Один из способов, когда этоможет иметь место, когда окончательный план находится на стадии тривиального плана(Trivial plan).Если простой план возможен и его результирующая стоимость меньше настроенныхпороговых затрат параллелизма, остальные этапы полной оптимизации пропускаютсяи последовательный план немедленно возвращается.

Тривиальный план

Следующий запрос имеет оценочную стоимость последовательногоплана около 85 единиц, но с параллелизмом порогового значения (parallelismthreshold) установленным в 100производится тривиальный план (как показано в свойстве плана "Уровеньоптимизации" или путем проверки изменений вsys.dm_exec_query_optimizer_info, как показано ниже :

	SELECT
	    deqoi.[counter],
	    deqoi.occurrence
	FROM sys.dm_exec_query_optimizer_info AS deqoi
	WHERE
	    [counter] IN ('trivial plan', 'search 0', 'search 1', 'search 2')
	GO
	SET SHOWPLAN_XML ON
	GO
	SELECT
	    COUNT_BIG(*)
	FROM dbo.bigTransactionHistory AS bth
	OPTION (RECOMPILE)
	GO
	SET SHOWPLAN_XML OFF
	GO
	SELECT
	    deqoi.[counter],
	    deqoi.occurrence
	FROM sys.dm_exec_query_optimizer_info AS deqoi
	WHERE

	    [counter] IN ('trivial plan', 'search 0', 'search 1', 'search 2')


Когда стоимость порога снижена до 84 единиц, мы получаем параллельный план ...


Более глубокий анализ показывает, что запрос все ещеквалифицирован для тривиального плана (и этап был запущен), но окончательнаястоимость превысила порог параллелизма и оптимизация продолжилась. Этот запросне может претендовать на "search 0" (обработки транзакций), потомучто, как минимум, три таблицы необходимы.

Так, оптимизация переходит к "search 1" (Быстрыйплан), который проходит два раза. Она работает только с учетомпоследовательного плана, и выходит с лучшей стоимостью в 84,6181. Так как этопревышает порог 84, Быстрый План повторно запускается с включенной опциейпараллельного плана. Результатом является параллельный план по стоимости44,7854. План не отвечают условиям для входа в "search 2" (Полнаяоптимизация), таким образом, мы получаем готовый план.

Достаточно хороший план и таймаут.

Возвращаясь к коду, который предотвращает параллельный план,последняя категория охватывает вопросы, которые входят в стадию быстрого плана(Quick Plane), но этот этап заканчивается раньше, либо с сообщением, что найдендостаточно хороший план или таймаут. Оба этих эвристических анализа служат длятого, чтобы не дать оптимизатору тратить больше времени на оптимизацию, чем онвыиграет от снижения расчетного время выполнения (стоимости). ДостаточноХороший План возвращается, когда текущий план имеет низкую стоимость и она дотакой степени низка, что дальнейшие усилия по оптимизации являютсянеоправданными.

Таймаут является связанным явлением: в начале стадии,оптимизатор ставит перед собой 'бюджет' ряда применяемых правил, оценивает,может выполнять в то время, подобранное первоначальной оценкой стоимости плана.Это означает, что деревья запросов, которые начинаются с более высокойстоимости получают соответственно большую возможность применения правил(примерно сопоставимый с числом ходов, которые шахматная программа обдумываетвперед). Если оптимизатор исследует допустимое количество правил доестественного конца стадии оптимизации, он возвращает лучший полный план в этойточке с сообщением Таймаут. Это может произойти во время первого запуска'search 1', что мешает нам достичь второго захода, который добавляетпараллелизм.

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

	SELECT * FROM Production.Product AS p

Как и следовало ожидать, этот запрос, как правило,оптимизирован с помощью тривиального плана (здесь нет выбора плана на основезатрат):


... Но когда порог стоимости (cost threshold) устанавливается равным нулю, мы получаем полную оптимизацию с "Time Out" ... оптимизатор закончил работу по таймауту, выясняя как сделать SELECT * из одной таблицы!


В данном конкретном случае, оптимизатор закончил работу по таймауту после 15 задач (обычно проходят многие тысячи). "Time Out" результат иногда может быть показателем того, что введенный запрос является слишком сложным и его интерпретация вовсе не так проста.

Решение

Нам нужна надежная подсказка (hint) плана запроса, аналогично MAXDOP, чтобы мы могли указать в качестве последнего средства, когда все другие методы приводят к последовательному плану, и где параллельно альтернатива гораздо предпочтительнее, конечно. Я действительно хочу подчеркнуть, что очень много случаев нежелательных последовательных планов обусловлены тем, что разработчики не дают оптимизатору информацию хорошего качества. Я вижу очень мало систем с такими вещами, как надлежащей "multi-column" статистики, отфильтрованных индексов / статистики и адекватных ограничений. Еще реже, я вижу (возможно, несохраненные) вычисляемых столбцов (computed columns), созданных на основе выражений фильтра запроса, чтобы помочь с мощностными оценками. С другой стороны, нереляционные базы данных с недостатком индексации, и решительно нереляционные запросы чрезвычайно распространены. (Так, разработчики баз данных жалуются на то, что оптимизатор иногда принимает плохие решения!)

Всегда есть флаг трассировки

В то же время, есть обходной путь. Он не идеален (и, безусловно, выбирать его нужно только в крайнем случае), но есть недокументированный (и неподдерживаемый) флаг трассировки, который эффективно снижает пороговую стоимость (cost threshold) в ноль для конкретного запроса. На самом деле это несколько больше, чем это; например, следующий запрос не будет генерировать параллельный план даже с нулевой пороговой стоимостью:

 SELECT TOP (1)

    p.Name

FROM Production.Product AS p

JOIN Production.TransactionHistoryAS th ON

    th.ProductID= p.ProductID

ORDERBY

    p.Name





Это, конечно, совершенно тривиальный план запроса - первая строка сканирования присоединена к одной строке поиска. Общая оценочная стоимость параллельного плана 0,0065893. Возвращаясь к пороговой стоимости параллелизма, по умолчанию установленной в 5, только для полноты ощущений, мы можем получить параллельный план (исключительно в демонстрационных целях) с помощью флага трассировки:

SELECT TOP (1)

    p.Name

FROM Production.Product AS p

JOIN Production.TransactionHistoryAS th ON

    th.ProductID= p.ProductID

ORDERBY

    p.Name

OPTION (RECOMPILE, QUERYTRACEON 8649)


Параллельная альтернатива возвращена, несмотря на то, что она стоит намного выше 0.0349929 (в 5,3 раза больше стоимости последовательного плана). В ходе моих тестирований, этот флаг трассировки оказался неоценимым в некоторых особенно сложных случаях, когда параллельный план имеет важное значение, но нет разумного способа получить его от стандартного оптимизатора.

Вывод

Даже эксперты с многолетним опытом работы с SQL сервером и детальным знанием внутренних механизмов должны быть осторожны с этим флагом. Я не могу рекомендовать вам использовать его непосредственно на продуктивной базе, если не будет таких рекомендаций от Microsoft, но вы могли бы использовать его на тестовой системе в качестве крайнего случая, может быть, для генерации структуры плана или USE PLAN подсказку (hint) для использования в продуктивной базе (после тщательного анализа).

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Александр Капустин (kapustinag) 11.09.14 21:31
(0) Не получилось найти нормальный перевод для "параллелизм ингибирующие операции", "параллелизм ингибирующие компоненты"?
Ингибитор - из школьного курса химии - вещество, замедляющее течение химической реакции.
Однокоренной глагол имеет следующие общеупотребительные варианты перевода:
- подавлять, препятствовать, тормозить, запрещать, мешать, сдерживать.
Далее в тексте написано, что имеются в виду операции, которые не могут выполняться параллельно, или даже весь запрос, включающий такие операции, исполняется только последовательно.

Поэтому в авторском переводе можно было бы вместо "параллелизм ингибирующие операции" написать, например, "Операции, препятствующие параллелизму".

2. Alexei Zhovner (jan27) 12.09.14 05:09
(1) я особо и не искал - это латинское слово, единственное не учел, что, возможно не все знают
3. Андрей Казанцев (ander_) 17.09.14 07:36
зато звучит вон как солидно :)
4. Антон Стеклов (asved.ru) 18.09.14 04:42
По-русски это называется "блокирующий оператор".

5. Alexei Zhovner (jan27) 18.09.14 05:07
(4) тогда уж блокирующие компоненты, но ингибитор мне больше нравится :)
6. Павел (Yimaida) 18.09.14 15:12
7. Максим Кузнецов (Makushimo) 22.09.14 08:00
где-то на середине перестал понимать смысл текста
но плюс, т.к информация полезная
не все ее поймут, конечно
8. Alexei Zhovner (jan27) 22.09.14 08:48
(7) спасибо, мне тоже показалась интересной, решил перевести для широкой аудитории