Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 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!!!
Сообщений: 3685
это в мсскл я вам скажу еще хороший оптимизатор....
кое где даже тупо все варианты плана перебрать толком не успевает
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]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить