Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Можно ли заставить оптимизатор подольше подумать над планом запроса при создании хранимки?  [new]
Лукьчнчук Анатолий
Member

Откуда: Москва
Сообщений: 68
 		Можно ли заставить оптимизатор подольше подумать над планом запроса при создании хранимой процедуры?

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) 
	Sep 21 2011 22:45:45 
	Copyright (c) 1988-2008 Microsoft Corporation
	Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)
15 окт 13, 18:22    [14975089]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заставить оптимизатор подольше подумать над планом запроса при создании хранимки?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31780
Лукьчнчук Анатолий
Можно ли заставить оптимизатор подольше подумать над планом запроса при создании хранимой процедуры?
При сохздании нельзя, он при сохздании ХП планы не строит.

Планы строятся при первом выполнении.
Качество построения плана не регулируется, это заложено в коде, но можно заставить перестраивать план в зависимости от текущей статистики и параметров (как для всей процедуры, так и для отдельных запросов), см RECOMPILE
15 окт 13, 18:32    [14975124]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заставить оптимизатор подольше подумать над планом запроса при создании хранимки?  [new]
komrad
Member

Откуда:
Сообщений: 5496
 		Лукьчнчук Анатолий,

может это будет полезно
15 окт 13, 18:43    [14975180]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заставить оптимизатор подольше подумать над планом запроса при создании хранимки?  [new]
Shakill
Member

Откуда: мск
Сообщений: 1882
 		Лукьчнчук Анатолий,
интересная статья про эксперименты с флагом трассировки 8780, обратите внимание на выводы
15 окт 13, 18:52    [14975237]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заставить оптимизатор подольше подумать над планом запроса при создании хранимки?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Shakill
обратите внимание на выводы
Оно то понятно. Но хинты на то и придуманы, и энтерпрайзы на то и покупают, что всё меняется, всего не учтёшь и зоопарк бесконечен.

Т.е. надо решение когда переписать немедля дороже, чем временно заставить.

К примеру когда сущностей в запросе 100500 (доархитектурились), и все комбины явно уже не переберёт.
Такой флаг может быть лучше - один раз подождём, зато потом работает как надо. Чем выёпывать промежуточные результаты с гарантированной потерей производительности. К примеру.
15 окт 13, 20:39    [14975576]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заставить оптимизатор подольше подумать над планом запроса при создании хранимки?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Mnior
Shakill
обратите внимание на выводы
Оно то понятно. Но хинты на то и придуманы, и энтерпрайзы на то и покупают, что всё меняется, всего не учтёшь и зоопарк бесконечен.

Т.е. надо решение когда переписать немедля дороже, чем временно заставить.

К примеру когда сущностей в запросе 100500 (доархитектурились), и все комбины явно уже не переберёт.
Такой флаг может быть лучше - один раз подождём, зато потом работает как надо. Чем выёпывать промежуточные результаты с гарантированной потерей производительности. К примеру.
Я так понимаю, что при количестве сущностей в запросе 100500 этот флаг становится практически бесполезен, так как приведет к условно бесконечному циклу с перебором всех вариантов.
16 окт 13, 02:38    [14976919]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заставить оптимизатор подольше подумать над планом запроса при создании хранимки?  [new]
gang
Member

Откуда:
Сообщений: 1394
Mnior
К примеру когда сущностей в запросе 100500 (доархитектурились), и все комбины явно уже не переберёт.

Можно самому a офлайне подобрать нужную "комбинацию" и использовать optimize for, ну или, если совсем уж жестко, конкретный план гвоздями (USE PLAN) прибить. В принципе, и стандартных хинтов, чтобы план подправить, изрядное количество.
16 окт 13, 09:42    [14978061]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заставить оптимизатор подольше подумать над планом запроса при создании хранимки?  [new]
Crimean
Member

Откуда:
Сообщений: 13148
 		от как красиво расписали про хинты и планы.. это хорошо, если хранимка не параметризованная
а если от параметров план должен быть разным?
а для 1 стабильного плана да гвоздями - вообще не вопрос
16 окт 13, 10:02    [14978149]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заставить оптимизатор подольше подумать над планом запроса при создании хранимки?  [new]
gang
Member

Откуда:
Сообщений: 1394
Crimean
а если от параметров план должен быть разным?

А давайте еще раз постановку вопроса на 180 поменяем, ну чтоб не скучать.
ТС спрашивал нельзя ли заставить оптимизатор тратить больше времени на
построение исходного плана проц-ры при ее создании. Т.е. полагал этот план
некоторой единожды создаваемой (что конечно же неверно) ну или хотя бы
достаточно стабильной величиной. Соответственно этому и предложены были
варианты, что возможно предпринять.
Ваш же последний вопрос прямо противоположный - победить стабильность
плана и заставить его меняться. Ясное дело что одной таблеткой и понос и
золотуху не вылечить.
16 окт 13, 11:53    [14978931]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заставить оптимизатор подольше подумать над планом запроса при создании хранимки?  [new]
Crimean
Member

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

и да и нет. но в холивар скатимся такими темпами - полностью согласен. инструментов влияния на план перечислили достаточно, особенно по ссылкам хорошо
16 окт 13, 12:47    [14979380]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заставить оптимизатор подольше подумать над планом запроса при создании хранимки?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Mind
Я так понимаю, что при количестве сущностей в запросе 100500 этот флаг становится практически бесполезен, так как приведет к условно бесконечному циклу с перебором всех вариантов.
Если понимать буквально, то вообще не сработает - вывалится в ошибку. Есть ограничение на количество соединений. И оно относительно маленькое (256-1024). Но это не значит что нельзя за приемлемое время найти подходящий вариант. Все варианты всё равно сервер не перебирает - эвристики.
gang
Можно самому a офлайне подобрать нужную "комбинацию" и использовать optimize for, ну или, если совсем уж жестко, конкретный план гвоздями (USE PLAN) прибить.
1. optimize for тут как раз не сработает, у него другая задача - нелинейность данных. А тут порядок соединения, тут катит Force Order.
Хинты есть, на то он и коммерческий продукт, а не академический, чтобы в нём хотя бы один хинт был. :)

Но дело в том, что как раз случаи описанные автором случают не когда вся простыня на ладони, а зарыта во вложенных объектах.
И выковыривать всё это наружу - ухудшает поддержку кода (дублирование) или не всегда приемлемо/возможно (кажись параметризованных рекурсивных WITH не сделаешь, но это пример).

И даже force order не так прост подобрать. Хорошо что есть флаг, и можно сначала подсмотреть решение.
Короче - машина на то и машина, чтобы делать рутинные вычисления, а то так или итак очевидно или сиди ипись.

Crimean
инструментов влияния на план перечислили достаточно
Имхо мало, как мало и самих инструментов плана.
Время идёт, а MS SQL отстаёт от остальной отрасли в механизмах.
Я понимаю что нишевых конкурентов мало, но на фоне других ниш - порой лучше вообще от SQL отказаться - не тянет.
16 окт 13, 15:14    [14980637]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заставить оптимизатор подольше подумать над планом запроса при создании хранимки?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Mnior
Mind
Я так понимаю, что при количестве сущностей в запросе 100500 этот флаг становится практически бесполезен, так как приведет к условно бесконечному циклу с перебором всех вариантов.
Если понимать буквально, то вообще не сработает - вывалится в ошибку. Есть ограничение на количество соединений. И оно относительно маленькое (256-1024). Но это не значит что нельзя за приемлемое время найти подходящий вариант. Все варианты всё равно сервер не перебирает - эвристики.

Но дело в том, что как раз случаи описанные автором случают не когда вся простыня на ладони, а зарыта во вложенных объектах.
И выковыривать всё это наружу - ухудшает поддержку кода (дублирование) или не всегда приемлемо/возможно (кажись параметризованных рекурсивных WITH не сделаешь, но это пример).
Вы забываете одну простую истину. Даже перебирая все возможные варианты, оптимизатор все равно опирается не на реальный план выполнения и статистики по времени и IO, а на условные значения стоимости. И если в запросе с 100500 сущностей кривые статистики и неправильно расчитывается кардиналити соединений, то даже если оптимизатор докопается до идеального плана, он его отметет как не самый оптимальный по стоимости. А ошибка в статистиках, как мы знаем, всегда есть, и чем больше таблиц тем она больше.
В случае же если статистики и распределения у нас идеальны и фильтры все хорошие, то чтобы найти Good Enough Plan не обязательно перебирать все варианты, в 99% случаев он его найдет значительно быстрее.

Так что я не вижу особого практического применения этому флагу, разве что в академических целях.
16 окт 13, 21:49    [14982761]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заставить оптимизатор подольше подумать над планом запроса при создании хранимки?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Mind
Вы забываете одну простую истину. Даже перебирая все возможные варианты, оптимизатор все равно опирается не на реальный план выполнения и статистики по времени и IO, а на условные значения стоимости.
Естественно. Как вы могли на меня кого либо такое подумать?
Мы не рассматриваем вообще случай что Good Enough Plan (GEP) оказался паршивеньким. Это уже проблема архитектора БД.
Mind
И если в запросе с 100500 сущностей кривые статистики и неправильно расчитывается кардиналити соединений ...
То это ничего не значит. Ибо нет такого вообще понятия как кривые статистики у генератора плана. Что дали то и имеет.
Mind
, то даже если оптимизатор докопается до идеального плана
И такого понятия нет. Если только под этим не подразумевается GEP.
Mind
В случае же если статистики и распределения у нас идеальны и фильтры все хорошие, то чтобы найти Good Enough Plan не обязательно перебирать все варианты, в 99% случаев он его найдет значительно быстрее.
Wrong. Ничего находиться быстрее не будет (в общем виде, в среднем).
При любых статистиках будут разные планы и не будет полного перебора. И будет примерно одинаково (в среднем по больнице).

Такое ощущение что вы сами же в свою проблему и сели. Или невнятно выразились.
Mind
А ошибка в статистиках, как мы знаем, всегда есть, и чем больше таблиц тем она больше. ...
Так что я не вижу особого практического применения этому флагу, разве что в академических целях.
1. В том-то и дело, что факт какого-то там "накопления ошибки" ничего не даёт. Он просто тупо отказывается искать лучший (с точки зрения статистики) план.
2. Целесообразность? Это что призумция виновности? "Ты гуёвый аргитекдор - план палюбэ будет авно!"
Ну спасибки, удружили.

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

Я соглашусь, что план иногда и выходит паршивенький, но то что он частенько лучше - это факт. И более того, не будет лучше - зайдём с другого боку.

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

И я постоянно сетую на то, что механизм компиляции у скуля паршивенький тем - что он не кэширует промежуточные вычисления. А делает всё с нуля каждый раз доказывая что 2+2=4, при разворачивании одних и тех же вьюх. Это не говоря о постоянном парсировании запроса из строки.

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

Вопрос о необходимом перестроении архитектуры в данном случае - это другая тема.
И главное - где помощь в анализе планов? Имея статистики, структуру - он должен не только подобрать приемлемый план, подставив необходимый механизмы-подпорки в нужных местах, но и подсказать - эй мужик, а вот тут обрати внимание - тут лучше кэшировать данные, к примеру.
17 окт 13, 03:22    [14983254]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заставить оптимизатор подольше подумать над планом запроса при создании хранимки?  [new]
Александр Гладченко
Member

Откуда:
Сообщений: 10765
Блог
Лукьчнчук Анатолий
Можно ли заставить оптимизатор подольше подумать над планом запроса при создании хранимой процедуры?


Можно: http://msmvps.com/blogs/gladchenko/archive/2009/08/31/1719693.aspx

Сообщение было отредактировано: 17 окт 13, 14:17
17 окт 13, 14:16    [14985497]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить