Заметки Дмитрия Пилюгина о Microsoft SQL Server

Оптимизатор (ч.4): Optimization: Full Optimization: Search 1

В данном разделе: — update statistics with row_count, page_count; — преобразования memo; — параллельный план;

Данная фаза, называется также Quick Plan. Как мы уже говорили, запросы могут миновать стадию Transaction Processing (search 0), и сразу перейти к этой фазе, если в запросе менее трех таблиц. Также эта фаза примечательна тем, что на ней осуществляется поиск параллельного плана.

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

Начнем с того, что изменим наши данные так, чтобы план запроса, найденный на предыдущей стадии search0, перестал удовлетворять внутреннему порогу оптимизатора для этой стадии и оптимизатор продолжил поиски на стадии search1. Для этого, нужно увеличить размер исследуемых таблиц, однако, мы воспользуемся другим приемом, а именно недокументированными опциями команды update statistics — with row_count, page_count. Эти опции изменяют метаданные об индексах, устанавливая те значения, которые мы укажем.

Помните, что все операции мы выполняем на тестовом сервере.

Результат (до и после):

Вы видите, что метаданные таблиц изменились, теперь, количество страниц и строк в таблицах стало равным тому, что мы указали.

Поговорим немного подробнее об этой команде. Хотя это опции команды update statistics, но в реальности сама статистика не обновляется. Посмотрим, к примеру, какие есть статистики для таблицы t1:

Одна статистика для кластерного индекса, и две автоматически созданные статистики для колонок b,c.

Прим. Кстати, как формируется имя автоматической статистики, последняя часть – это object_id объекта для которого создана статистика, дальше идет id столбца, дальше префикс Sys, ну а WA – встречал в литературе такое описание, что якобы это Washington, место, где располагается команда разработки, однако люди, которые сейчас работают в оптимизаторе сказали, что этой истории не помнят. Впрочем, может, помнит кто-то другой, пока будем считать это просто легендой.

Давайте выполним команду для просмотра статистики по первичному ключу (у вас наверняка будет другой идентификатор первичного ключа, так что поменяйте на свой):

Как видите, никаких данных о 1000000 строк нет. Т.е. команда update statistics with row_count,page_count – не затрагивает саму статистику.

Более того, если вы выполните (заменив PK__t1__3BD0198E0F624AF8 на то значение, которое будет у вас):

Команда пройдет успешно, но если вы выполните (т.е. попытаетесь обновить данные по статистике без индекса):

То результатом будет: Msg 3739, Level 11, State 3, Line 1 Cannot UPDATE the index ‘_WA_Sys_00000002_0D7A0286’ because it is not a statistics collection.

Если вы попытаетесь вернуть все значения назад, при помощи команды:

То результат останется прежним:

Все это говорит о том, что при обновлении метаданных в данном случае, реальная статистика никак не затрагивается, а обновляются только метаданные индекса. Поэтому, конечно было бы логичнее поместить этот функционал, например, в alter index, но, впрочем, т.к. эта опция недокументированна, то и претензии предъявлять неуместно.

По этому, вернуть значения к «нормальным» можно перестроив индексы:

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

Теперь, выполним наш запрос, с информацией по стадиям оптимизации:

Мы видим, что запрос действительно прошел стадии, упрощения, search 0. Почему в выводе search1 встречается два раза? Дело в том, что эта фаза может быть повторно выполнена для поиска параллельного плана. Стоимость 8.5812, больше заданного по умолчанию значения 5. Чтобы посмотреть, какое пороговое значение стоимости для параллелизма задано у вас, выполните:

Теперь, измените значение на 10 и перезапустите запрос:

Вы увидите, что теперь вывод:

Т.е. повторный запуск фазы search1 и исследования для параллельного плана, не производятся, т.к. стоимость 8.45349 меньше значения10, после которого нужно производить поиск параллельного плана.

Не забудем все вернуть на круги своя:

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

А пока, давайте взглянем на то, как между стадиями оптимизации преобразуется структура memo. Для этого выполним запрос с некоторыми недокументированными флагами, описанными в предыдущей части:

Я не буду приводить здесь вывод с консоли, т.к. он довольно объемный. Приведу сокращенный результат в виде рисунка.

Из этого результата видно, что на стадии search 1, в качестве входного, изначального memo используется результат предыдущей стадии search 0 (вы можете сравнить вывод с консоли, я например, сравнивал вывод memo, сохранив результат в два текстовых файла, при помощи Total Commander). Результаты 2 и 3 – полностью совпадают. После этого, идет исследование альтернатив плана на стадии search 1, и оно отображается в части 4. Для стадии exploration при поиске параллельного плана, новое memo не строится, используется то, что было на стадии поиска последовательного, и в результате, мы получаем итоговое memo — 5. После этого выполняется поиск самого дешевого плана и в данном случае, при одинаковых стоимостях, выбирается последовательный.

Вернемся к вопросу, есть ли способ, заставить оптимизатор использовать параллельный или последовательный план? В случае последовательного плана, работает документированный хинт option(maxdop 1), а как насчет параллельного? Да, возможность есть, но она не документирована, это использование очередного флага трассировки 8649.

Выполним наш запрос и посмотрим на план:

Мы видим, что теперь, мы получили параллельный план. Если выполнить этот запрос, со всеми, упомянутыми ранее, включенными флагами трассировки, то мы не найдем в memo физический оператор Parallelism. Однако, в выходном дереве физических операторов, будет присутствовать операция Exchange Start:

Это говорит о том, что используется оператор параллелизм. В случае, когда в плане отображаются другие операторы параллелизма, например Parallelism — Repartition streams, вы можете увидеть в дереве операцию «Exchange Partition» и т.д. Почему мы не видим отдельных «параллельных» операторов. Дело в том, что параллелизм в сиквеле достигается не за счет распараллеливания отдельных операторов, а за счет распараллеливания всего плана (хотя в нем могут быть и последовательные ветки, на что мы позже посмотрим). Нагляднее это продемонстрирует позаимствованная мной картинка из блога Craig Freedman-а.

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

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

То вы получите последовательный план.

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

Однако, параллельный план возможен не всегда.

Выполним такой запрос, с флагом параллельного запроса:

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

Далее, другой запрос:

Multi-statement Table Valued Function – требуют последовательной ветки в плане, однако не ограничивает в параллелизме весь план.

И, наконец, inline table function:

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

Теперь, выполним запрос:

Использование некоторых (но не всех) встроенных функций, также ограничивает параллелизм.

Предложение top может форсировать появление последовательной ветки в параллельном плане. Выполним запросы и посмотрим на планы:

Как вы видите, в первом случае, top действительно привел к последовательной ветке в плане, во втором нет. Однако, следует сказать, что в случае Nested Loops, для операторов во внутреннем цикле, практически всегда идет последовательное выполнение, обрабатывая по одной строке внешнего цикла, хотя в плане все равно присутствует значок параллельного плана. Давайте выполним один и тот же запрос с флагом параллельного плана и без:

Посмотрим на план и оценки:

Посмотрим на оператор Clustered Index Scan t1. Parallel Plan: 1.2932 (CPU = 0.550079) Serial Plan: 1.84328 (CPU = 1.10016) В случае параллельного плана, стоимость CPU снизилась в два раза (1.10016/2=0.55008), т.е. сервер масштабировал оценку, учитывая, что просмотр таблицы может происходить в несколько потоков. Что повлияло на общую оценку этого оператора.

Посмотрим на оператор Clustered Index Seek t3. Parallel Plan: 2.12011 (CPU = 0.0001581) Serial Plan: 2.12011 (CPU = 0.0001581) Как мы помним, для операции lookup в Nested Loops в поле CPU оператора отражается всегда стоимость одной операции, поэтому не будем смотреть на то, что значение 0.0001581 и там и там одинаково. Однако, что интересно, и общая стоимость оператора никак не изменилась, не было сделано никакого масштабирования в зависимости от числа процессоров. Знак параллельного выполнения в данном случае, может означать то, что операторы во внутренней части цикла Nested Loops, выполняются последовательными потоками не зависящими от DOP. Об это также можно почитать в статье Paul White-а, и в упомянутом выше блоге Craig Freedman-а.

Совсем ли отсутствует реальный параллелизм во внутренней части оператора соединения вложенными циклами? Нет, не совсем. Например, случай, когда во внешней таблице один ряд, выполните:

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

На этом, мы завершаем разговор про параллельные планы и стадию search 1.

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

upd.06.09.2012 В 2012 сервере появились новые интересные элементы плана выполнения. В том числе, касающиеся параллельного плана. Например, можно посмотреть причину, почему не сгенерировался параллельный план. За это отвечает недокументированный атрибут NonParallelPlanReason, элемента QueryPlan. Также, появился элемент OptimizerHardwareDependentProperties, в нем нас интересует атрибут EstimatedAvailableDegreeOfParallelism. Отмечу, что если вы также как и я, пока пользуетесь SSMS 2008, при работе с 2012 сервером, то не пользуйтесь графическим интерфейсом просмотра планов (даже если потом вы переключитесь в xml). Т.е. не включаете режим Include actual Execution Plan, и не пользуйтесь Display Estimated Execution Plan. Вместо этого, можно включить режим или задействовать инструменты, перечисленные ниже:

Получив план одним из этих способов, посмотрите содержимое xml, вы увидите искомые атрибуты. В частности, выполняя запросы с флагом 8649 на редакции Express, я не получил параллельного плана, зато получил такое объяснение:

< QueryPlan NonParallelPlanReason =»NoParallelPlansInDesktopOrExpressEdition« …/>

Если попробовать выполнить запрос со скалярной функцией, о котором говорилось выше, то причина указывается просто:

< QueryPlan NonParallelPlanReason =»CouldNotGenerateValidParallelPlan« …/>

Не очень информативно. Ранее мы использовали недокументированную команду dbcc optimizer_whatif. Теперь, можно видеть как ее выполнение отражается в плане. Если выполнить dbcc optimizer_watif (1,1), или dbcc optimizer_whatif(‘CPUs’,1), что одно и то же, и посмотреть на атрибут OptimizerHardwareDependentProperties. То можно увидеть

< OptimizerHardwareDependentProperties … EstimatedAvailableDegreeOfParallelism =»1» />

< OptimizerHardwareDependentProperties … EstimatedAvailableDegreeOfParallelism =»4» />

Потом, не забудьте сбросить результаты экспериментов.

— Microsoft SQL Server 2008 Internals (Kalen Delany) — Counting, Enumerating, and Sampling of Execution Plans in a Cost-Based Query Optimizer (Florian Waas, Cesar Galindo-Legaria) — Inside the SQL Server Query Optimizer (Benjamin Nevarez) (можно скачать PDF) — Microsoft SQL Server 7.0 Query Processor (Goetz Graefe, Jim Ewel, and Cesar Galindo-Legaria) — Inside Microsoft SQL Server 2008 — TSQL Querying (Itzik Ben Gan) — Блог Craig Freedman-а — Статья: Forcing a Parallel Query Execution Plan