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

Twitter RSS
Home SQL Server (все заметки) Оптимизатор без границ (ч.2)
formats

Оптимизатор без границ (ч.2)


Продолжаем отключать внутренние пороги оптимизатора.

В первой части были приведены общие теоретические сведения (на которые я буду ссылаться, по этому, рекомендую их просмотреть, если еще не успели), а так же представлен флаг трассировки 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.
    И выполнить его со включенным флагом, и без — то разница во времени компиляции станет заметна невооруженным глазом.

    Spoiler:

    set showplan_xml on
    go
    declare @v1 int,@v2 int,@v3 int,@v4 int,@v5 int,@v6 int,@v7 int,@v8 int;
    with cte as(select t1.* from t1 join t2 on t1.a = t2.b where t1.b > 1)
    select 
    	@v1=t1.a, @v2=t1.b, @v3=t1.c,
    	@v4=cte.b, @v5=cte2.c
    from 
    	t1 
    	join cte on t1.a = cte.b 
    	join cte cte2 on t1.c = cte2.b 
    	join cte cte3 on t1.c = cte3.b
    	join cte cte4 on t1.c = cte4.b	
    	join cte cte5 on t1.c = cte5.b
    	join cte cte6 on t1.c = cte6.b
    option 
    (
    	recompile
    	,querytraceon 3604
    	,querytraceon 8675
    	,querytraceon 8780
    )
    go
    declare @v1 int,@v2 int,@v3 int,@v4 int,@v5 int,@v6 int,@v7 int,@v8 int;
    with cte as(select t1.* from t1 join t2 on t1.a = t2.b where t1.b > 1)
    select 
    	@v1=t1.a, @v2=t1.b, @v3=t1.c,
    	@v4=cte.b, @v5=cte2.c
    from 
    	t1 
    	join cte on t1.a = cte.b 
    	join cte cte2 on t1.c = cte2.b 
    	join cte cte3 on t1.c = cte3.b
    	join cte cte4 on t1.c = cte4.b	
    	join cte cte5 on t1.c = cte5.b
    	join cte cte6 on t1.c = cte6.b
    option 
    (
    	recompile
    	,querytraceon 3604
    	,querytraceon 8675
    	,querytraceon 8780
    	,querytraceon 8671
    )
    go
    set showplan_xml off
    go

    Посмотрим на планы:

    Как мы видим, стоимость планов абсолютно одинаковая (планы не отличаются), только время компиляции у первого плана 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, пишите код «с оглядкой» на производительность.
    Пожалуйста:

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

    Spoiler for Дополнение для внимательных =)

    Если вы следили за ходом рассуждений, то наверняка заметили, что правило GetToIdxScan было отключено, однако, в плане мы видим Index Scan ix_b. Почему?
    Для ответа на вопрос, выполним приведенный выше запрос вместе с запросом в котором нет отключенных правил, а просто форсируется индекс ix_b.

    --index scan + predicate
    select *,(select 1) from t1 where t1.b+1 = t1.b+1
    option(
    	recompile
        ,queryruleoff GetToIdxScan
        ,queryruleoff GetToScan
    );
    --index scan + no predicate
    select *,(select 1) from t1 with(index(ix_b))
    option(
    	recompile
    );

    Посмотрим на планы:

    Уже по стоимости видно, что хотя планы имеют одинаковую форму, они отличаются.
    Если посмотреть свойства Index Scan ix_b, то будет видно в чем отличие:

    Как видно из свойств, очень отличается оценка. В 10 раз.
    И в случае когда у нас есть условие where, у нас появляется предикат, который как раз дает такую оценку.
    По сути, внутри, в первом случае при помощи правила SelToIdxStrategy, оператор LogOp_Get преобразуется в PhyOp_Filter + PhyOp_Range. Во втором случае, когда предиката нет, просто PhyOp_Range.

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

    --cheating optimizer
    update statistics t1(ix_b)  with rowcount = 10000, pagecount = 100
    go
    --query
    set showplan_xml on
    go
    select *,(select 1) from t1 where t1.b+1 = t1.b+1
    option(
        recompile
        ,queryruleoff GetToIdxScan
        ,queryruleoff GetToScan 
    );
    go
    set showplan_xml off
    go
    --returning back
    alter index ix_b on t1 rebuild;
    go
    

    План:

    Как видно, он даже похож по форме на тот план, что строится по-умолчанию, при включенных правилах для запроса «select *,(select 1) from t1».

 

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Анти-спам: введите результат (цифрами) *