Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?  [new]
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) делает запрос намного более оптимальным?  [new]
invm
Member

Откуда: Москва
Сообщений: 9915
OPTION(MAXDOP 1) ни при чем.
Вы поменяли текст процедуры, что вызвало ее перекомпиляцию с другим значениями параметров - соответственно, изменился план выполнения.
7 июн 16, 15:45    [19267669]     Ответить | Цитировать Сообщить модератору
 Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?  [new]
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) делает запрос намного более оптимальным?  [new]
o-o
Guest
планы выкладывайте,
может, вы в куче своих NL не заметили отличие планов.
и вообще, когда "куча" джойнов, их разбивать надо.
соединили 4-5 штук, сохраняете результат во времянку.
---
может, MAXDOP 1 и вовсе ни при чем.
если процедура с параметрами,
изменение текста процедуры вынесло план из кэша.
а первый запуск процедуры оказался с более "подходящим" параметром
7 июн 16, 15:53    [19267718]     Ответить | Цитировать Сообщить модератору
 Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9184
офф... Не бывает "намного более оптимально", бывает или оптимально или не оптимально. Оптимально - это максимальная оценка, по-русски "самый лучший".
7 июн 16, 16:27    [19267890]     Ответить | Цитировать Сообщить модератору
 Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?  [new]
WinterGraveyard
Member

Откуда:
Сообщений: 79
invm
OPTION(MAXDOP 1) ни при чем.
Вы поменяли текст процедуры, что вызвало ее перекомпиляцию с другим значениями параметров - соответственно, изменился план выполнения.

Нет. Я подозревал такое поведение, поэтому эта опция добавлялась и удалялась несколько раз. Каждый раз после добавления снижались время выполнения и кол-во чтений, после удаления опции - возрастали.
o-o
и вообще, когда "куча" джойнов, их разбивать надо.
соединили 4-5 штук, сохраняете результат во времянку.

Не выйдет. Процедура - не самописная, такие процедуры в большом количестве генерит ERP. Собственно, было вообще предложено выставить max degree of parallelism=1, я удивился такому кардинальному решению, и решил попробовать на одной процедуре (на всех существующих автосгенеренных это будет затруднительно ввиду их большого количества). Однако, эксперимент принес свои плоды.
o-o
может, MAXDOP 1 и вовсе ни при чем.
если процедура с параметрами,
изменение текста процедуры вынесло план из кэша.
а первый запуск процедуры оказался с более "подходящим" параметром

да, с параметрами, про вынос из кэша написал выше.
o-o
планы выкладывайте,
может, вы в куче своих NL не заметили отличие планов.

А тут проблема в том, что в студии процедура что так, что так отрабатывает одинаково медленно - она возвращает очень большой объем данных. А вышеописанное поведение с 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) делает запрос намного более оптимальным?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9184
WinterGraveyard,

То, что упало количество чтений, означает использование другого плана запроса. Чудес не бывает.
7 июн 16, 18:00    [19268298]     Ответить | Цитировать Сообщить модератору
 Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Владислав Колосов,

ну то что он другой может стать это без вопросов. Вообще были статьи по поводу того что отключение параллелизма может увеличить производительность запроса, ну и про то что это снизит нагрузку ЦП вообщем-то ясно
7 июн 16, 18:06    [19268311]     Ответить | Цитировать Сообщить модератору
 Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?  [new]
o-o
Guest
Да мы переживем, что студия медленно отрабатывает и то, и это. Все равно план давайте, актуальный.
Наверняка все ваши лупы при смене плана превращаются в hash join, вот и посмотрим, что за оценки и что там на самом деле.
Короче, план в студию
7 июн 16, 18:35    [19268376]     Ответить | Цитировать Сообщить модератору
 Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?  [new]
invm
Member

Откуда: Москва
Сообщений: 9915
WinterGraveyard
А вышеописанное поведение с OPTION(MAXDOP 1) наблюдается именно в клиентском приложении (несамописном), которое из всей выборки берет только 10 строк.
Очевидно в параллельном плане присутствует блокирующий итератор, которого нет в обычном.

Выполните процедуру в студии в обоих вариантах, с одинаковыми параметрами и с set xml statistics on и выложите сюда полученные планы в формате sqlplan.
7 июн 16, 21:52    [19268889]     Ответить | Цитировать Сообщить модератору
 Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?  [new]
WinterGraveyard
Member

Откуда:
Сообщений: 79
Планы в аттаче (в архиве, т.к. очень большие).

К сообщению приложен файл (maxdop.7z - 71Kb) cкачать
8 июн 16, 07:29    [19269355]     Ответить | Цитировать Сообщить модератору
 Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?  [new]
invm
Member

Откуда: Москва
Сообщений: 9915
WinterGraveyard
Планы в аттаче
Плана с параллелизмом там нет.
И статистику неплохо бы обновить - оптимизатор в оценках сильно промахивается.
8 июн 16, 10:29    [19269915]     Ответить | Цитировать Сообщить модератору
 Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?  [new]
o-o
Guest
invm
И статистику неплохо бы обновить - оптимизатор в оценках сильно промахивается.

он не то что промахивается.
это ему выставили OPTION(FAST 100),
чтобы он лупы запилил (Estimated number of rows = 100)
ССЗБы
8 июн 16, 10:52    [19270024]     Ответить | Цитировать Сообщить модератору
 Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?  [new]
SomewhereSomehow
Member

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

Добрый день.
автор
За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?

Такое, в принципе, может происходить и 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)

Это наводит на мысль, что вы просто неправильно проводите свой тест производительности и замеряете время запросов для разных параметров.

Кроме того, пара общих соображения по плану:
  • много соединений
  • Хинтование – кроме того, что у вас Fast 100, но реально 40 000 (Зачем 40 000? Или зачем Fast 100?) у вас еще есть форсированные индексы, например, в самом первом (крайнем правом) Index Seek у вас свойство Forced Index = true. Я понимаю, что это сторонний производитель писал и ему виднее, может быть хинты действительно нужны, но в таком случае, не удивляйтесь и просто следуйте рекомендациям, ведь вам же помог совет про maxdop 1.
  • 2005 сервер… в 2016 году… =)

    Удачи.
  • 8 июн 16, 12:02    [19270508]     Ответить | Цитировать Сообщить модератору
     Re: За счет чего OPTION (MAXDOP 1) делает запрос намного более оптимальным?  [new]
    o-o
    Guest
    SomewhereSomehow
    Кроме того, пара общих соображения по плану:
  • много соединений

  • он уже на это сообщил:
    WinterGraveyard
    o-o
    и вообще, когда "куча" джойнов, их разбивать надо.
    соединили 4-5 штук, сохраняете результат во времянку.

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

    как во времянки сложить, так они не могут процедуру редактировать,
    а как хинтами уделывать, так сколько угодно
    8 июн 16, 12:22    [19270659]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить