PDF
Продолжаем отключать внутренние пороги оптимизатора.
В первой части были приведены общие теоретические сведения (на которые я буду ссылаться, по этому, рекомендую их просмотреть, если еще не успели), а так же представлен флаг трассировки 8780 который устанавливает timeout в очень большое число, фактически отключая его.
Осталось несколько других механизмов управляющих тем, когда происходит оптимизация и какие учитываются альтернативы:
- Timeout
- Cost Based Pruning Factor
- Discarding
Вернемся к запросу из первой части:
set showplan_xml on go with cte as(select t1.* from t1 join t2 on t1.a = t2.b where t1.b > 1) select t1.a, t1.b, t1.c, cte.b, cte2.c from t1 join cte on t1.a = cte.b join cte cte2 on t1.c = cte2.b option ( recompile ,querytraceon 3604 ,querytraceon 8675 ,querytraceon 8780 ) go set showplan_xml off go
Причина завершения оптимизации:
Если задаться вопросом, можно ли как-то продолжать оптимизацию не учитывая порог, при котором план считается достаточно хорошим, то ответ будет — да, можно.
Согласно алгоритму, описанному в первой части, на этапе Optimize Inputs производится оценка наилучшего плана, на этапе Optimize Group сохранение лучшего плана в Memo. Внутри сервера эти операции происходят в функциях CTask_OptInputs::Perform и CTask_OptimizeGroup::Perform соответственно.
Если присмотреться повнимательнее к этим функциям, то можно увидеть нечто вроде этого:
Число 21DF в шестнадцатеричной системе, это 8671 в десятеричной, это ни что иное как флаг трассировки, который управляет тем как рассчитывается наилучший план.
Давайте посмотрим на работу флага в действии. Выполним предыдущий запрос с этим флагом и без него, и сравним результаты.
set showplan_xml on go with cte as(select t1.* from t1 join t2 on t1.a = t2.b where t1.b > 1) select t1.a, t1.b, t1.c, cte.b, cte2.c from t1 join cte on t1.a = cte.b join cte cte2 on t1.c = cte2.b option ( recompile ,querytraceon 3604 ,querytraceon 8675 ,querytraceon 8780 ) go with cte as(select t1.* from t1 join t2 on t1.a = t2.b where t1.b > 1) select t1.a, t1.b, t1.c, cte.b, cte2.c from t1 join cte on t1.a = cte.b join cte cte2 on t1.c = cte2.b option ( recompile ,querytraceon 3604 ,querytraceon 8675 ,querytraceon 8780 ,querytraceon 8671 ) go set showplan_xml off go
Посмотрим информацию на вкладке Messages:
Как видно из результатов, в случае со включенным флагом, на кажой стадии было проведено больше исследований (больше задач оптимизации), кроме того, на стадии search 1, оптимизатор использовал все доступные преобразования, и перешел на следующую стадию, search 2. Если флаг выключен, оптимизация останавливается еще на стадии search 1.
Насколько это хорошо или плохо? Опять же, концепция Good Enough Plan придумана не зря. Если мы посмотрим на планы, то будет видно, что они почти ничем не отличаются. За исключением того, что во втором плане исчезла секция «Reason For Early Termination Of Statement Optimization : Good Enough Plan Found», и увеличилось времени компиляции с памятью.
Если взять запрос посложнее, например, близкий к запросу из первой части, который превышал псевдо-бесконечный timeout.
И выполнить его со включенным флагом, и без — то разница во времени компиляции станет заметна невооруженным глазом.
Посмотрим на планы:
Как мы видим, стоимость планов абсолютно одинаковая (планы не отличаются), только время компиляции у первого плана 3 секунды, у второго 14.
Итог: 11 секунд было потрачено впустую!
Любопытный факт, на который можно обратить внимание, это то, что у первого плана тоже нет секции «Reason For Early Termination: Good Enough Plan». Это значит, что на ранней стадии не был выявлен хороший план, и оптимизатор честно отработал все преобразования до тех пор пока не нашел хороший.
В чем тогда разница, и что отключает флаг 8671? Разница в количестве исследуемых ветвей, и, соответственно, количестве преобразований.
В случае «нормальной» обработки запроса, при поиске наиболее эффективного плана, потенциально неэффективные ветви отбрасываются(pruning), ведется своеобразный «трэккинг» лучшего плана. Если на определенном этапе оптимизатор определяет, что он еще может делать преобразования, но достаточно хороший план уже найден — то происходит ранее завершение с формулировкой «Good Enough Plan Found». Если же мы при помощи флага 8671 отключаем алгоритм сравнения с фактором отвечающим за то, что считать ли план хорошим, или отбросить как неэффективный — мы значительно увеличиваем время на поиск и исследование альтернатив. Мы начинаем исследовать неэффективные ветви, тратя на это ресурсы. И мы не получим раннего завершения.
Никогда так не делайте!
Насколько бесполезен данный флаг? Я думаю, что в реальной жизни флаг 8671, (как и 8780, 8788 из первой части) бесполезен почти всегда, и привожу его здесь только в качестве демонстрации того, что отключение внутренних порогов — это не выход, и чтобы при желании вы могли в тестовой (!) среде проверить свой случай сами.
Отключение внутренних порогов это не решение
Вы могли подумать, что отключив возможность раннего завершения, а также таймаут — любой запрос всегда будет проходить все стадии преобразования и будут рассмотрены все альтернативы. Спешу огорчить, это не так.
Убедимся в этом на примерах.
Не все стадии
Полная оптимизация может быть разбита на три стадии:
— Search 0
— Search 1 (Search 1 parallel)
— Search 2
Давайте выполним два запроса. Оба запроса с «отключением» внутренних порогов и посмотрим, какие стадии проходит каждый запрос.
-- 4 tables select * from t1 join t1 t2 on t1.a = t2.a join t1 t3 on t1.a = t3.a join t1 t4 on t1.a = t4.a option( recompile ,querytraceon 3604 ,querytraceon 8675 ,querytraceon 8780 ,querytraceon 8671 ); -- 5 tables select * from t1 join t1 t2 on t1.a = t2.a join t1 t3 on t1.a = t3.a join t1 t4 on t1.a = t4.a join t1 t5 on t1.a = t5.a option( recompile ,querytraceon 3604 ,querytraceon 8675 ,querytraceon 8780 ,querytraceon 8671 );
Результат на вкладке Messages:
End of simplification, time: 0 net: 0 total: 0 net: 0 end exploration, tasks: 98 no total cost time: 0.003 net: 0.003 total: 0.003 net: 0.003 end search(0), cost: 0.414214 tasks: 245 time: 0 net: 0 total: 0.004 net: 0.004 end exploration, tasks: 1231 Cost = 0.414214 time: 0.003 net: 0.003 total: 0.008 net: 0.008 end search(1), cost: 0.0915104 tasks: 2443 time: 0.006 net: 0.006 total: 0.015 net: 0.015 End of post optimization rewrite, time: 0 net: 0 total: 0.015 net: 0.015 End of query plan compilation, time: 0 net: 0 total: 0.015 net: 0.015 End of simplification, time: 0 net: 0 total: 0 net: 0 end exploration, tasks: 126 no total cost time: 0.005 net: 0.005 total: 0.006 net: 0.006 end search(0), cost: 0.623234 tasks: 310 time: 0 net: 0 total: 0.007 net: 0.007 end exploration, tasks: 1327 Cost = 0.623234 time: 0.003 net: 0.003 total: 0.011 net: 0.011 end search(1), cost: 0.116833 tasks: 2582 time: 0.005 net: 0.005 total: 0.017 net: 0.017 end exploration, tasks: 4485 Cost = 0.116833 time: 0.008 net: 0.008 total: 0.026 net: 0.026 end search(2), cost: 0.116833 tasks: 6542 time: 0.01 net: 0.01 total: 0.037 net: 0.037 End of post optimization rewrite, time: 0 net: 0 total: 0.037 net: 0.037 End of query plan compilation, time: 0 net: 0 total: 0.038 net: 0.038
Как видно из результатов, оба запроса хотя и были выполнены с одинаковыми флагами трассировки, но один закончился на стадии search 1, другой на стадии search 2. Дело в том, что кроме отключения пороговых значений, необходимо, чтобы запросы удовлетворяли некоторым условиям для перехода на следующую стадию оптимизации. В данном случае одним из входных достаточных условий является то, что в запросе соединяется 5 или более таблиц (разумеется, это не единственное условие, и, кроме того, условия могут меняться от версии к версии).
Учитываются не все альтернативы
Возможно, вы могли подумать, что оптимизатор, при отключенных границах рассматривает все варианты и способы выполнить запрос. И когда внутренние границы включены оптимизатор просто не успевает «дойти» до этих вариантов. Но на самом деле, это работает не так.
Выполним простой запрос:
select *,(select 1) from t1 option( recompile );
Примечание: Подзапрос (select 1), нужно чтобы предотвратить тривиальный план, можно было бы использовать недокументированный флаг 8757, который предотвращает тривиальный план, и о котором я писал в заметке Оптимизатор (ч.2): Optimization: Trivial Plan Optimization, но чтобы максимально исключить влияние недокументированных флагов не относящихся к заметке, я решил воспользоваться подзапросом.
Просмотрев план запроса мы увидим, что причина прекращения оптимизации то, что был найден достаточно хороший план:
Рассмотрим альтернативы. Учитывая что у нас есть индекс по колонке b (если посмотреть на генерацию БД opt там будет строчка «create index ix_b on dbo.t1(b);»), то есть как минимум 5 способов выполнить этот запрос.
1 — выполнить сканирование таблицы;
2 — выполнить сканирование кластерного индекса (что в данном случае тоже самое, что и пункт 1);
3 — выполнить сканирование НЕ кластерного индекса ix_b и «подтянуть» при помощи поиска по кластерному индексу (lookup) недостающие данные.
4 — выполнить поиск по кластерному индексу.
5 — выполнить поиск по НЕ кластерному индексу ix_b и «подтянуть» данные из кластерного.
Давайте выполним запрос, отключив два первых способа.
За генерацию этих альтернатив отвечают два правила:
- GetToScan — Преобразовать оператор Get Table в Scan Table;
- GetToIdxScan— Преобразовать оператор Get Table в Scan Index;
Для отключения я буду использовать недокументированный хинт queryruleoff, о котором рассказывал в заметке Оптимизатор (недокументированное): Отключить правила преобразования в отдельном запросе
select *,(select 1) from t1
option(
recompile
,queryruleoff GetToIdxScan
,queryruleoff GetToScan
);
Результат:
Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
Итак, отлично. Запрос не смог выполнится, т.к. правила отключены. Что можно с этим поделать в контексте рассматриваемого вопроса?
Ответ, который может напрашиваться это: Конечно же, заставить оптимизатор исследовать дальше отключив timeout и pruning, ведь оптимизация прекратилась по причине Good Enough Plan. По этому, может быть при более глубокой оптимизации, оптимизатор все-таки сможет найти варианты с индексом ix_b (номер 3 и 5).
Это не верный вывод.
Давайте убедимся, выполнив запрос с флагами трассировки отключающими timeout и pruning.
select *,(select 1) from t1 option( recompile ,queryruleoff GetToIdxScan ,queryruleoff GetToScan ,querytraceon 8780 ,querytraceon 8671 );
Результат такой же:
Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
Почему? Вспомним теорию из первой части:
Важный момент: правила применяются не все подряд. А только те, что соответствуют некоторому шаблону для конкретного выражения группы (оператора).
….
В данном случае, просто НЕТ такого «паттерна» к которому можно было бы применить правило SelToIdxStrategy (отвечающее за поиск по индексу или просмотр с фильтрацией). Просто в дереве нет логического оператора LogOp_Select.
Примечание:
Select в данном случае означает «выбрать из», он не является аналогом select из запроса. Он относится к выражению where.
Давайте добавим в запрос условие «where», которое сравнивает колонку с самой собой where t1.b = t1.b, т.е. фактически бесполезно, так как колонка равна сама себе. И, поскольку оптимизатор умеет определять такие избыточные (redundant) проверки, добавим над колонкой выражение, например where t1.b+1 = t1.b+1 — иначе оптимизатор исключит избыточное условие на предварительном этапе, до того как дело дойдет до самой оптимизации.
Выполним:
select *,(select 1) from t1 where t1.b+1 = t1.b+1 option( recompile ,queryruleoff GetToIdxScan ,queryruleoff GetToScan );
И в результате мы получим план!
Т.е. логически тот же самый запрос, выполнился при отключенных правилах. И даже без дополнительных флагов включающих более глубокую оптимизацию!
А все потому, что мы написали такой код, к которому оптимизатор смог применить соответствующие правила. Если вам интересно, то отключите в этом запросе правило SelToIdxStrategy (queryruleoff SelToIdxStrategy), и увидите, что теперь оптимизатор опять не может построить план.
Из всего вышесказанного можно сделать очень простой вывод, ради которого можно было бы и не писать столько букв.
Вывод
Если код написан плохо, если дизайн БД плохой — вряд ли вас спасет увеличенный таймаут или отказ от прекращения оптимизации на ранней стадии.
Используйте best practices, пишите код «с оглядкой» на производительность.
Пожалуйста:
Не отключайте внутренние границы оптимизатора, это не может быть решением ни по каким причинам.
