Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
o-o
Guest
приглашаются к обсуждению все желающие,
но основная надежда все же на SomewhereSomehow.
прилагаю актуальный план.
сервер был пойман на спилле в темпдб,
спрашивается, зачем он сортирует такой объем,
когда сам же оценивает план с насильным хэшем как более дешевый?
кластерный по num_prat в tab_procedure_t не предлагать.
вопрос в другом, почему merge?
+ statistics time, io

Warning: The join order has been enforced because a local join hint is used.
Table 'TAB_PROCEDURE_T'. Scan count 9, logical reads 3534693, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TAB_TABELLONE_DATI_AGGIUNTIVI_T_day'. Scan count 9, logical reads 137616, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 155434, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1903553 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 39484 ms, elapsed time = 47835 ms.
Table 'TAB_TABELLONE_DATI_AGGIUNTIVI_T_day'. Scan count 9, logical reads 137792, physical reads 0, read-ahead reads 10, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TAB_PROCEDURE_T'. Scan count 9, logical reads 3534693, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1903553 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 36048 ms, elapsed time = 14477 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.


К сообщению приложен файл (merge_hash_carla_actual.zip - 31Kb) cкачать
3 май 17, 12:31    [20452723]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
o-o,

Приветствую. К сожалению, ничем новым вас не порадую.

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

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

Можно, ради эксперимента, убрать хинт hash, добавить TF: option(querytraceon 8780, querytraceon 8671) – и посмотреть, «дойдет» ли оптимизатор до плана с hash своим путем, без явного указания оного, если у него будут отключены пороги по раннему завершению оптимизации.
3 май 17, 13:37    [20452941]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
o-o
Guest
Спасибо, завтра же с флагом попробую, сегодня уже не там :(
Удивило, что перебирать-то особо нЕчего, и времени у него было завались, и вот так нехорошо выбрал.
Это у них уже второй сервер так делает: 2008ой постоянно выбирал NL, этот 2012 и ему нравится merge.
А наши объемы требуют hash, и у всех-то на него аллергия..
3 май 17, 14:18    [20453138]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
o-o
Guest
флаги ему не помогли, merge join, и гигабайт снова в темпдб слил

К сообщению приложен файл (merge_hash_carla_actual_TF.zip - 17Kb) cкачать
4 май 17, 10:25    [20455680]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7754
o-o,

подозреваю, что дело в оценках на затраты слияния, а не на затраты по плану в целом.
4 май 17, 10:58    [20455829]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
o-o,

Интересно, что в этом плане присутствует Row Count Top, который обычно появляется, если уровень совместимости БД выставить меньше 2012, в первых планах такого не было. Это флаги так повлияли или уровень совместимости менялся?

Возвращаясь к флагам, я написал, предполагаю, а не то, что они точно помогут. Это первый и самый простой эксперимент который бы я сам сделал если бы у меня под рукой была эта БД. Флаги не повлияли, значит нужно смотреть дальше, для этого желательно иметь репро, если у вас уже есть репро, которым вы можете поделиться, это упростило бы задачу. В ближайшие дни, если будет время, вернусь к этому вопросу.
4 май 17, 11:42    [20456123]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
o-o
Guest
SomewhereSomehow
o-o,
Интересно, что в этом плане присутствует Row Count Top, который обычно появляется, если уровень совместимости БД выставить меньше 2012, в первых планах такого не было. Это флаги так повлияли или уровень совместимости менялся?

нет, уровень совместимости не меняли.
просто недавно мигрировали и у всех пользовательских остался 100,
а в мастере само собой 110.
и вот только вчера дошли руки в студии выставить дефолтную базу для коннекта пользовательскую,
так что вчерашний скрипт выполнялся из мастера, а сегодня из моей базу.
у базы же, из которой читаю, ничего не менялось, все время 100.
давайте сейчас из мастера выполню
---
а про репро даже не знаю, таблицы здоровые, их не заскриптовать инсертами,
я могу по 100 строк оттуда скопировать в другие 2 таблицы и проверить, что за план будет,
но не факт, что воспроизведется.
если да то ради бога, скрипт инсертов отдам
4 май 17, 11:52    [20456195]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
o-o
Guest
ну вот все то же самое без топа ибо из мастера (110)

К сообщению приложен файл (merge_hash_carla_actual_TF_110.zip - 16Kb) cкачать
4 май 17, 12:04    [20456263]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
o-o,

Не думаю, что дело в top, но на всякий случай, решил уточнить, под каким уровнем совместимости все-таки выполняется.

Жаль, что у вас не 2016 SP1 или 2014 SP2, там как раз для таких случаев DBCC CLONEDATABASE предусмотрен. Копирует только метаданные без самих данных, как раз то что нужно для подобных случаев.

По идее, можно руками все это копировать или скрипт сделать. Скрипт создания таблиц, индексов и статистики + для каждой статистики ее бинарники "dbcc show_statistics(таблица,статистика) with stats_stream;" - этого должно хватить, чтобы воспроизвести компиляцию плана.
4 май 17, 12:11    [20456304]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
o-o
Guest
перечислите скрипт чего нужен, я заскриптую.
но думаю, все дело в данных, ибо на неполных не воспроизводится:
выцепляю 100 строк одной таблицы и все ей соответствующие из второй, их 1609.
и что, теперь у него merge дешевле, чем hash.
4 май 17, 12:23    [20456375]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
o-o,

автор
создания таблиц, индексов и статистики + для каждой статистики ее бинарники "dbcc show_statistics(таблица,статистика) with stats_stream;"

Сами данные не нужны, нужны метаданные (две таблицы, индексы по ним и статистики по ним) и бинарные данные статистик (они в себе содержат копию объекта статистики, заголовок, гистограмму и плотности).
Вот тут я когда-то накидывал подобие скрипта, который собирает данные оп статистике 14461240, можете его взять как основу и модифицировать под себя.
4 май 17, 12:33    [20456423]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
o-o
Guest


К сообщению приложен файл (SS.z01 - 130Kb) cкачать
4 май 17, 13:12    [20456661]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
o-o
Guest


К сообщению приложен файл (SS.zip - 116Kb) cкачать
4 май 17, 13:12    [20456666]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
o-o,
Спасибо за репро.

Не все с ним прошло гладко, для некоторых статистик обновление прошло с ошибкой: «Msg 9105, Level 16, State 106, Line 301 The provided statistics stream is corrupt.»

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

Картинка с другого сайта.

По умолчанию, без хинта, выбирается тоже первый вариант с Merge Join (стоимость поддерева плана с соединением 2791.17), который дороже второго с Hash Match (2777.07).

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

Этой развилкой стал порядок соединений.

Если из запроса убрать хинт INNER HASH JOIN, форсирующий не только hash, но и порядок соединений и заменить его на OPTION (FORCEORDER), то я получаю тот же (второй на картинке) план в котором без всякого указания hash, этот тип (hash) выбран автоматически.

И наоборот, если вместо хинта INNER HASH JOIN использовать OPTION (HASH JOIN), то план с hash join становится дороже (2838.38), чем с merge join и имеет другой порядок соединений.

Картинка с другого сайта.

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

Картинка с другого сайта.

Смотрим снизу-вверх.

0. Сначала присутствует только логическое соединение таблиц (LogOp_Join), соединяются группы 102 (соответствует таблице TAB_TABELLONE_DATI_AGGIUNTIVI_T_day) и 107 (соответствует таблице TAB_PROCEDURE_T) (110 это группа проекции, она нам тут не важна) - "102 join 107".

1. Далее применяется правило JoinCommute и, т.к. для логического внутреннего соединения все равно как соединять (свойство коммутативности), в группу записывается альтернатива "107 join 102".

2-7. Применяются правила на стадии Search 1.

Нас интересуют финальная стадия Search 1 Parallel, а именно операторы Merge и Hash, пункты 9, 10 и 11.

9. Берем логическую альтернативу из п.1 (107 join 102). В таком случае, получаем Many-to-Many Merge Join (9 PhyOp_MergeJoin M-M x_jtInner Cost = 2802.16) т.к. на таблице TAB_PROCEDURE_T по полю NUM_PRAT видимо нет ограничения уникальности (не кластерных индексов не было в репро, так что это я предполагаю), в отличие от другой таблицы, где по этому полю кластерный PK.

10. Так что оптимизатор берет другую логическую альтернативу из п.0 (102 join 107), где таблицы имеют другой входной порядок для соединения, тут уже все нормально, Merge Join One-to-Many (10 PhyOp_MergeJoin 1-M x_jtInner Cost = 2791.17) он получается дешевле. Можно обратить внимание, что значение 2791.17 мы и видим в первом плане.

11. Далее рассматривает Hash Join. Опять берет группу из п.1 (107 join 102), получает дорогой Hash Join (11 PhyOp_HashJoinx_jtInner Cost = 2838.38), поскольку таблица 107 (TAB_PROCEDURE_T) очень объемная, использовать ее на вход как Build таблицу – требует много памяти, что косвенно отражается в стоимости. Опять же, можно обратить внимание, что это ровна та стоимость, когда мы ограничили только тип, но не порядок соединения hash join-ом в третьем плане.

И вот тут происходит провал, он же фэйл.

На следующем этапе, было бы логично опять взять альтернативу из п.0 (102 join 107), с исходным порядком таблиц и оценить hash для нее (как в случае с merge), но этого не происходит. Вместо этого, далее оценивается следующий физический тип Loop Join, который оказывается также дороже и всё.

Далее выбирается самый дешевый найденный в этой группе, т.е. merge one-to-many (с порядком таблиц из логической альтернативы номер 0), он и используется в плане, что мы наблюдаем.

У меня есть два предположения, почему так может быть (не рассматривается ветка с другим порядком таблиц в hash). Либо это какой-то дефект основного алгоритма оптимизации - тогда надо заводить на коннект, либо это особенность эвристического алгоритма оценки выгоды и поиска пути, который, не является гарантированно точным и оптимальным. Например, такая особенность, как возможность или не возможность применения правила на основе некоторого обещания выгоды (promise). Возможно, что для данного случая, обещания выгоды от исследования ветви плана с Hash Match с другим порядком таблиц получилось ниже, чем использование уже имеющейся ветви с Merge.

Я больше склоняюсь к последнему, т.к. разница в стоимости, что в вашем плане, что в моем репро, очень невелика, вероятно, заложенная эвристика в ожидании выгоды не перекрыла уже имеющуюся лучшую стоимость. И правда, если бы не неудачная оценка Sort, и не spill (т.е. все было близко к тому, как оптимизатор предполагал, когда строил план), я думаю, на такое бы вряд ли вообще обратили внимание (в последних версиях появился хинт MIN_GRANT_PERCENT, задав ему нужный грант, можно было бы быстро, в качестве эксперимента, проверить, как бы запрос выполняется, если бы памяти было достаточно и spill не происходил и проверить, насколько медленнее в реальности merge без spill-а).

Можно попробовать побороться с оценками, может если бы оптимизатор понял, что в сортировке реально 1 903 578 строк, а не 623 932, то он бы все-таки исследовал ту ветку с Hash. Я не могу строить разные индексы и статистики на пустых таблицах, так что эксперименты сильно ограничены, но если я увеличиваю число потенциальных строк в 6 раз (с 38832585 до 188832585), чтобы на выходе из скана у меня получалось около 2 млн, то я таки получаю план той формы и с тем порядком таблиц, как с картинки номер 2, без всяких хинтов.

Картинка с другого сайта.

Так что может имеет смысл поколдовать с оценками.

Ради интереса, проверил это в 2017 сервере. Под уровнем 110 – все так же, как и в 2012. Под уровнем 120-140 план ближе к форме номер 3, но, конечно, с другими оценками.

Чем мог.
4 май 17, 22:56    [20458644]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
o-o
Guest
спасибо за проделанную работу и объяснения.
вот что интересно:
у нас все еще жив 2008-ой сервер, где данные примерно те же (остановлены на 24/05/2017)
и вот на нем без хинтов выбирается хэш, причем порядок соединения тот самый.
а вот с хинтом порядок другой, план дороже

К сообщению приложен файл (for_SS.zip - 21Kb) cкачать
5 май 17, 09:40    [20459310]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
o-o
Guest
еще вопрос, что значит, что обновление статистик произошло с ошибками?
с чем это может быть связано?
просто у нас новый ДБА, еще круче немцев, чекдб теперь не делается вообще.
могу только сказать, что в еррорлоге нет ошибок типа 824, 825, а за остальное не ручаюсь.

собрать ли все то же по статистикам тем же курсором, что и вчера, но на 2008-ом сервере?
---
в TAB_PROCEDURE_T действительно неуникальный NUM_PRAT,
т.е. это так и задумано, одному NUM_PRAT из TAB_TABELLONE_DATI_AGGIUNTIVI_T_day
соответствует несколько строк в процедурах.
индексов на TAB_TABELLONE_DATI_AGGIUNTIVI_T_day кроме ПК нет.
а вот на TAB_PROCEDURE_T есть,
они не заскриптовались вчера, т.к. у меня в новой студии это не было насторено (уже да)
могу все это сегодня выложить, если интересно
5 май 17, 09:58    [20459406]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
o-o
спасибо за проделанную работу и объяснения.
вот что интересно:
у нас все еще жив 2008-ой сервер, где данные примерно те же (остановлены на 24/05/2017)
и вот на нем без хинтов выбирается хэш, причем порядок соединения тот самый.
а вот с хинтом порядок другой, план дороже


Судя по плану, что вы приложили без хинтов - выбирается план формы номер три из предыдущего ответа (с "неудачным" порядком), который в данном случае дешевле, а с хинтом выбирается план формы номер два (с "удачным" порядком), который в данном случае оказывается дороже.

Стоимость тонкая вещь, например, в оценке операторов потребляющих память участвует конфигурация оборудования, такая как число доступной памяти. А в параллельных планах участвует еще и число CPU. Например, на моей домашней машине 14 ГБ ОЗУ и 4 ядра. На такой конфигурации, тот пример что я расписывал выше на репро базе без хинтов дает план формы номер три, т.е. hash с "неудачным" порядком, а не merge с другим порядком.
Так что, чтобы максимально приблизить условия к тем, что есть у вас и поставить оптимизатор в одинаковые условия, перед тем как скомпилировать план, я выполнил команду:
dbcc optimizer_whatif('CPUs', 8) 
dbcc optimizer_whatif('MemoryMBs', 64000)

Чтобы оптимизатор считал, что имеет дело с восьмиядерным сервером на котором 64 ГБ оперативки. Если установить например 48 ГБ, то уже выбирается hash. И Sort и Hash потребляют память, но при бОльшем числе памяти Sort оказывается дешевле и план приходит к Merge, а альтернативная ветка к сожалению не исследуется по причинам приведенным выше. Если конфигурация оборудования на 2008 сервере отличается от 2012, то причина может быть в этом. Можете это проверить на 2008, если это тестовый сервер и там можно запускать недокументированные команды, насколько я помню эти значения устанавливаются в рамках сессии, но на всякий случай, не забудьте потом вернуть к старым значениям - dbcc optimizer_whatif('ResetAll').

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

o-o
еще вопрос, что значит, что обновление статистик произошло с ошибками?
с чем это может быть связано?

Скорее всего с тем, что я создавал статистики руками по вашему файлу (в репро скриптов на их создание не было) и потом накатывал туда гистограммы, может быть какие-то еще опции нужно было учесть при создании статистики, может и скрипт где-то косячит, я его на коленке писал и толком не тестировал (то ли нельзя было тогда из студии гистограммы сделать, то ли я не знал, то ли не все нужно было). Можно и без всякого скрипта копировать статистики. Может тогда бы не было ошибок.

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

К сообщению приложен файл. Размер - 35Kb
5 май 17, 11:22    [20459835]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
o-o
Guest
конфигурация отличается вот в этом.
2008 R2:
max server memory (MB) 36864
max degree of parallelism 4
---------------------------------------
max server memory (MB) 53248
max degree of parallelism 8

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

К сообщению приложен файл. Размер - 12Kb
5 май 17, 11:50    [20459982]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
o-o
Guest
dbcc optimizer_whatif на него не подействовал,
merge он не выбрал.
план вообще тот же остался.
и с 53248, и с 64000.
-----
ну и ладно.
спиллы я все равно теперь легко нахожу.
у нового дба хобби -- каждый день шринкует темпдб по несколько раз в день,
все добивается 1Гб.
ну что, молодец, каждый спилл расширяет темпдб обратно, в еррорлог сыпется
SQL Server has encountered 186 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\SQLTEMP\Microsoft SQL Server\MSSQL11.DZ3\MSSQL\Data\tempdev5.ndf] in database [tempdb] (2).  
The OS file handle is 0x0000000000000E34.  The offset of the latest long I/O is: 0x00000093870000

с указанием точного времени.
а процедуры логируют каждую инструкцию.
вот и нахожу

Сообщение было отредактировано: 5 май 17, 12:17
5 май 17, 12:05    [20460063]     Ответить | Цитировать Сообщить модератору
 Re: to SomewhereSomehow: добровольный выбор более дорогого плана  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
o-o
dbcc optimizer_whatif на него не подействовал,
merge он не выбрал.
план вообще тот же остался.
и с 53248, и с 64000.

optimizer_whatif моделирует аппаратную часть, если у вас память ограничена ниже 64 настройкой, то он будет использовать последнее (когда это репро крутил, я забыл, что выставил всем инстансам на своей машине не более 1 ГБ, пока не снял не взлетело).

В общем, я сказал куда можно смотреть. Можете как ориентир использовать свойства OptimizerHardwareDependantProperties в корневом элементе плана (не помню, есть ли они в 2008), нужно добиться, чтобы они были одинаковыми на разных серверах, для воспроизводимости ситуации. Если это не поможет, значит нужно смотреть насколько отличаются статистики. Конечно, 2008 и 2012 имеют много различий в оптимизаторе, но не много в плане основного алгоритма поиска плана, так что не думаю, что это регресс 2012 по сравнению с 2008.
5 май 17, 15:17    [20461056]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить