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

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

В очередной раз наткнулся на "подводный камень".
Запрос, считающий количество записей по некоторым условиям, работает на одних значениях одной и той же переменной быстро на других медленее (при том же количестве результов на выходе) а другом немеряно долго (большее количество строк). Причём "переменная" - условие поиска из одного слова в ФТС.

Конечно, глубо "управление планами ззапросов" не знаю но пытался сделать "такой же план как у быстрого запроса" методом with(Index=IndexName). Особо не получилось, пошёл не Index Seek, а такой же кривой план только Index Scan вместо Table Scan.

Что самое интересно если запрос с option(maxdop 1) - план стабильный и медленный запрос работает не 15ть секунд, а 1,2
Но тогда и быстрый запрос работает не 66мс, а 206мс (ещё обы, один процессор). Может при "реальной нагрузке" так и лучше (если много пользователей его выполняют одновременно и сервер всё равно загружен и лучше не тратить ресурсы на перелеливание).
Но с другой стороны WTF? Почему я вообще туда полез?
С другой стороны "долез, увидел, а что делать не знаю". Что делать? Как заставить использовать "такой ж план" (или лучший).

Далее, когда я пишу запрос у меня есть своё представление "по какому пути пойдёт запрос" (которое конечно часто отличается от серверного). Часто я понимаю что "надо сначала выполнить эту часть, потом к данному результату приджойнить вот эту таблицу/таблицы, получить дочерние записи и всё подсчитать в group by",
но по факту СКЛ сервер "думает иначе" и в итогде запрос работает дольше.
Доходит до маразма что я ставлю вместо "inner join" - "left join" и запрос гораздо ближе к тому плану что я ожидаю.
Да что там "замена на left", иногда "сохранть результат во временную таблицу и потом к нему приджойнится" ( или CTE c TOP) работает быстрее и никакие пляски с бубнами не помогают.
Как тут явно указывать "Сначала сделай это, потом лезь туда используя индекс..." ?
Я то понимаю что "тот порядок join что я написал SQL Серверу глубоко по боку".

Далее, когда я соединяю дочернюю и родительскую таблицы и имею параметр который ограничивает родителей, потом соответственно гораздом меньше дочерних записей к которым соотвественно применяются другие критерии поиска, то я ожидаю что СКЛ сервер сделат именно так.

Например, есть "Человек (ФИО, ПОЛ, национальность)" > "Город (Налвание, Область, тип города)".

и когда я указываю параметры поиска города, потом параметры поиска человека, например like 'Иван%' по фамилии (или другому атрибуту) а потом хочу подсчитать сколько там мужчин, женщин, потом по каждой национальности, потом по двум атрибутам вместе и "всего" (имею/тестирую отдельную вертикальную таблицу для этого что бы не заморачиваться с grouping sets который пока работает), то я ожидаю

1) что если я ввёл критерии поиска города, то мы сначала отсеим много городов тут и потом начнём выбирать их дочерние "Человек", которые на него ссылаются, а после этого фильтровать уже экземпляры "Человек" по дополнительным критериям поиска. Вместо этого я наблюдаю что план сначала находит туеву хучу людей, а потом отсеивает их маленьку часть по родительским атрибутам проверяя ОДНОГО и ТОГОЖЕ родителя для каждого найденного "Человек".

2) Только после того как найдены необходимые люди приджойнится таблица которая содержит атрибуты человека ввертикальном виде (на самом деле сложнее, потому что есть ещё отдельная таблица которая хранит прямые ссылки для каждого человека на родительские и дочерние сущности) ТОЛЬКО для найденных людей. Причём будет Index seek по заведомо подготовленному индексу. Да, часто это происходит, но бывают случаи когда НЕ происходит (а должно). Причём таких проблем нет при (maxdop 1).
Что тут делать?!

Хоть какую статью/литературу (не МСДН) для понимания этих процессов чиать?
Ещё лучше в них "не лазить" и если знаешь что "оптимальный путь выполнения есть" и "созданы все нужные индексы для того что бы пройти по этому пути" - то запрос сработает быстро (при любом раскладе) и не будет резко менять время выполнения при изменеии значения одного и того же параметра, но при этом возвращающем приблизительно то же количество строк.


Заранее спасибо за советы.
26 июн 13, 15:14    [14487066]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Мона начинать отсюда , и далее все статьи свзанные с етим . SomewhereSomehow в свое время крепко постарлся по етому поводу
Паралельно мона здесь посмотреть,тоже хороший сборник статей
26 июн 13, 15:23    [14487141]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
"Далее, когда я пишу запрос у меня есть своё представление "по какому пути пойдёт запрос" (которое конечно часто отличается от серверного). Часто я понимаю что "надо сначала выполнить эту часть, потом к данному результату приджойнить вот эту таблицу/таблицы, получить дочерние записи и всё подсчитать в group by"
Времянки с предварительными вычислениями + FORCE ORDER ?
26 июн 13, 16:04    [14487555]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Cammomile
"Далее, когда я пишу запрос у меня есть своё представление "по какому пути пойдёт запрос" (которое конечно часто отличается от серверного). Часто я понимаю что "надо сначала выполнить эту часть, потом к данному результату приджойнить вот эту таблицу/таблицы, получить дочерние записи и всё подсчитать в group by"
Времянки с предварительными вычислениями + FORCE ORDER ?


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

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
как-нить можно "зафиксировать" план выполнения запроса для различных значений переменных?
26 июн 13, 16:20    [14487681]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Скажем так, за 7 лет практики я встречал массу ситуаций, когда времянки не то, что не быдлокод, а единственный приемлимый вариант.

И главное, какая вам разница что кто считает? У вас задача, вам ее надо решить.
Сначала сделайте чтоб работало, потом сделайте чтобы быстро работало, затем, если будет время и желание, чтобы было идеально.
26 июн 13, 16:31    [14487763]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
NIIIK
как-нить можно "зафиксировать" план выполнения запроса для различных значений переменных?

Можно зафиксировать план первой компиляции
автор
The KEEPFIXED PLAN query hint is used in order to retain the original execution plan used to initially compile a statement
26 июн 13, 16:34    [14487784]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
NIIIK
Member

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

И главное, какая вам разница что кто считает? У вас задача, вам ее надо решить.
Сначала сделайте чтоб работало, потом сделайте чтобы быстро работало, затем, если будет время и желание, чтобы было идеально.


Вот "что бы работало" уже "наделали".
26 июн 13, 16:44    [14487863]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Вот я не гуру, но правда подразумеваю что грамотно схороненные предрасчеты + форсордер + возможно джойнхинты вам дадут нужный результат.
26 июн 13, 16:46    [14487877]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
[quot Cammomile
автор
The KEEPFIXED PLAN query hint is used in order to retain the original execution plan used to initially compile a statement
[/quot]
Поставил в запросе (скрипт, не процедура) выполняются с разными планами выполнения.
26 июн 13, 16:56    [14487942]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
NIIIK
Member

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


с option (FORCE ORDER)

работает быстрее чем вообще без Хинтов, но даже с maxdop 1 работает быстрее чем с форсордер.
Быстрый вариант запроса (тот который целевой, который тоже не мало строк возращает но интуитивно понятно что план хорший) начинает работать значительно медленее. Есть "одна идея", проверяю
26 июн 13, 17:12    [14488074]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
А с максдоп И форсордер как работает?
ПРо кипфиксед почитайте доки. там внятно пояснено как и для чгео он нужен ;-)
26 июн 13, 17:26    [14488200]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Вместе работают плохо.
Могу сказать что "если оформить запрос в процедуру и сделать option (KEEPFIXED PLAN)
работает хорошо и план "тот же самый что на быстром варианте".

Но всё равно, возвращаясь к изначальному, это всё "пляски с бубнами".
26 июн 13, 17:33    [14488231]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
И , кстати, бываю случаи, когда распараллеливание негативно сказывается на быстродействии, но это надо гуглить отдельно .

К примеру http://blogs.msdn.com/b/davidhardin/archive/2011/09/28/sql-server-parallelism-the-dark-side.aspx
26 июн 13, 17:36    [14488239]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
По факту "распаралеливание" в целом замедляет запрос всегда, если считать "сколько ресурсов потрачено", просто оно позволяет делать это одновременно.
Но если сервер "сильно загруженный по CPU", и много запросов выполняется одноверменно, то лучше выполнять 8мь запросов одновременно и каждый на своём ядре (из 8ми) чем все 8 запросов на всех 8ми ядрах.

Как-нить джойну можно указывать "его выполнить в последюю очередь, а остальное согласно плана" ?
26 июн 13, 17:52    [14488312]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
автор
Как-нить джойну можно указывать "его выполнить в последюю очередь, а остальное согласно плана" ?

Форс ордер позволяет задать порядок джойнов. Что последним напишите то последним и будет. И что значит" остальное согласно плана"?
26 июн 13, 17:58    [14488329]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Что бы "форсордер примерялся только к последнему, например

есть

with cteSearchResult as
(
 select
 ...
 join 
 join ...
)

select ..., 
         count(1)
  from cteSearchResult sr
  inner --Реально это внутри СТЕ, но пробовал и так и заменяю этот джой на LEFT потому что реально и план лучше и работает
         --быстрее
   join lastTable lt
     on sr.pk = lt.fk
 group by ...


Вот что бы сначала выполнилось "то что в ЦТЕ по своему плану как если бы было только оно", а потом внешний джойн (и желательно что бы всё без лишнего ЦТЕ)
26 июн 13, 18:10    [14488380]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Гм, о таком функционале я не знаю.

Но вы же можете результат СТЕ запихатьв временную табличку или переменную ;-)
26 июн 13, 18:15    [14488409]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Кстати есть фича, которая позволяет написать план буквально руками и его использовать. Мб это вас спасет? Лично я таким ни разу не пользовался.

http://technet.microsoft.com/en-us/library/cc917694.aspx
26 июн 13, 18:22    [14488448]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
хорош сочинять
Guest
Cammomile
Кстати есть фича, которая позволяет написать план буквально руками и его использовать. Мб это вас спасет? Лично я таким ни разу не пользовался.

http://technet.microsoft.com/en-us/library/cc917694.aspx


Plan Guides руками не пишут.
их применяют как последнее средство, например, когда код невозможно поменять
(зашит в приложении),
а хорошо бы в тот код пропихнуть некий хинт(или наоборот, убрать имеющийся).
план все равно сервер строит, а не кто-то там руками, меняя все подряд как захотелось.
никакая не панацея, короче
26 июн 13, 18:36    [14488500]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3716
это в мсскл я вам скажу еще хороший оптимизатор....
кое где даже тупо все варианты плана перебрать толком не успевает
26 июн 13, 18:39    [14488518]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
хорош сочинять
Cammomile
Кстати есть фича, которая позволяет написать план буквально руками и его использовать. Мб это вас спасет? Лично я таким ни разу не пользовался.

http://technet.microsoft.com/en-us/library/cc917694.aspx


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


Да я бы рад не писать хинтов и т. п. колхозов, но пока единственный вариант нашёл
"Хранимка + option (KEEPFIXED PLAN)" и работает в целом "как надо", а для остального "углубления" пока мозгов не хватает.
26 июн 13, 18:41    [14488527]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
хорош сочинять, да я понятия не имею панацея не панацея. Просто предложил автору темы топик для ознакомления. Вдруг его это спасёт?
26 июн 13, 18:43    [14488533]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
NIIIK, ну что вы заладили "быдлокод, колхоз"

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


Т.е. грамотное хинтование совершенно нормальная практика, если вы ТОЧНО знаете структуру данных, и какой порядо обработки будет оптимальным.
26 июн 13, 18:53    [14488563]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan's hell  [new]
NIIIK
Member

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

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


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


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


Кстати, вариант со временной таблицей работает долго (не говорю что все детали успел проверить).

Длительное время занимает вставка самих идентификаторов (хотя просто count работает быстро и сам промежуточный результат содержит менее 50000 строк) но если даже разобарться с этим шагом, то второй шаг который джойнится к временной (так же проверенно на обычной постоянной таблице) 370мс против варианта "процедура + хинт", где ВЕСЬ запрос работает 276мс (хотя план опять же идёт не по лучшему варианту).
26 июн 13, 19:06    [14488617]     Ответить | Цитировать Сообщить модератору
 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]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Microsoft SQL Server Ответить