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

Откуда: Moscow Square
Сообщений: 637
SELECT @@VERSION
Microsoft SQL Server 2005 - 9.00.5000.00 (X64)   Dec 10 2010 10:38:40   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) 

В последнее время часто начала появляться блокировка типа [COMPILE] на одной и той же хранимой процедуре (см. вложение)

Насколько я понял, этот тип блокировки возникает, когда хранимка отсутствует в кеше планов, и её постоянно приходится компилировать. Посмотрел её в кеше:
select cp.*
from sys.dm_exec_cached_plans cp
	cross apply sys.dm_exec_query_plan(cp.plan_handle) qp
where qp.objectid = 1200133695
  AND qp.dbid = 11

Действительно в плане нет.

Нарыл пару статей
http://www.pythian.com/blog/sql-server-compile-locking-and-encryption-keys/
https://support.microsoft.com/en-us/kb/263889

Там написано, что это может возникать при несовпадении хозяина и вызывающего, а также при неуказании полного имени.
Хранимая процедура вызывается с сайта под специальным логином с урезанными правами, так что первый пункт очевиден. Однако, вызывается она уже несколько лет под этим логином, а проблемы появились сравнительно недавно. Аналогично и со вторым пунктом, вызов идет через обертку sp_executesql и с полным именем, включающим схему и имя.

В майкрософтовской статье указано как профилировать этот случай, брать события:
RPC:Starting
SP:CacheMiss
SP:ExecContextHit(SP:CacheHit)
SP:Starting

Я запрофилировал, событие SP:CacheMiss отсутствует, идут подряд события
RPC:Starting
SP:CacheHit
SP:Starting

Подскажите куда ещё надо копать? Блокировки напрягают, доходя периодически до 5 секунд

К сообщению приложен файл. Размер - 2Kb
18 янв 16, 16:56    [18697109]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
step_ks
Member

Откуда:
Сообщений: 936
Plan Caching in SQL Server 2008
в частности, раздел Identifying Recompilations
18 янв 16, 21:54    [18698569]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 901
Профайлер запускаем, событие фильтруем на SP:Recompile, смотрим столбец EventSubClass
, далее узнаем причину перекомпиляции.
В BOL есть таблица.
19 янв 16, 09:57    [18699738]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4974
Oblom,

Может вам запрос параметризировать надо, чтобы его план кешировался ?
19 янв 16, 14:14    [18701337]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 637
a_voronin,

Запрос параметризирован, это хранимая процедура, с параметрами.
Перекомпиляции там нет, ещё раз профайлером убедился. Там именно что каждый раз план компилируется с нуля.
Проблема не в перекомпиляции, там всё понятно. Проблема в том, что одна конкретная процедура не кешируется, при этом по событию CacheMiss она не ловится.
19 янв 16, 17:36    [18702618]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
o-o
Guest
Oblom
одна конкретная процедура не кешируется

может, она создана WITH RECOMPILE option?
BOL
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure and the procedure is compiled at run time
19 янв 16, 17:59    [18702735]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
Crimean
Member

Откуда:
Сообщений: 13147
чего-то вы недоговариваете

> Перекомпиляции там нет, ещё раз профайлером убедился

вы уверены? по-моему вот этих событий:

> В майкрософтовской статье указано как профилировать этот случай, брать события:
> RPC:Starting
> SP:CacheMiss
> SP:ExecContextHit(SP:CacheHit)
> SP:Starting
>
> Я запрофилировал, событие SP:CacheMiss отсутствует, идут подряд события
> RPC:Starting
> SP:CacheHit
> SP:Starting

слегка недостаточно
опять же, по Starting событиям можно же вычислить на каком именно стейтменте происходит ожидание. воспроизведение постоянное или редкое? или постоянное - давно бы уже поймали
19 янв 16, 18:03    [18702751]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 901
Oblom,

вы поймали причину рекомпиляции
SP:Recompile
процедуры 11:1200133695?
Она вам создает блокировку, причин рекомпиляции много.
Временные таблицы есть в процедуре?
19 янв 16, 18:12    [18702791]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
Crimean
Member

Откуда:
Сообщений: 13147
> вы поймали причину рекомпиляции
> SP:Recompile

вы же видели - он эти события даже не собирает :)
на всякий случай напомню про Event SubClass
19 янв 16, 18:25    [18702829]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
o-o
Guest
если процедура создана с with recompile,
SP:Recompile не срабатывает.
правда ловится SP:CacheMiss
19 янв 16, 18:37    [18702869]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 637
Crimean,

Я добавил в трассу событие SP:Recompile, фраза "Перекомпиляции там нет, ещё раз профайлером убедился." это подразумевала.
Между сообщением, которое цитировали вы и этой фразой прошли сутки. Время достаточное, чтобы поставить отдельную трассу на рекомпиляцию.
Инструкция WITH RECOMPILE отсутствует.
За идею по событию Starting стейтментов хранимки найти место компиляции спасибо, сейчас займусь. Но мне почему-то кажется, что компилится вся хранимая процедура, а не какой-то конкретный стейтмент.

Основной вопрос - тип блокировки [COMPILE] имеет причиной рекомпиляцию?
Все предыдущие ответы в этой теме указывают на это. Все наблюдения показывают обратное. Рекомпиляций у меня на сервере тьма, проблема только с этой хранимкой.

И да, я в курсе про Event SubClass и перекомпиляции при работе с времянками. Только нет здесь перекомпиляций, а блокировка [COMPILE] есть.
20 янв 16, 10:10    [18704341]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
step_ks
Member

Откуда:
Сообщений: 936
SQL: StmtRecompile для кучи еще помониторьте
20 янв 16, 10:22    [18704397]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 901
step_ks
SQL: StmtRecompile для кучи еще помониторьте

кстати, да.
рекомпиляция должна попадаться в профайлер, значит что-то не то снимаете.

У вас в запросе не используются связанные сервера?

только нет здесь перекомпиляций, а блокировка [COMPILE] есть.

если есть такое ожидание, значит то и есть.
Посмотрите по кешу статистику ваших планов, когда компиляция и частота их использования.
Компилируется стейтмент кода, а не вся процедура, если она не простая, это уже давно вроде с 2005.
20 янв 16, 12:36    [18705286]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
o-o
Guest
Oblom
https://support.microsoft.com/en-us/kb/263889

Там написано, что это может возникать при несовпадении хозяина и вызывающего, а также при неуказании полного имени.
Хранимая процедура вызывается с сайта под специальным логином с урезанными правами, так что первый пункт очевиден. Однако, вызывается она уже несколько лет под этим логином, а проблемы появились сравнительно недавно. Аналогично и со вторым пунктом, вызов идет через обертку sp_executesql и с полным именем, включающим схему и имя.

В майкрософтовской статье указано как профилировать этот случай, брать события:
RPC:Starting
SP:CacheMiss
SP:ExecContextHit(SP:CacheHit)
SP:Starting

Я запрофилировал, событие SP:CacheMiss отсутствует, идут подряд события
RPC:Starting
SP:CacheHit
SP:Starting

вы знаете, в той статье немного не то написано, что вы поняли.
а именно, там пишут не "это может возникать при несовпадении хозяина и вызывающего, а также при неуказании полного имени".
статья относится к версиям 7, 2000 и 2005, и они(авторы kb) еще не привыкли схему называть схемой,
но по сути там речь о схемах и о пропущенной схеме при вызове процедуры.
т.е. овнеры там ни при чем, при чем схема: есть она или нет в вызове,
и всю их фразу надо понимать так:
это (CacheMiss) случается тогда, когда вызывающий не указал схему,
и его дефолтная схема не совпадает со схемой процедуры.
т.к. первая попытка в разрешении имени процедуры при вызове товарищем Harry вида "exec mystoredproc"
будет не dbo.mystoredproc, а Harry.mystoredproc.
и это будет the initial cache lookup by object name fails.

теперь вернемся к вашему случаю.
описанное в kb не ваш случай.
в kb пишут:
при таком раскладе, когда вызывающий не указал схему,
и его дефолтная схема не совпадает со схемой процедуры,
в профайлере вы будете наблюдать:
RPC:Starting mystoredproc
SP:CacheMiss mystoredproc
SP:ExecContextHit mystoredproc
SP:Starting mystoredproc
где SP:CacheMiss указывает на то, что первая попытка найти процедуру по имени в процедурном кэше обломалась.

ну и далее вы пишете, что у вас-то:
событие SP:CacheMiss отсутствует, идут подряд события
RPC:Starting
SP:CacheHit
SP:Starting
и они далее разъясняют:
SP:ExecContextHit или SP:CacheHit -- план процедуры БЫЛ НАЙДЕН
--------------------------------------------------------------------------
что мне непонятно, так это почему сервером он был найден, а вами нет.
повторюсь, если план в кэше не найден, будет в явном виде SP:CacheMiss.
это очень просто воспроизвести,
например это происходит при самом первом запуске процедуры
или при каждом запуске процедуры с recompile.

так что все нормально, что вы не находите перекомпиляции.
ненормально то, что вы план не находите, в то время как он есть и сервер его видит
20 янв 16, 13:12    [18705517]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
Crimean
Member

Откуда:
Сообщений: 13147
вы бы все же инфы навалили без ваших инерпретаций. у меня, скажем, было похожее раза два. первый раз была проблема с кешем планов на UDF из-за разных connection settings разных приложений. второй раз - таки банальные компиляции, к которым приводило агрессивное обновление статистик. оба раза достаточно оперативно ловилось и лечилось. внешне выглядело достаточно похоже.
извините, 2005 скуля уже нет под рукой, смоделировать ваше поведение не смогу
20 янв 16, 14:05    [18705958]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 637
o-o,

Спасибо!
Поставил WITH RECOMPILE, поймал SP:CacheMiss. Убрал WITH RECOMPILE, событие SP:CacheMiss осталось. Значит до этого неправильно ловил. Но SP:CacheMiss идет на всю процедуру, не на стейтмент.

Ещё раз взял все рекомпиляции без фильтра по событиям
SP:Recompile
SQL: StmtRecompile
Нет по этой хранимке рекомпиляций в те моменты когда проявляется SP:CacheMiss

Куда ещё можно копать?
20 янв 16, 14:29    [18706116]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
Crimean
Member

Откуда:
Сообщений: 13147
памяти мало? вытесняется?
20 янв 16, 19:12    [18707817]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
o-o
Guest
когда вытесняются, то сразу куча, а тут прямо любимец вытеснений.
может, там внутри BULK INSERT? такое содержащее вообще не кэшируется
20 янв 16, 20:53    [18708030]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 637
o-o, Crimean

Памяти всегда мало )

да, дело именно внутри хранимки. BULK нету. А что ещё есть некешируемое в принципе?
21 янв 16, 10:01    [18709129]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка типа [COMPILE]  [new]
o-o
Guest
перечислено в статье Plan Caching in SQL Server 2008
в частности,
The execution contexts for bulk insert statements are never cached.
A batch that contains any one literal longer than 8 KB is not cached.
Therefore, query plans for such batches cannot be reused. (A literal's length is measured after constant folding is applied.)
21 янв 16, 10:50    [18709314]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить