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

Откуда:
Сообщений: 167
Привет всем!

Есть хранимая процедура. Сначала она работает быстро ( < 1с ). Потом проходит
определенное время, и по мере того, как люди работают с системой, процедура
начинает работать медленно ( ~1 мин. ). Обычно на следующий день.
Стоит сделать ей ALTER PROCEDURE как опять все начинает работать быстро.

Я так понимаю, что из-за относительно нечастого использования ее план вытесняется
из кеша, потом ее вызывают с какими-то параметрами, назовем их "набор Х", под
которые SQL Server оптимизирует план, потом этот план кешируется, но он не годится
для других параметров. Проблема в том, чтобы воспроизвести это поведение не ожидая
целый день пока оно само произойдет. Как мне узнать "набор Х", для которого
закешировался план?

Или может вообще причина не в этом?
24 дек 15, 14:43    [18604644]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
SergASh
Member

Откуда:
Сообщений: 167
Забыл сказать, что ради эксперимента я скопировал параметры в
локальные переменные в начале процедуры и дальше уже
все операторы работали с локальными переменными.
Но увы, это никак не повлияло на проблему. На следующее утро
опять все работало медленно, а после ALTER PROCEDURE стало быстро.
24 дек 15, 14:56    [18604707]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
Jovanny
Member

Откуда:
Сообщений: 1195
Если процедура используется не часто, то можно сделать её WITH RECOMPILE.
Второе решение - периодически помечать её к рекомпиляции - sp_recompile.
24 дек 15, 16:06    [18605121]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
SergASh
Member

Откуда:
Сообщений: 167
На всякий случай версия сервера
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) 
	Aug 19 2014 12:21:34 
	Copyright (c) Microsoft Corporation
	Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
24 дек 15, 16:45    [18605287]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
SergASh
Member

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

C WITH RECOMPILE все ясно, спасибо, но мне бы понять глубинную причину тормозов.
Главный вопрос как узнать параметры, для которых план закешировался.
24 дек 15, 16:51    [18605323]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
но мне бы понять глубинную причину тормозов


Найдите место процедуры, которое тормозит, и постарайтесь его оптимизировать.
24 дек 15, 17:11    [18605414]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
invm
Member

Откуда: Москва
Сообщений: 9402
SergASh
Главный вопрос как узнать параметры, для которых план закешировался.
Хорошо, узнали.
Чем это поможет выяснить причину падения производительности?
Тем более, что у вас и так план процедуры строится исходя из неизвестных значений параметров.
24 дек 15, 17:15    [18605428]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
SergASh
Member

Откуда:
Сообщений: 167
3unknown,

Не грех было бы вопрос прочитать перед тем как отвечать.

Говорю же, что после ALTER PROCEDURE она перестает тормозить.
Чтобы что-то оптимизировать, нужно сначала увидеть это что-то
медленно работающим. А для этого нужно отловить набор параметров,
на которых был закеширован план. Потому что на тех параметрах, на
которых я ее запускаю для проверки, никаких тормозов нет.
24 дек 15, 17:19    [18605443]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
Konst_One
Member

Откуда:
Сообщений: 11538
запустите профайлер и ловите по имени процедцуры и порогом по времени выполнения
24 дек 15, 17:23    [18605459]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
автор
3unknown,

Не грех было бы вопрос прочитать перед тем как отвечать.


Я прочитал внимательно Ваш вопрос. И обсуждение прочитал. Я понимаю, Вы хотите решить вопрос, не вдаваясь в частности.

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


Для этого нужно не параметры отлавливать, а найти тормозящий кусок кода процедуры. Для этого надо собрать статистику работы процедуры за определенное время. Лучше всего вставить в код логирование по времени, а затем проанализировать результат логирования.
24 дек 15, 17:25    [18605470]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
SergASh
Member

Откуда:
Сообщений: 167
invm
SergASh
Главный вопрос как узнать параметры, для которых план закешировался.
Хорошо, узнали.
Чем это поможет выяснить причину падения производительности?
Тем более, что у вас и так план процедуры строится исходя из неизвестных значений параметров.

Дальше алгоритм такой
1. sp_recompile
2. вызов на найденных параметрах.
тут план кешируется
3. вызов на моих параметрах.
наблюдаю медленную работу.
4. смотрю в план, меняю код процедуры
5. goto 1.

B так пока в пункте 3 не будет домтигнута нужная производительность.
Сейчас я могу выполнить одну такую итерацию в день потому что не знаю параметров для №2
24 дек 15, 17:28    [18605486]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
SergASh
Member

Откуда:
Сообщений: 167
3unknown
Для этого нужно не параметры отлавливать, а найти тормозящий кусок кода процедуры. Для этого надо собрать статистику работы процедуры за определенное время. Лучше всего вставить в код логирование по времени, а затем проанализировать результат логирования.


Это все сделано было, включая хронометраж. Я знаю какой оператор медленно работает.
Но как я могу его оптимизировать, если в лабораторных условиях он работает быстро?!
24 дек 15, 17:32    [18605500]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
автор
Это все сделано было, включая хронометраж. Я знаю какой оператор медленно работает.
Но как я могу его оптимизировать, если в лабораторных условиях он работает быстро?!


Что такое "лабораторные условия"? Добейтесь быстрой работы тормозящего куска без кеширования, и все проблемы решатся.
24 дек 15, 17:35    [18605514]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
SergASh
Member

Откуда:
Сообщений: 167
3unknown,

То ли я плохо объяснил, то ли мы на разных языках говорим...

Лабораторные условия - это когда в SSMS я запускаю процедуру на разных наборах параметров. И все время производительность в норме.
1. Мне не удается придумать такой набор параметров, чтобы на нем процедура работала медленно сразу после sp_recompile
2. Мне не удается придумать такие два набора параметров, чтобы на первом план закешировался, а на втором по закешированному плану процедура сработала медленно.

Однако пользователям удается сделать второе, косвенно разумеется.
24 дек 15, 17:50    [18605560]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
А с очищенным кешем как работает процедура Ваша?
24 дек 15, 17:55    [18605577]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
SergASh
Member

Откуда:
Сообщений: 167
3unknown,

В том-то и дело, что после ALTER PROCEDURE или sp_recompile она работает быстро.
То есть я никак не могу увидеть дают ли что-то мои попытки оптимизировать медленный оператор.
24 дек 15, 18:02    [18605607]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
После читски кеша: https://technet.microsoft.com/en-us/library/ms174283(v=sql.105).aspx
Как процедура ратотает?
24 дек 15, 18:13    [18605668]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
invm
Member

Откуда: Москва
Сообщений: 9402
SergASh
Дальше алгоритм такой
1. sp_recompile
2. вызов на найденных параметрах.
тут план кешируется
3. вызов на моих параметрах.
наблюдаю медленную работу.
4. смотрю в план, меняю код процедуры
5. goto 1.

B так пока в пункте 3 не будет домтигнута нужная производительность.
Сейчас я могу выполнить одну такую итерацию в день потому что не знаю параметров для №2
Вы впустую тратите время.

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

2. При
SergASh
Забыл сказать, что ради эксперимента я скопировал параметры в
локальные переменные в начале процедуры и дальше уже
все операторы работали с локальными переменными.
parameters sniffing не работает и план не зависит от значений параметров.

3. Вам уже советовали - 18605459. Отловите проблемные вызовы и воспроизводите их в SSMS, не забыв обеспечит одинаковые язык, опции соединения и формат даты.
24 дек 15, 18:15    [18605675]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
SergASh
Member

Откуда:
Сообщений: 167
invm

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

Ну естественно, что я убедился в идентичности настроек SSMS и приложения, прежде чем лезть сюда с вопросами.
В кеше всегда не больше одного вхождения для моей процедуры если верить sys.dm_exec_cached_plans.
invm
2. При
SergASh
Забыл сказать, что ради эксперимента я скопировал параметры в
локальные переменные в начале процедуры и дальше уже
все операторы работали с локальными переменными.
parameters sniffing не работает и план не зависит от значений параметров.
Вы думаете я этого не знаю? Однако наблюдаемого поведения это не отменяет. Поэтому в самом первом посте я и спросил, может ли быть другая причина.
invm
3. Вам уже советовали - 18605459. Отловите проблемные вызовы и воспроизводите их в SSMS, не забыв обеспечит одинаковые язык, опции соединения и формат даты.
Это самый дельный совет на сегодня, пробую...
24 дек 15, 18:49    [18605841]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SergASh
А для этого нужно отловить набор параметров, на которых был закеширован план.
...
В кеше всегда не больше одного вхождения для моей процедуры если верить sys.dm_exec_cached_plans.
Если у вас есть "плохой" план, то что вам мешает из него параметры вытащить на которых он был скомпилирован?
24 дек 15, 22:57    [18606978]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SergASh,

Да и вообще, у вас планы то разные получаются у процедуры, до и после sp_recompile ? Ну так сравните их и будет понятно в чем проблема.
24 дек 15, 22:58    [18606986]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 205
Mind,

Дак ТС вроде и задал вопрос:
автор
как узнать параметры, для которых план закешировался.


Вот тут ответ есть: http://www.somewheresomehow.ru/fast-in-ssms-slow-in-app-part2/
24 дек 15, 23:21    [18607046]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
Владислав Колосов
Member

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

если запрос параметризован, попробуйте использовать руководства планов. В этом случае запрос не будет пытаться оптимизировать и создавать новые планы в зависимости от параметров и накапливать их к кэш, в том числе Вы избавитесь от выбора неверного плана из-за прослушивания параметров и принудительных рекомпиляций, как советуют.
25 дек 15, 11:30    [18608356]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про кеширование плана  [new]
Zelius
Member

Откуда: Россия, Москва
Сообщений: 1434
SergASh,

Попробуй найти SELECT который тормозит и добавь к нему option (optimize for unknown)
25 дек 15, 11:48    [18608498]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить