Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 изменение плана со временем  [new]
план-аэроплан
Guest
Вот ситуация. Есть отчет, там большой запрос. Все сначала работает относительно быстро. Вся статистика обновляется каждую ночь.

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

Вот что именно происходит, что "план ломается"? Что заставляет сервер менять план?
27 сен 13, 17:11    [14894306]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
Crimean
Member

Откуда:
Сообщений: 13148
"отчет" = хранимка, небось?
28 сен 13, 20:23    [14897058]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
план-аэроплан
Guest
Crimean
"отчет" = хранимка, небось?
да, точно
28 сен 13, 20:49    [14897179]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
ziktuw
Member

Откуда:
Сообщений: 3552
Зихинтуй запрос, и план будет всегда тот, который тебе нужен
28 сен 13, 21:45    [14897446]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
Exproment
Member

Откуда:
Сообщений: 416
Глеб, вроде разговор идет о том, почему так происходит, а не как с этим бороться. Какие стандатные причины таких проблем ? Сам такое видел много раз.
28 сен 13, 21:51    [14897465]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
ziktuw
Member

Откуда:
Сообщений: 3552
IMHO: за отведенный квант времени оптимизатор не успевает выбрать наиболее оптимальный план и использует с наименьшей стоимостью из перебранных им вариантов, либо тупо ошибается в оценке оптимальности, что бывает на сложных или многошаговых запросах часто. А пересматривать план для скомпиленного запроса (процедуры) его заставляет банальная причина - статистики изменились.
28 сен 13, 21:59    [14897493]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
Exproment
Member

Откуда:
Сообщений: 416
Глеб, тогда почему та-же самая перекомпиляция плана помогает решить проблему ? Причем в 90% случаев, если слатал план, то перекомпилировал и все норм.
28 сен 13, 22:41    [14897679]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Глеб
Зихинтуй запрос, и план будет всегда тот, который тебе нужен


это если распределение данных для параметров построение постоянное )
а иначе регулярно будут проблемы
если грубо - то option( recompile )
а если точно - надо смотреть хороший и плохой планы и вообще смотреть на отчет
29 сен 13, 20:44    [14898960]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34621
Exproment
Глеб, вроде разговор идет о том, почему так происходит, а не как с этим бороться. Какие стандатные причины таких проблем ? Сам такое видел много раз.



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

А лечить — хинтами.
29 сен 13, 23:07    [14899223]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
Exproment
Member

Откуда:
Сообщений: 416
MasterZiv, была на прошлой работе процедура, план которой слетал раз в месяц. Как только данная операция начинала тормозить - сразу писали в IT и процу перекомпилировали. При этом каждый раз это помогало. Порой с момента падения плана проходило не более минуты, как его перекомпилировали руками, т.е. общее состояние базы за это время поменяться не могло(ну не каждый раз ведь). К сожалению плохой план никто не зафиксировал и в итоге решили проблему хинтами.

Единственная причина, которую тут смог предположить, что автоматическая перекомпиляция как-то отличается от перекомпиляции руками. При разовом падении плана, можно сослаться на "оптимизатор затупил", но если это происходит регулярно...
30 сен 13, 00:19    [14899343]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34621
MasterZiv, была на прошлой работе процедура, план которой слетал раз в месяц.

Охотно верю...

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


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


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

Могут быть и другие сценарии.
30 сен 13, 10:39    [14899992]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
план-аэроплан
Guest
MasterZiv
... Это очень долго рассказывать и достаточно трудно понять, почему именно так. Очень много причин, и вовсе ее потому, что в развитое баги и он недоделанный. В двух словах — потому что оптимизатор — это очень сложная штука. ...
Ну необязательно рассказывать. Ссылки на статьи можете кинуть?
30 сен 13, 10:40    [14900000]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34621
план-аэроплан
MasterZiv
... Это очень долго рассказывать и достаточно трудно понять, почему именно так. Очень много причин, и вовсе ее потому, что в развитое баги и он недоделанный. В двух словах — потому что оптимизатор — это очень сложная штука. ...
Ну необязательно рассказывать. Ссылки на статьи можете кинуть?


Я такую статью ещё пока не написал...
Напишу -- тогда может быть смогу кинуть.
30 сен 13, 10:44    [14900012]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
план-аэроплан
Guest
MasterZiv
план-аэроплан
пропущено...
Ну необязательно рассказывать. Ссылки на статьи можете кинуть?


Я такую статью ещё пока не написал...
Напишу -- тогда может быть смогу кинуть.
имел в виду статьи не только вашего авторства.
30 сен 13, 10:50    [14900035]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
лан-аэроплан
Guest
MasterZiv
... Нет, причина такого поведения чаще всего -- вытеснение процедуры из процедурного кэша и затем при след. выполнении помещение обратно в кэш (т.е. оптимизация запросов) под другие значения параметров процедуры. ...
уточнить, план существует только в кэше?
30 сен 13, 11:04    [14900112]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
aleks2
Guest
план-аэроплан
MasterZiv
пропущено...


Я такую статью ещё пока не написал...
Напишу -- тогда может быть смогу кинуть.
имел в виду статьи не только вашего авторства.


Случайные колебания плана можно объяснить "случайной загрузкой процессора".
Оптимизатор (в теории) опирается на ограничение в форме "надо найти план за N (милли)секунд",
если оптимальный план находится в конце перебираемых вариантов,
то любая доп. нагрузка на сервер может приводить к "недоперебору" вариантов и, как результат, облому.

Вот и фся статья.
30 сен 13, 11:08    [14900136]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
Exproment
Member

Откуда:
Сообщений: 416
MasterZiv, вообще да.. если проца была перекомпилирована с некоторыми нестандартными параметрами, то для стандартных её выполнение может сильно отличаться. Как-то не подумал об этом :)
30 сен 13, 11:08    [14900138]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
план-аэроплан, если установлено, что план именно "ломается", то есть заменяется на худший, то можно попробовать отловить рекомпиляцию и найти её причину

http://support.microsoft.com/kb/308737/en-us
30 сен 13, 11:25    [14900262]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Таймаут оптимизатора измеряется не временем, а количеством задач оптимизации, которое предварительно отводит себе сервер для оптимизации запроса. Оно зависит не от загруженности процессора, а от "сложности" запроса - количества и свойств логических групп оптимизации (типа "я угадаю эту мелодию с пяти нот...", т.е. "я построю план этого запроса за столько-то шагов...").

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

В следующий раз, когда начнуться тормоза, зафиксируйте это на 5 минут, попросите никого ничего не трогать. Запустите профайлер и отловите вызов долгой процедуры, копируйте себе в SSMS текст вызова, где будут указаны все параметры, кроме того, скопируйте все настройки соединения (они тоже будут в профайлере, если выбирать шаблон по-умолчанию).
Типа такого должно быть.
+
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 1
set transaction isolation level read committed
go
exec dbo.MyProc 'param1'

Включите в студии Include Actual Execution Plan и выполните. Посмотрите на время, если будет долго, то вы поймали ситацию плюс у вас есть нэффективный действительный план. Сохраните его (на всякий случай) и можете анализировать (обратите внимание на "Parameter Compiled Value" и "Parameter Runtime Value"). Можно сразу в профайлере включить соотв.событие, чтобы увидеть план.
Теперь можете рекомпилировать процедуру, и выполнить запрос еще раз и снова посмотреть план. Сравнить, с тем, что был при прошлом, медленном выполнении.

Хорошие кандидаты на такие случаи - универсальные процедуры, в которых набор возвращаемых данных может сильно меняться в зависимости от значений параметров.
30 сен 13, 15:28    [14902203]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
aleks2
Guest
SomewhereSomehow
Таймаут оптимизатора измеряется не временем, а количеством задач оптимизации, которое предварительно отводит себе сервер для оптимизации запроса. Оно зависит не от загруженности процессора, а от "сложности" запроса - количества и свойств логических групп оптимизации (типа "я угадаю эту мелодию с пяти нот...", т.е. "я построю план этого запроса за столько-то шагов...").


Хе-хе. Может она и в штуках таймаут измеряет, но
http://mssqlwiki.com/2012/10/07/optimizer-timeout-or-optimizer-memory-abort/
"This is called optimizer timeout (based on the number of plans considered relative to the cost of the best plan so far)."

Как бе намекает, что не просто "штуки"... а этот хренов "cost of the best plan" может изменяться, ну и "штуки" с им.
1 окт 13, 05:36    [14904515]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
SomewhereSomehow
Member

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

Не понял, к чему это вы? Я разве говорил что он не изменяется на протяжении всего процесса оптимизации? Таймаут задает некоторый бюджет задач оптимизации, который оптимизатор расходует. Могу расписать подробнее, если интересно (хотя я вроде описывал у себя в блоге это уже, ну да ладно, "эх раз, еще раз").
Основные понятия
Memo — структура в памяти сервера, которая используется для хранения и анализа получаемых в результате преобразований деревьев операторов.
Group — группа эквивалентности, часть структуры Memo, в которой хранятся эквивалентные выражения (операторы), например — Group 1: (A join B) , (B join A).
Optimization Task — дословно, задача оптимизации, это операция предпринимаемая оптимизатором в процессе поиска плана. Это может быть например, применение правила преобразования к узлу дерева операторов.
Timeout — определенное количество задач оптимизации (Optimization Task), которое отводит себе оптимизатор перед тем, как начинает оптимизировать запрос, т.е. некий бюджет на количество преобразований. По мере выполнения преобразований оптимизатор смотрит на этот счетчик, и как только потратил всё отведенное количество — прекращает оптимизацию и выдает тот план, который у него есть на данный момент.
Процесс оптимизации
Оптимизация состоит из стадий:
- тривиальный план
- search 0
- search 1
- search 2
Каждая стадия характеризуется набором доступных операций оптимизации, входными условиями и т.д. От стадии к стадии набор приемов расширяется. Для отслеживания вариантов планов, на каждой стадии, оптимизатор заполняет структуру Memo в которой хранит группы эквивалентности.
Конечное Memo предыдущей стадии используется как начальное для следующей. И от стадии к стадии число групп растет, сложность растет - таймаут тоже растет. Более того Мемо ведет своеобразный треккинг "лучшего" плана и отметает неэффективные ветки, от этого в частности зависит и величина таймаута (это можно проверить трейсфлагом). Но тем не менее, если только таймаут не отключен (хотя его нельзя полностью отключить, можно только сильно увеличить) - он задает некоторый лимит операций, за который оптимизатор не может выйти на определенной стадии.

Если интересно, можно взять дебаггер, поставить брэйкпоинты на следующие методы:
CMemo::OptimizeQuery
CMemo::SetTimeout
CMemo::PerformOptimizationStage
CMemo::ReportEarlyAbort
CMemo::ExecuteTasks

После этого выполнить какой-нибудь запрос с таймаутом, (я взял пример из своего блога) и посмотреть, какие методы в какой последовательности вызываются.
+
use opt;
go
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 2372
)
go
set showplan_xml on
go

Результат:
Memory before NNFConvert: 22
Memory after NNFConvert: 23

Memory before project removal: 29
Memory after project removal: 31

Memory before simplification: 31
End of simplification, time: 0.003 net: 0.003 total: 0.003 net: 0.003
Memory after simplification: 106

Memory before heuristic join reordering: 106
Memory after heuristic join reordering: 165

Memory before project normalization: 165
Memory after project normalization: 165

Memory before stage TP: 167
end exploration, tasks: 919 no total cost time: 0.039 net: 0.039 total: 0.042 net: 0.042
end search(0),  cost: 5439.01 tasks: 1621 time: 0.005 net: 0.005 total: 0.048 net: 0.048
Memory after stage TP: 238

Memory before stage QuickPlan: 238
end exploration, tasks: 3627 Cost = 5439.01 time: 0.005 net: 0.005 total: 0.054 net: 0.054
end search(1),  cost: 5438.91 tasks: 4688 time: 0.01 net: 0.01 total: 0.065 net: 0.065
end exploration, tasks: 4689 Cost = 5438.91 time: 0 net: 0 total: 0.065 net: 0.065
Memory after stage QuickPlan: 296

Memory before stage Full: 296
*** Optimizer time out abort at task 614400 ***
Memory after stage Full: 13299
*** Optimizer time out abort at task 614400 ***

Memory before copy out: 13300
Memory after copy out: 13301

End of post optimization rewrite, time: 3.627 net: 3.625 total: 3.693 net: 3.691

End of query plan compilation, time: 0 net: 0 total: 3.694 net: 3.692

Т.е. было пройдено две стадии полной оптимизации search0 (также известная как Transaction Processing) и search1 (известная как Quick Plan), начата стадия search2 известная как просто Full, т.е. полная оптимизация, но прежде чем на ней был найден достаточно хороший план - получен получен таймаут, когда число преобразований достигло 614400.
Это отразилось в вызовах следующим образом (мои коменты зеленым):
+
Картинка с другого сайта.


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

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

В любом случае ведь проще сначала проверить распространенные причины, прежде чем лезть в дебри, имхо.
1 окт 13, 14:05    [14906989]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
SomewhereSomehow,
Запрос только не тот запостил, таблиц нужно больше 5, чтоб до стадии 2 (Full) дошел процесс.
Правильный запрос релевантный примеру.
+
with cte as(select t1.* from t1 join t2 on t1.a = t2.b where t1.b > 1)
select
	*
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
    join cte cte7 on t1.c = cte7.b  
option
(
    recompile
    ,querytraceon 3604
    ,querytraceon 8675
    ,querytraceon 2372
)
1 окт 13, 14:19    [14907100]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
aleks2
Guest
SomewhereSomehow
aleks2,
Не понял, к чему это вы?


1. Я к тому, что оптимизатор все ж, банально, перебирает разное число планов при перекомпиляциях.
2. И единственно правильный способ стабилизировать план - не давать оптимизатору никакого выбора.
3. А читать многабукав из твоих, возможно правильных, текстов - неразумно.
1 окт 13, 15:49    [14907788]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
aleks2
1. Я к тому, что оптимизатор все ж, банально, перебирает разное число планов при перекомпиляциях.
2. И единственно правильный способ стабилизировать план - не давать оптимизатору никакого выбора.
3. А читать многабукав из твоих, возможно правильных, текстов - неразумно.

Много букв это да, смысл в их написании тут - был простой, показать, что банальный перебор идет не от желания левой пятки оптимизатора, а по определенным алгоритмам, и таймаут зависит не от нагрузки.
Что касается стабильности - разное можно под этим понимать, можно стабильность (предсказуемость) выбора плана в зависимости от ситуации, имхо, в большинстве случаев это предпочтительнее, но может быть и некий усредненный план, пусть и не очень оптимальный для каждого конкретного случая, optimize for unknown не зря же придумали. Это смотря что надо ТС.
1 окт 13, 16:13    [14907996]     Ответить | Цитировать Сообщить модератору
 Re: изменение плана со временем  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SomewhereSomehow
Много букв это да, смысл в их написании тут - был простой, показать, что банальный перебор идет не от желания левой пятки оптимизатора, а по определенным алгоритмам, и таймаут зависит не от нагрузки.
Большой Вам респект за пример. Я всегда подозревал, что загрузка процессора ни коим образом не влияет на количество планов которые успеет перебрать оптимизатор, но все некогда было в этом покопаться.
1 окт 13, 20:16    [14909159]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить