Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет!  [new]
snake
Member

Откуда: Russia, Penza
Сообщений: 2290
1. Есть "проца", в которой есть селект (довольно сложный). Но не внем сейчас дело. Меня интересует, почему условно говоря при создании хп, она выполняется быстро - 0,01-0,02 сек. А на следующее утро 2-3 сек. При DBCC FREEPROCCACHE опять начинает выполняеться быстро.
ночью работает maintenance plan - полный бэк, с оптимизацией индексов и потом статистики.

2. Обьясните, пожалуйста, отличия в использовании таких двух вызовов.

exec sp_executesql N'exec dbo.someshit @P1 , @P2 , @P3 , @P4 , @P5 , @P6 ', 

N'@P1 varchar(2),@P2 varchar(2),@P3 varchar(4),@P4 varchar(4),@P5 varchar(4),@P6 varchar(17)',
'29', ' 1', '2000', ' 258', ' 1', '26,29,41,44,52,55'
и
exec dbo.someshit '29', ' 1', '2000', ' 258', '   1', '26,29,41,44,52,55'

в плане того что и как попадает в syscacheobjects, как используется, когда оттуда "вымывается".

заранее, всем спасибо.
зы. приветствую ссылки на msdn.
5 авг 03, 11:10    [288280]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
Glory
Member

Откуда:
Сообщений: 104760
она выполняется быстро - 0,01-0,02 сек. А на следующее утро 2-3 сек. При DBCC FREEPROCCACHE опять начинает выполняеться быстро.
Это говорит о том, что закэшированный план выполнения не есть самый оптимальный план выполнения.

Возможно план становится неоптимальным как раз в следствии "ночью работает maintenance plan - полный бэк, с оптимизацией индексов и потом статистики". Может быть заодно после этого установить флаг перекомпиляции процедуры, с помощью sp_recompile.
5 авг 03, 11:20    [288303]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
snake
Member

Откуда: Russia, Penza
Сообщений: 2290
закэшированный план выполнения не есть самый оптимальный план выполнения.
и
Возможно план становится неоптимальным как раз в следствии
? (наверное здесь имелось ввиду оптимальным ?)

1. т.е. я правильно понял - самый оптимальный и выполняется быстро вещи здесь не тождественные?

2. установить флаг перекомпиляции процедуры, с помощью sp_recompile
т.е. заново положить в syscacheobjects не самый оптимальный план выполнения?
Это как вариант. Хотя,я думаю, тогда уже лучше взяться за запрос. (беда...)

3. Glory, а насчет отличия в использовании ...
5 авг 03, 11:33    [288338]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
Glory
Member

Откуда:
Сообщений: 104760
1. План создается и скорость выполнения процедуры оптимальная
2. "ночью работает maintenance plan - полный бэк, с оптимизацией индексов и потом статистики" и этот план становится неоптимальным.
3. Но план все равно продолжает использоваться сервером. Ведь он же закэширован.
4. DBCC FREEPROCCACHE заставит построить новый план но уже с учетом новых данных об индексах и статистиках.
5. То, что делает DBCC FREEPROCCACHE для всех планов можно сделать с помощью sp_recompile для одного плана.

6. Какие отличия вас интересуют ?
5 авг 03, 11:45    [288363]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
Crimean
Member

Откуда:
Сообщений: 13148
План оптимальный с точки зрения сервера. А это совершенно не значит, что это самый быстрый способ выполнения запроса. Более того, есть варианты, когда планы выполнения (несколько) известны заранее и ими можно управлять (хинтами). Так вот, самый быстрый план оказывается самым дорогим с точки зрения оптимизатора. И, если подумать, это отчасти логично. Но как это напрягает! :)

P.S.В 7.0 у меня часто "попускало" после
update statistics with fullscan

Мои личные объяснения - база большая и общие тенденции распределения данных совершенно не такие, как частные за последний период. А большинство объектов ориентированы на общую статистику. Ну и далее - додумать самостоятельно, я, собственно, привел только факты :)
5 авг 03, 11:54    [288384]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
KOLCHOZ_POSTEVENT
Guest
Как сказал один заслуженный мент:
"Мы умножению не обучены,а только отниманию и делению".
А теперь к делу.
Одна из моих баз болела тем же самым.Мне пришлось вспомить вышеуказанную мысль и сделать следющее:
1)Составил на бумаге список замешанных в деле таблиц.
2)Изваял процедуру,которая убивает все их non clustered index и заново создает.
3)Замерял время ее работы-получил 5 минут.
4)Поставил её на еженощное выполнение.
Все прошло,как с белых яблонь дым.
Причины,как видно таковы:массивные insert разрушают non clustered index,все эти update statistics-в пользу бедных,что они могут убрать наплодившиеся leef lawel,да ни в жисть.А посему снова возвращаемся к первой мысле нашего повествования.
5 авг 03, 12:13    [288424]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
Glory
Member

Откуда:
Сообщений: 104760
Можно поподробнее о "массивные insert разрушают non clustered index" ?
5 авг 03, 12:15    [288431]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
snake
Member

Откуда: Russia, Penza
Сообщений: 2290
2 Glory
1,2,3,4,5 - понятно, спасибо.

6. Два вида вызовов, так?

Чистим процедурный кэш.
Для одного вызова (который первый) в syscacheobjects "кладётся" запись Compiled Plan с objtype Prepared и две Executable Plan и Compiled Plan с objtype Proc.
Чистим процедурный кэш.
Для другого вызова (который в второй) в syscacheobjects "кладётся" две записи Executable Plan и Compiled Plan с objtype Proc.

И далее для всех вызовов используется Executable Plan (usecounts увеличивается)

А в чем разница у Compiled Plan с objtype Prepared и Compiled Plan с objtype Proc (а ещё бывают и adhoc)?

ps. только заметил, очищая процедурный кэш и пересоздавая проц. время выполнения (а соответственно и план) разные. т.е. очистил - сохранил - выполняется "долго",
добавил пробел в комментарии - сохранил - выполняется "быстро".
???
subj, собственно.
5 авг 03, 12:33    [288462]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
>> добавил пробел в комментарии - сохранил - выполняется "быстро". А

может всё гораздо проще ??? :-)

У Вас медленные винты и много оперативки ?
Тогда, когда таблица в целиком памяти - работает быстро.
После джоба она вылетает из оперативки и её надо снова загружать..

В этом случае разница скорости вполне может составлять 2- 5 раз.
Надо провести следственный эксперимент для получения
"чистой картины".
5 авг 03, 13:38    [288614]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
snake
Member

Откуда: Russia, Penza
Сообщений: 2290
может всё гораздо проще ??? :-)

ну, эт уж слишком просто :-)

Тогда, когда таблица в целиком памяти - работает быстро.

боюсь, что тут вся база целиком в памяти :) (это правда так)

После джоба она вылетает из оперативки

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


Надо провести следственный эксперимент для получения "чистой картины".

absolutely agree - я тут погряз в этих следственных экспериментах.
5 авг 03, 13:49    [288645]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
snake
Member

Откуда: Russia, Penza
Сообщений: 2290
тоже интересно https://www.sql.ru/forum/actualtopics.aspx?search=proccache&bid=1
5 авг 03, 14:30    [288717]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
KOLCHOZ_POSTEVENT
Guest
Non cluster index-это некоторая совокупность вспомог.таблиц,разделенных на семейства,именуемые leef labels.
Строки данных таблиц содержат ссылки на строки более старшего leef label,пока те не упрутся ссылкой в таблицу данных.
При вводе,таблицы leef labels пополняются новыми строками,в какой-то момент сервер решает,что новых строк он во вспом.таблицы не добавляет и создает новый leef label.Сервер попадает в ситуацию сложной древообработки и тереят переадресацию,виноватым выходит индекс-он больше за индекс не считается.
Если его кокнуть и сделать заново,то он,свежeнький,стандартно,выходит с 2 вспом.уровнями и тикает,как часы.
5 авг 03, 14:58    [288786]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
KOLCHOZ_POSTEVENT
Guest
Извините,я вместо lawel написал label,ох уж этот cut and past
5 авг 03, 15:01    [288793]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
Сергей АБ
Member

Откуда:
Сообщений: 167
По идее, когда план помещается в кэш, то в некоем заголовке перечисляется служебная информация, с использованием которой и составлялся данный план, как-то: состояниие таблиц (структура, размер, индексы), версия индексов и статистик, и т.д. И как только эти параметры подвергаются сушественным изменениям (с точки зрения сервера), то план считается недействительным, и пересоздаётся.
А планы выполнения (имею ввиду хотя бы Ctrl+K в QA) различны в быстром и медленном вариантах?
5 авг 03, 15:09    [288808]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
Glory
Member

Откуда:
Сообщений: 104760
2KOLCHOZ_POSTEVENT

Хм. А я считал, что в MSSQL индексы организованы так - https://www.sql.ru/articles/mssql/03013101Indexes.shtml

Или под "совокупность вспомог.таблиц,разделенных на семейства,именуемые leef labels" вы имеете ввиду GAM, SGAM и IAM ?

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


2snake
Ну так запрос, который передается sp_executesql тоже ведь надо скомпилировать :). Думаю такая же картина будет и для
exec('exec dbo.someshit ''29'', '' 1'', ''2000'', '' 258'', '' 1'', ''26,29,41,44,52,55''')
5 авг 03, 15:10    [288813]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
snake
Member

Откуда: Russia, Penza
Сообщений: 2290
2Сергей АБ
быстрый и медленный вариант возможны только из приложения.
из QA всегда (или почти всегда) выполняется быстро.
2Glory
т.е. ни чем кроме как дополнительным временем на компиляцию и кэширование sp_executesql не отличается?
Думаю такая же картина будет и для exec ... нет, не такая ;)
в syscacheobjects "кладётся" две записи - Executable Plan и Compiled Plan с objtype Proc. т.е. Compiled Plan с objtype Prepared не кэшируется для вызова exec ('exec dbo...')
5 авг 03, 15:37    [288858]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
vdimas
Member

Откуда: Севастополь
Сообщений: 1147
KOLCHOZ_POSTEVENT прав.

Если в базе начинаются тормоза - надо грохать и создавать заново индексы.
Но самая действенная мера - это отвести для базы минимум 2 файла данных, причем 1 из них - буферный. Время от времени (раз в неделю), я перемещаю ВСЕ таблицы в буферный файл, а потом обратно (совсем недолго такой скриптец наваять). Процедура не быстрая. :) Около часа занимает. Зато потом - всю неделю как на реактивной тяге работает. Попробуйте, понравиться. :)
5 авг 03, 16:05    [288908]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
Glory
Member

Откуда:
Сообщений: 104760
2snake
.е. ни чем кроме как дополнительным временем на компиляцию и кэширование sp_executesql не отличается?
В данной ситуации (относительно плана выполнения) думаю ничем

Думаю такая же картина будет и для exec ... нет, не такая ;)
Ну раз так, то так. Значит так себя ведет только sp_executesql. Про нее точно сказано
"... The contents of stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql."

2vdimas
Если в базе начинаются тормоза - надо грохать и создавать заново индексы.
Прямо так сразу и грохать ?

Время от времени (раз в неделю), я перемещаю ВСЕ таблицы в буферный файл, а потом обратно (совсем недолго такой скриптец наваять).
Наверное у вас проблемы с дефрагментацией данных. Так это можно "лечить" и без перегонки таблиц.
5 авг 03, 16:16    [288933]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
KOLCHOZ_POSTEVENT
Guest
GLORY с уважением(БЕЗ БАЛДЫ)

Я благодарен за LINK-поставил его в favorites.
В главе 4 автор пишет(напоминаю,статья пошла в favorites):
"Некластерный индекс имеет leaf level",от себя добавлю:и не один...
Что касается устойчивости Ваших индексов,а вы уверены
a)Что они non clustered
б)Что ими кто-то пользуется
в)Что они завтра не развалятся.
Я уже говорил,что у меня с умножением-не очень,больше по отниманию и делению.
Была точно такая проблемка,была и нету...
Вобще-то,если про "рыбак-рыбака"...,то вы СПЕЦ.
ПОКА.
5 авг 03, 16:29    [288954]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
Dmitryp
Member

Откуда:
Сообщений: 79
GLORY с уважением(БЕЗ БАЛДЫ)
Вот только вопросик:
DBCC INDEXDEFRAG что - то не нашел !
подскажите ...
5 авг 03, 16:50    [288993]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
Glory
Member

Откуда:
Сообщений: 104760
2KOLCHOZ_POSTEVENT
Насчет leaf level я и не спорил. Я интересовался что такое "совокупность вспомог.таблиц,разделенных на семейства". Какие такие "вспомогательные таблицы" ? extent-ы ? data page-и ?

А по пунктам
а) да, уверен
б) да, пользуются
в) во всяком случае, от того, что я каждый день делаю bulk insert нескольких сот тысяч записей нет, не развалятся. Статистика, планы выполения и дефрагментация после нескольких таких операций конечно же изменятся. Но это же разные вещи.

2Dmitryp
Это обращение ко мне ? И что за вопрос тогда ?
5 авг 03, 17:10    [289038]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
Dmitryp
Member

Откуда:
Сообщений: 79
To :Glory
Можно и к тебе :)
Читал статью по твоей ссылке и встретил DBCC INDEXDEFRAG
набрал в BOL DBCC INDEXDEFRAG и ничего
Может чего не так делаю?
Что такое же DBCC INDEXDEFRAG и где бы его посмотреть?
5 авг 03, 17:19    [289057]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
Glory
Member

Откуда:
Сообщений: 104760
В BOL есть полное описание DBCC INDEXDEFRAG. Это ведь документированная команда.
5 авг 03, 17:22    [289065]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
Муковоз
Member

Откуда: Белгород
Сообщений: 156
Круто !
Столько новой инфы для себя узнал, просто супер!
6 авг 03, 00:06    [289394]     Ответить | Цитировать Сообщить модератору
 Re: Compiled Plan, Executable Plan для Proc, Prepared, Adhoc - у меня крыша едет  [new]
snake
Member

Откуда: Russia, Penza
Сообщений: 2290
в общем проблема действительно была в том,
что в кэше сохранялся не оптимальный план исполнения.
переписали запрос. проблема исчезла.
6 авг 03, 11:22    [289768]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить