Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
 Re: Execution Plan's hell  [new]
хорош сочинять
Guest
Cammomile
хорош сочинять, да я понятия не имею панацея не панацея. Просто предложил автору темы топик для ознакомления. Вдруг его это спасёт?


а я не говорю "нефиг предлагать".
или "хинты -- зло".
я говорю именно что "нефиг сочинять".
никто план руками не пишет.
план строит сервер.
а это способ повлиять, что в план попадет (какой параметр/хинт).
и нужно это, когда в явном виде это в запрос не пробросить.
а при доступности запроса, его же и корежат
26 июн 13, 19:08    [14488628]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
хорош сочинять,

В данной ситуации запрос доступен. Что делать что бы не нарваться на такой же прикол?

Тем более запрос в процедуре будет динамический (это я сейчас легко создал тестовую со статическим). А там ещё "какой вариант первый выполнился того и план будет". И не факт что "быстрый вариант" будет первым. Да и не уверен я что весь этот костыль будет работать в динамическом запросе.
26 июн 13, 19:20    [14488648]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
SomewhereSomehow
Member

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

Дважды прочитал ваш вопрос, но так и не понял, "кто на ком стоял". План, лучше тысячи слов, если хотите получить ответ для конкретного запроса. Выкладывать лучше действительные планы быстрого и медленного запроса, приложенные отдельными файлами в формате xml/sqlplan. Рекомендую это сделать, и, хотя, я, скорее всего, не смогу вам что-то посоветовать, т.к. завтра ухожу в отпуск, но уверен, что вы получите гораздо больше полезных советов от участников форума, если они смогут "пощупать" реальный план.

Что касается общих вопросов.

В MS SQL оптимизатор базируется на оценке стоимости плана, на которую влияет много факторов (доступные индексы, типы итераторов, свойства и т.д.), и одним из главных факторов является оценка числа строк - кардинальность. В том числе, он использует эту оценку для определения порядка соединения таблиц.
Для оценки числа строк сервер использует:
1) базовые сведения о статистике распределения данных в таблицах и индексах
2) математическая модель

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

1. Статистика

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

1.2 Отсутствует - отключено автосоздание статистики, используются табличные переменные, присутствуют коррелированные столбцы (многоколоночная статистика автоматически не создается)

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

2. Математическая модель
Как и любая модель, она несовершенна и может отличаться от реальности. В основном это происходит, если случай не предусмотрен в модели. К наиболее известным проблемам относятся

2.1 Догадки.
2.1.1 Неизвестные значения переменных - если сервер не знает значение для которого нужно оценить число выбираемых строк, он вынужден грубо оценивать это число при помощи зашитых коэффициентов.

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

2.1.3 Плохо поддерживаемые конструкции и предикаты, например, условие or в предикатах может моделироваться не очень хорошо.

2.2 Ограничения модели.
Тут трудно расписать конкретные случаи, по этому, опишу процесс в целом. Оценка (как правило, но не всегда) начинается от самых нижних итераторов (как правило это итераторы доступа к данным - сканирование/поиск по индексу или таблице) к верхним. В ней участвуют основные элементы статистики, гистограммы и плотность. По мере продвижения к верхним итераторам, эти объекты (гистограммы и плотность) модифицируется согласно алгоритмам модели. Например, при соединении двух таблиц получается новая гистограмма, которая сделана на основе двух гистограмм соединяющихся таблиц. При этом, она получается путем совмещения/склеивания и интерполяции шагов участвующих гистограмм. В результате, поскольку мы и так оперируем статистикой (т.е. некой усредненной информацией) получившаяся гистограмма может уже сильно отличаться от реальности, она в свою очередь может быть входной гистограммой для следующего этапа и т.д. - чем больше итераторов - тем более вероятно расхождение с реальностью и накопление ошибки. Отличная статья есть у SQL CAT When to Break Down Complex Queries.

2.3 Баги
Сервер тоже пишут люди, им свойственно допускать ошибки, например, не очень хорошо вычисляется стоимость параллельного loop join. Есть также и чистые ошибки моделирования внутренних функций (пример).

Как бороться.

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

1.2 Включить автосоздание, использовать вместо табличных переменных временные таблицы, создать для коррелированных столбцов статистику/индекс вручную.

1.3 Использовать обновление статистики с опцией with fullscan.

2.1 Не использовать локальные переменные, разнести по разным веткам кода при помощи if (особенно актуально в случае optional parameter problem), сделать вычисляемые колонки по выражениям, привести в порядок схему данных, чтобы избавиться от неявных преобразований. Использовать опцию option(recompile) - в определенных версиях сервера и при соблюдении определенных условий это позволяет оптимизатору заменить переменных конкретными значениями времени выполнения и построить план именно для них, а не для неизвестных параметров, минусом является нагрузка на сервер который будет вынужден строить план каждый раз, когда запрос выполняется.

2.2 Материализовать результат промежуточных вычислений. Единственный легитимный способ это сделать - сохранить данные во временную таблицу/табличную переменную. Есть предложения на коннект добавить хинт, который бы указывал оптимизатору материализовать CTE, если есть желание - можно проголосовать Provide a hint to force intermediate materialization of CTEs or derived tables by Adam Machanic.

2.3 Завести пункт на connect =) Постараться избавиться от бажной конструкции при помощи переписывания/разделения запроса.

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

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

В определенных случаях - разнообразные хинты(with(index), forceseek, forcescan, loop/merge/hash join, force order и т.д.) Обычно хинты рекомендуют использовать как последний рубеж, когда ничего другое не помогает и имеет место быть либо баг оптимизатора, либо очень специфичная ситуация, либо когда разработчик знает лучше оптимизатора.

Скорее всего я что-то забыл упомянуть, т.к. писал на вскидку, если что, меня дополнят.
26 июн 13, 20:54    [14488857]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
Cammomile_home
Guest
хорош сочинять, т.е. вы хотите сказать, что если я скопипращу серверный план в формате эксемель, поставлю его в N'' после USE PLAN, и но руками поменяю, например, тип джойна, или какой-то числовой оценочный парамтер, то этот план не отработает ?
26 июн 13, 22:20    [14489122]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
SomewhereSomehow,

Спасибо за внушительный ответ! Многое конечно я знаю, но если выкинуть то что я знаю ответ был бы несвязным и непонятным другим. Думаю у меня "есть чем занятся на завтра по полной" если никто не дёрнит :)
26 июн 13, 23:03    [14489252]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Cammomile_home
хорош сочинять, т.е. вы хотите сказать, что если я скопипращу серверный план в формате эксемель, поставлю его в N'' после USE PLAN, и но руками поменяю, например, тип джойна, или какой-то числовой оценочный парамтер, то этот план не отработает ?
Почти наверняка оно свалится с ошибкой или просто проигнорирует. Попробуйте вот так вот просто поменять NESTED LOOPS на MERGE JOIN.
27 июн 13, 00:39    [14489509]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
хорош сочинять_home
Guest
Cammomile_home
хорош сочинять, т.е. вы хотите сказать, что если я скопипращу серверный план в формате эксемель, поставлю его в N'' после USE PLAN, и но руками поменяю, например, тип джойна, или какой-то числовой оценочный парамтер, то этот план не отработает ?


1a. (тип джойна: who knows :) )
You can change the plan before you use it by using USE PLAN, such as by changing join orders and operators, and adjusting scans and seeks. However, format of the plan must still match Showplanxml.xsd. You may not be able to force a plan that has been changed. An error occurs if you use a plan in a USE PLAN hint when the plan is not one of the plans that SQL Server would typically consider for the query during optimization.

1b. (какой-то числовой оценочный парамтер)
Information about cardinality estimates dictated by the <EstimateRows> element is not enforced by the USE PLAN query hint. Because the query optimizer uses cardinality estimation to determine the amount of memory to devote to running a query, you should maintain accurate statistics, even when you are using USE PLAN.

http://msdn.microsoft.com/en-us/library/ms186954(v=sql.105).aspx

2. (план не отработает ?)
When USE PLAN is specified directly in a query, an invalid plan causes the query to fail. When USE PLAN is specified in a plan guide, an invalid plan will not cause a query to fail; however, the plan is compiled without using the hint and may not be the best choice.
http://msdn.microsoft.com/en-us/library/ms186343(v=sql.105).aspx

---------------------------------------------------------
а вообще, никогда не доводилось видеть "вручную переправленный" план
(пусть тут выскажется кто-нибудь, кто видел "не в теории")

план выдирается и вставляется "как есть" в случаях типа:

" A common scenario may involve queries that executed well in an earlier version of SQL Server, but perform poorly under an upgraded version, whether this upgrade is a service pack or a full version upgrade. Most of the time, an upgrade leads to equal or better performance in most query execution times; however, there might be some exceptions. The USE PLAN query hint is available to handle those cases when a query plan that is selected by the query optimizer in an earlier product version is preferred over the one selected after the upgrade."

"Sometimes what you want to do is create a plan guide from an existing query plan so you make sure that the existing plan "sticks" in the future. For example, if you shut down and restart SQL Server, it will empty the plan cache and all the old plans will be rebuilt, possibly in a different way. And one way to prevent that is to create a plan guide that reapplies the same query plan all the time"

т.е. обычно направление USE PLAN в query или PLAN GUIDE FROM HANDLE (sp_create_plan_guide_from_handle)-- "сохранить бы хорошо работающее", а не "укорежить план".

a вот PLAN GUIDE (sp_create_plan_guide с указанием @params/@hints) -- про что был мой первый ответ -- наоборот, заставляет сгенерить другой план:
в случае "недоступности исходного кода" дается попытка "пропихнуть" в исходный код
хинт или "хороший" параметр.
там вообще "свой" план не требуется, оптимизаторa заставляют переделать построенный ранее (плохой) план: увидел запрос, слово в слово совпадающий с тем, для к-ого задан plan guide, рассматривай его с учетом хинта/параметра, заданного в plan guide-е, т.е. строй другой план
27 июн 13, 01:52    [14489574]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
От обилия чтива уже дымлюсь :)

В своём примере доупростил до минимума
1) Основная таблица сущности которую ищу (пробовал и без неё потому что непосредственно из неё ничего не надо получать в примере)
2) Containstable с параметром и единственным !!! (от которого скорость и план запроса меняются) приджойненный к основной сущности)

(основные таблицы с данными которые потом группируются)
3) таблица в которой вертикально хранятся сущности (пока 1-1 или уникальные если передан дополнительный параметр и родительские) связанные с найденной сущностью
4) значения атрибутов (вертикально как в EAV моделях) "ссылка на сущность", "ссылка на атрибут", "значение атрибута" (пока таблицы тестовые и ссылки и логические значения хранятся одинаково)

потом происходит процесс группировки "атрибут, значение" таким образом я получаю
1) общее количество найденных сущностей (есть специальный атрибут для каждой сущности который по сути означене её существование)
2) детали по каждому атрибуту-значению

Например, (отдалённый, а то реальная специфика другая).
Всего 10
Цвет, красный, 5
Цвет, синий, 3
....
Производитель, Адидас, 4
Производитель, Абибас, 6
...
Новая коллекция, 1, 7

Это позволяет иметь один group by оператор простейший с нужными индексами (лучше чем текущий вариант с grouping sets, который я написал исправляя "предыдущее" где их много и различные строковые атрибуты и т. п.).

В целом пробовал этот же вариант упрощать до
1) Функця ФТС
2) атрибуты (их большенство) только найдённой сущности

т. е. в запросе один джойн только. Закономерности те же.

Если запрос возвращает приблизительно менее 10000 строк - всё работает "замечательно"
Такой же запрос, но с другим значением параметра возвращающий 50000 строк работает долго, но если заставляю использовать план как для первого значения параметра через OPTIMIZE FOR (не анкновн).

Я как бы вообще хотел бы уйти от хинтов, потому что запрос серьёзно менятся может и я физически не смогу прописать "для каких оптимизировать" (да там даже ФТС не постоянно будет) а KEEPFIXED PLAN ещё вопрос "что первое выполнят".

Я уже пересоздавал тестовые объекты (таблицы, индексы и т. п.) и статистику.
Я ещё готов поставить что-то вроде (OPTIMIZE FOR unknown) которые как бы "универсальные", хотя не хочу ставить такие хинты которые заставляют не сохранять планы запросов. Но в идеале хотел бы сделать так, что бы СКЛ Сервер просто не ошибался бы.
В случае когда возвращается 50000 строк без хинта работает 15 секунд, а с хинтом 250-300мс и ещё проц меньше грузит.
Жёстко планы прописывать динамическому запросу не кажется "хорошей идеей" (тем более такого объёма).

И что характерно - план выполнения хороший только есть LEFT join таблицы с связанными сущностями и "любой" со значениями атрибутов.

Заранее спасибо! Пока читаю и "огружаюсь" дальше.
28 июн 13, 18:27    [14498814]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Нарвался на вариант запроса, когда через раз работает быстро/долго как "т-триггер" (из цифровой техники, а не ДМЛ триггер в МС СКЛ). Причём "чётко работает" :)
28 июн 13, 18:44    [14498838]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
NIIIK
Нарвался на вариант запроса, когда через раз работает быстро/долго как "т-триггер" (из цифровой техники, а не ДМЛ триггер в МС СКЛ). Причём "чётко работает" :)
Опцию option(recompile) то уже попробовали?
28 июн 13, 20:16    [14499050]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Да, пробовал.
28 июн 13, 21:13    [14499191]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
После долгих "шаманств" своём конкретном случае
решаю остановится на варианте решения hint-ов
1) WITH (FORCESEEK)
2) дополнительный для schema binding view вариант with(noexpand, FORCESEEK)
3) Left join для таблицы в которой лежат "связанные сущности" (к ней ещё один INNER join) и это кусок запроса который должен "применится" только после того как "всё уже найдено", хотя по логике там должен быть "inner join" (этот костыль победить не смог)

варианты хинтов считаю "минимально проблемынми" и в голову не пришёл случай когда в тех местах должно быть "по-другому".
2 июл 13, 19:38    [14512730]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
Все форумы / Microsoft SQL Server Ответить