| Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
| Все форумы / Microsoft SQL Server |
  |
||
 За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?
|
|
|
WinterGraveyard Member Откуда: Сообщений: 79 |
Есть некая хранимая процедура, внутри которой один-единственный запрос. Сам запрос представляет из себя кучу left join'ов, в плане исполнения - только nested loops, index seek+RID lookup, плюс на запрос повешен хинт OPTION(FAST 100). Не так давно было замечено, что выполнение этой процедуры сильно просело в производительности. Выход был неожиданным - OPTION(MAXDOP 1) (по совету сторонних специалистов). В профайлере по событию RPC:Completed видно, что время запроса после введения OPTION(MAXDOP 1) сократилось в ~10 раз, количество чтений с 5630122 упало до 1500, количество операций записи - с 29094 до 0, CPU - с 27094 до 47. Порылся по интернетам, прочитал про Wait type CXPACKET - неужели он может отбирать такое количество ресурсов? Можно ли этот момент отследить через события профайлера? Какие тут еще могут быть нюансы? Спасибо. |
| 7 июн 16, 15:36 [19267607] Ответить | Цитировать Сообщить модератору | |
| Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?
|
|
|
invm Member Откуда: Москва Сообщений: 9915 |
OPTION(MAXDOP 1) ни при чем. Вы поменяли текст процедуры, что вызвало ее перекомпиляцию с другим значениями параметров - соответственно, изменился план выполнения. |
| 7 июн 16, 15:45 [19267669] Ответить | Цитировать Сообщить модератору | |
| Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?
|
|
|
buser Member Откуда: Санкт-Петербург Сообщений: 4542 |
WinterGraveyard, https://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/ |
| 7 июн 16, 15:46 [19267673] Ответить | Цитировать Сообщить модератору | |
| Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?
|
|
|
o-o
Guest |
планы выкладывайте, может, вы в куче своих NL не заметили отличие планов. и вообще, когда "куча" джойнов, их разбивать надо. соединили 4-5 штук, сохраняете результат во времянку. --- может, MAXDOP 1 и вовсе ни при чем. если процедура с параметрами, изменение текста процедуры вынесло план из кэша. а первый запуск процедуры оказался с более "подходящим" параметром |
| 7 июн 16, 15:53 [19267718] Ответить | Цитировать Сообщить модератору | |
| Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?
|
|
|
Владислав Колосов Member Откуда: Сообщений: 9184 |
офф... Не бывает "намного более оптимально", бывает или оптимально или не оптимально. Оптимально - это максимальная оценка, по-русски "самый лучший". |
| 7 июн 16, 16:27 [19267890] Ответить | Цитировать Сообщить модератору | |
| Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?
|
|||||||||
|
WinterGraveyard Member Откуда: Сообщений: 79 |
Нет. Я подозревал такое поведение, поэтому эта опция добавлялась и удалялась несколько раз. Каждый раз после добавления снижались время выполнения и кол-во чтений, после удаления опции - возрастали.
Не выйдет. Процедура - не самописная, такие процедуры в большом количестве генерит ERP. Собственно, было вообще предложено выставить max degree of parallelism=1, я удивился такому кардинальному решению, и решил попробовать на одной процедуре (на всех существующих автосгенеренных это будет затруднительно ввиду их большого количества). Однако, эксперимент принес свои плоды.
да, с параметрами, про вынос из кэша написал выше.
А тут проблема в том, что в студии процедура что так, что так отрабатывает одинаково медленно - она возвращает очень большой объем данных. А вышеописанное поведение с OPTION(MAXDOP 1) наблюдается именно в клиентском приложении (несамописном), которое из всей выборки берет только 10 строк. Однако в профайлере плана этой процедуры нет, несмотря на выставленные ShowPlan XML и Shoplan XML statistics profile. SP:Starting/SP:Completed есть, Stmt:Starting/Stmt:Completed есть, RPC:Starting/PRC:Completed есть - а вот сообщений с планом нет. |
||||||||
| 7 июн 16, 17:54 [19268266] Ответить | Цитировать Сообщить модератору | |||||||||
| Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?
|
|
|
Владислав Колосов Member Откуда: Сообщений: 9184 |
WinterGraveyard, То, что упало количество чтений, означает использование другого плана запроса. Чудес не бывает. |
| 7 июн 16, 18:00 [19268298] Ответить | Цитировать Сообщить модератору | |
| Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?
|
|
|
TaPaK Member Откуда: Kiev Сообщений: 6802 |
Владислав Колосов, ну то что он другой может стать это без вопросов. Вообще были статьи по поводу того что отключение параллелизма может увеличить производительность запроса, ну и про то что это снизит нагрузку ЦП вообщем-то ясно |
| 7 июн 16, 18:06 [19268311] Ответить | Цитировать Сообщить модератору | |
| Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?
|
|
|
o-o
Guest |
Да мы переживем, что студия медленно отрабатывает и то, и это. Все равно план давайте, актуальный. Наверняка все ваши лупы при смене плана превращаются в hash join, вот и посмотрим, что за оценки и что там на самом деле. Короче, план в студию |
| 7 июн 16, 18:35 [19268376] Ответить | Цитировать Сообщить модератору | |
| Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?
|
|||
|
invm Member Откуда: Москва Сообщений: 9915 |
Выполните процедуру в студии в обоих вариантах, с одинаковыми параметрами и с set xml statistics on и выложите сюда полученные планы в формате sqlplan. |
||
| 7 июн 16, 21:52 [19268889] Ответить | Цитировать Сообщить модератору | |||
| Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?
|
|
|
WinterGraveyard Member Откуда: Сообщений: 79 |
Планы в аттаче (в архиве, т.к. очень большие). К сообщению приложен файл (maxdop.7z - 71Kb) cкачать 
|
| 8 июн 16, 07:29 [19269355] Ответить | Цитировать Сообщить модератору | |
| Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?
|
|||
|
invm Member Откуда: Москва Сообщений: 9915 |
И статистику неплохо бы обновить - оптимизатор в оценках сильно промахивается. |
||
| 8 июн 16, 10:29 [19269915] Ответить | Цитировать Сообщить модератору | |||
| Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?
|
|||
|
o-o
Guest |
он не то что промахивается. это ему выставили OPTION(FAST 100), чтобы он лупы запилил (Estimated number of rows = 100) ССЗБы |
||
| 8 июн 16, 10:52 [19270024] Ответить | Цитировать Сообщить модератору | |||
| Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?
|
|||
|
SomewhereSomehow Member Откуда: Moscow Сообщений: 2480 Блог |
WinterGraveyard, Добрый день.
Такое, в принципе, может происходить и maxdop может влиять на план даже если в итоге не выбирается параллельный план потому, что оптимизация запроса идет по стадиям. Результатом каждой стадии является наиболее дешевый план на этой стадии и поле альтернатив. Если самый дешевый план стадии не удовлетворяет порогу оптимизатора и стадия не последняя – оптимизатор переходит с следующей стадии подавая на вход следующей стадии то поле вариантов, которое он получил на предыдущей. Когда параллелизм не ограничен и возможен, стадии полной оптимизации идут так search0 -> search1 -> search1 parallel -> search2. Когда параллелизм ограничен - так: search0 -> search1 -> search2. План, найденный на самой последней стадии search2, зависит от того, какое поле вариантов у него было изначально на входе. Для ограниченного и не ограниченного вариантов оно разное и даже если в итоге выбирается не параллельный план, оно потенциально может отличаться. Я приводил этот пример в докладе Внутри оптимизатора запросов: параллелизм. Можете посмотреть на Channel 9, 30-ая минута. Это ответ почему такое в принципе возможно. Но в вашем случае, я не думаю, что дело в этом. Во-первых, посмотрите на стоимость самого первого оператора доступа в двух планах: No max DOP: Estimated IO cost = 0.0171991 Max DOP 1: Estimated IO cost = 1.06623 Просто так стоимость не может отличаться. К сожалению, оценочное число строк скрадывается подсказкой FAST 100, которая устанавливает на запрос определенное целевое число строк (Row Goal), но, если для достижения этого числа потенциально нужно прочитать разное число строк – оптимизатор это учитывает. И действительно похоже, несмотря на то, что ваш план анонимизирован («чтобы никто не догадался» (с)), если посмотреть на переданные параметры, то: No max DOP: Column209 = (Parameter Compiled Value = Value 1, Parameter Runtime Value = Value 1) Column208 = (Parameter Compiled Value = Value 2, Parameter Runtime Value = Value 2) Max DOP 1: Column209 = (Parameter Compiled Value = Value 4, Parameter Runtime Value = Value 1) Column208 = (Parameter Compiled Value = Value 5, Parameter Runtime Value = Value 2) Это наводит на мысль, что вы просто неправильно проводите свой тест производительности и замеряете время запросов для разных параметров. Кроме того, пара общих соображения по плану: Удачи. |
||
| 8 июн 16, 12:02 [19270508] Ответить | Цитировать Сообщить модератору | |||
| Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?
|
|||||||
|
o-o
Guest |
он уже на это сообщил:
как во времянки сложить, так они не могут процедуру редактировать, а как хинтами уделывать, так сколько угодно |
||||||
| 8 июн 16, 12:22 [19270659] Ответить | Цитировать Сообщить модератору | |||||||
| Все форумы / Microsoft SQL Server | |
|
x