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

Откуда: Москва
Сообщений: 1014
Добрый день,

Столкнулся с проблемой выявления истиных причин удаления планов исполнения триггеров из процедурного кеша.

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

Текущая ситуация:
- Имеем кучу проблем с различными настройками соединений, что конечно уменьшает варианты переиспользования планов генерирую дубликаты, временные таблицы в процедурах вызывающих триггеры, и еще пара пунктиков. Но это не главная тема.
- Имеем довольно большое Indernal Pressure в Procedure Cache, что конечно же выливается в вымывание планов и кеша. Но по логике сразу должны вымываться мало используемые Ad-Hoc планы, например. Уточнение - множество Ad-Hoc запросов по стоимости (>100) сильно превышает стоимость планов триггеров (у которых cost часто ~ 6-10).

Как выглядит проблема:
В профайлере наблюдаю следующую картину на события катательно триггера:
SP:CacheHit
SP:CacheRemove [EventSubClass: 1 - Compplan Remove]
SP:CacheRemove [EventSubClass: 1 - Compplan Remove] (удаление часто наблюдается для нескольких одинаковых планой (видимо удаляется все планы этого триггера или ольшенство, так как одновременно в кеше их млое количество))
SP:CacheRemove [EventSubClass: 1 - Compplan Remove] (то же самое моет быть несколько раз)
SP:CacheInsert

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

До этих дейстрий в профайтере нет никаких прямых указаний на причину удаления из кеша.
Но, врешняя процедура довольно часто перекомпелируется. Хотя не похоже, что это может выщвать перекомпиляцию триггеров.

Есть один стремный ньюанс, что из таблиц inderted и deleted данные выгрибаются используя *. И это в процесси оптимизации сейчас.
Хотя событий в трассе по тому, что это влиет на вымывание свных нет.

Лично мне кажеться, что планы триггеров у SQL Server в моей базе лучшие кандидаты на вымывание при нехватке памяти согласно стоимостной политики. Но пока у меня не достаточно доказатольств.

Вопрос:
- Сталкивался ли кто то с такой рода проблемой?
- И как можно точно выяснить причину такого поведения?
- Есть ли какие то еще зависимости в удалении планов триггеров из кеша от каких то внешних факторов (соединения часто закрываются)?

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

Спасибо за любую информацию.
14 ноя 12, 03:24    [13468920]     Ответить | Цитировать Сообщить модератору
 Re: Triggers - SP:CacheRemove (мутная проблема)  [new]
Crimean
Member

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

а пример траса посмотреть можно? было достаточно похожее под 2008. полечилось унификацией некоторых параметров connection settings. но были именно пары Remove + Insert в основном для UDF, но проскакивали (при определенных раскладах) и триггера и хранимки. причем именно Remove + Insert, а не просто "вымывание". коллеги из MS тоже сначала говорили "отстаньте, у вас мемори преша". самостоятельные раскопки показали что из-за баги в сервере из кеша выкидывались все планы без анализа тех самых сетов (в доке прописано ровно обратное). соответственно, при активной конкуренции процессы больше стояли на ожидании COMPILE при наличии хотя бы 1 активного процесса с отличными от других сетами
как ровнять сеты - дело сугубо интимное, благо средств более чем
14 ноя 12, 11:44    [13470243]     Ответить | Цитировать Сообщить модератору
 Re: Triggers - SP:CacheRemove (мутная проблема)  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1014
Спасибо за коментарии Crimean.

У меня SQL Server 2008 R2 SP2.

Если можно - укажи на конкретный номер бага и фикс, если такой есть?
Очень странно, что Microsoft бъет себя пяткой в ргудь и трубит на всех углах о механизме вымывания из кеша (надо подтвердить как то эти заявление).

Согласно твоим коментариям я, честно говоря, пока на стороне Microsoft о том, что это Internal Memory Pressure.
Так как, то, что ты описал (Compile Locks) это нормальное поведение при отсутствии плана в кеше, и SQL Server блокирует схему наглухо, что бы скомпилировать план. Что же в этом не естественного? И у меня на удивление очень похожая ситуация.
Но, то, что этого плана не оказывается в кеше - вот в чем вопрос. Если я почти каждый раз вижу SP:CacheHit и после этого покатилось...

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

Но это не снимает мой вопрос - как и дентифицировать точную причину такого поведения не применяя метод научного тыка?

По твоему запросу я постарался найти участок трассы с этим поведением, но он, конечно не всесь, так, что я не знаю, чем он сможет помочь в отличие от моих предыдущих описаний.
В трассе четко видно, что после удачного SP:CacheHit сервер удаляет 2 плана из кеша (SP:CacheRemove).
То, что он удаляет больше чем один, согласно той же документации говорит о том, что если процесс компилирующий план выявил недостаток места в кеше то он удалит столько сколько надо (до 16 объектов за раз). Причем Microsoft подчеркивает, что это выполняется на том же thread, что и интересующий нас процесс (видимо тот же самый SPID). Это меня и наводит на мысль о вымывании. Но нахрена же готовый план удалять и компилить его заново. Именно тут мне и нужна помощь в понимании - что же происходит, так как только трассой мне обойтись не получается.

Может надо что то еще конкретное из трассы?


Все здравые мысли приветствуются.

К сообщению приложен файл. Размер - 89Kb
15 ноя 12, 05:32    [13475940]     Ответить | Цитировать Сообщить модератору
 Re: Triggers - SP:CacheRemove (мутная проблема)  [new]
Crimean
Member

Откуда:
Сообщений: 13147
а что, кеш инсерты не включены в трас?
15 ноя 12, 11:41    [13476960]     Ответить | Цитировать Сообщить модератору
 Re: Triggers - SP:CacheRemove (мутная проблема)  [new]
Crimean
Member

Откуда:
Сообщений: 13147
вот "мой" сценарий:

создаем udf

use tempdb
go

create function dbo.f1 () returns int as begin return 1 end
go


первый процесс

use tempdb
go

set arithabort off
set quoted_identifier off
go

set nocount on
go

while 1=1 begin

declare @i int
set @i = dbo.f1 () 

end


второй процесс

use tempdb
go

set arithabort on
set quoted_identifier on
go

set nocount on
go

while 1=1 begin

declare @i int
set @i = dbo.f1 () 

end
go


пускаем трас (для наглядности) на кеш инсерт + кеш ремув + рекомпайл. видим картинку, пробуем объяснить :)
проверял на "Microsoft SQL Server 2008 R2 (SP1) - 10.50.2806.0 (X64)"

стоит говорить, что я на сервере один, ресурсов там - "залейся", больше активности нет?

К сообщению приложен файл. Размер - 40Kb
15 ноя 12, 11:56    [13477081]     Ответить | Цитировать Сообщить модератору
 Re: Triggers - SP:CacheRemove (мутная проблема)  [new]
Glory
Member

Откуда:
Сообщений: 104751
????

The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.
15 ноя 12, 12:03    [13477150]     Ответить | Цитировать Сообщить модератору
 Re: Triggers - SP:CacheRemove (мутная проблема)  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1014
To Crimean:
SP:CacheInsert-ы включены и есть пониже в трассе.
Так, что все получается в точности как у тебя.
В таком случае все идеалы разрушены. В рот мне ноги...
Пойду проверю это сам еще раз, что бы окончательно подтвердить.

To Glory:
Оптимизация под Ad-Hoc не избавит от блокировок компиляции, как ты сам понимаешь.
Хотя должно понизить Internal Procedure Cache Pressure.
Уже как 2 месяца в тестировании у нас.
И спасибо за коментарий.


Если кто знает какой то обход этой жопы - буду благодарен за вклад.
16 ноя 12, 00:51    [13481318]     Ответить | Цитировать Сообщить модератору
 Re: Triggers - SP:CacheRemove (мутная проблема)  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1014
To Crimean:

Тестировние показало, что UDF действительно принудительно выпихиваются из кеша при разных настройках соединения.
Даже при учеты, что функции завернуты в процедуру, где, казалось бы если не меняем SET настройки - все в норме должно быть. Но нек тут то было.
Согласен, что с UDF - это похоже на баг. Скорректируйте, если это фича :-) (шутка).

С триггерами и другими вложеными процедурами такого поведения не наблюдается!
Играясь с настройкми соединения закрывая и открывая последние периодически происходит выпихивания планов и вставка (периодически), но шаблона выявить не удалось.
В итоге - твой основной аргумент тут не подходит, так как относиться только к UDF.
Пока та же моя теория та же самая - вымывание из процедурного кеша.


Открыт для коментариев и мыслей.
16 ноя 12, 06:24    [13481553]     Ответить | Цитировать Сообщить модератору
 Re: Triggers - SP:CacheRemove (мутная проблема)  [new]
Crimean
Member

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

"баг" - это когда идут "парные" Remove / Insert в случае конкурентного использования. если же просто Remove - это, скорее, "вымывание". у меня нет простого репро для "бага" с триггерами и хранимками, но "наживую" я это тоже видел. просто "быстро" повторить не могу, да и не нужно после подбора эффективного "обхода" для этой бяки

и, извините, ваш трас совершенно "непоказательный" в свете вышеописанного, а версию с сетами вы не подтверждаете и не опровергаете. да, у вас как минимум 2 конкурентных приложения - но что с их сетами? там около 6 сетов критично для такого прведения кеша

как минимум нет евент сабкласса (почему его выкинули) + странно выглядит 2 (!) ремува сразу после хита для указанного триггера в пределах одного SPID

и - да, Glory предлагал "ту самую" опцию как средство снизить потребление памяти ад-хоками, которых у вас есть - это видно в трасе. и, как результат, возможно, снизить вероятность "вымывания", если это "вымывание".
16 ноя 12, 11:51    [13482726]     Ответить | Цитировать Сообщить модератору
 Re: Triggers - SP:CacheRemove (мутная проблема)  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1014
Спасибо за коменты Crimean.

У меня как раз и идут партые или и того больше SP:CacheRemove-ы. На продуктиве по 3-5 удалений подряд с одного соединения бавает.

Я не патаюсь перевести стрелки на баг. Может это и не баг вообще. Просто хочеться понять - как это четко продиагностировать. Так как на данном этапе все замылино - разными натройками соединений и мистическим Internal Cache Pressure.

По поводу трассы - я говорил, что она совсем не показателена, так как причин такого опведения в нем, лично мне, ну вообще не видно. Или очень туманно. Если ты омеешь ввиду что-то конкретное под настройками трассы - сообщи. Я прогоню и посмотрим.

По поводу сетов - подтверждаю, что разные сеты не выпихивают "все" планы триггеров из кеша, а только лишь генерируют новые. С UDF это однозначно принудительное выпихивание "всех", как ты и сказал.

Нет SubEventClass с причиной выкидывания - как это нет? В этом примере это "1 - Compplan Remove". А о причине может говорить только предыдущая строчка в трассе, которая в моем случае SP:CacheHit, что совсем все путает. Так как - план типа есть, но его и нет...

2 неестественных SP:CacheRemove - как я уже и писал это похоже на Internal Cache Pressure, так как удаление лишних планов производится на том же thread что и выполнение текущего запроса. Так, что это похоже естественно и только лишь это мне указывает на выпихивание кешей из памяти (не принимая во внимание разные счетчики).

Сонекты с разными сетами (пока это печально), это в процессе регулирования. Но это не снимает основного вопроса.

С Glory я согласен, что и подтвердил в коментах (это уменьшит Internal Cache Pressure).
Я знаю - как обойти эту "бяку", тут нет вопросов и не было.
Просто в этом тепике я спрашивал про методы диагностирования такого поведения (опять же те же яйца - вид в профиль). Так как мне нужны конкретные доказательства под переделку тех или иных кусков кода.
21 ноя 12, 01:44    [13504454]     Ответить | Цитировать Сообщить модератору
 Re: Triggers - SP:CacheRemove (мутная проблема)  [new]
Crimean
Member

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

а какие события вы включаете в трас? и - собственно полный трас дадите на посмотреть?
(я надеюсь в вашем трасе есть сабкласс?)
мемори преша - если есть - уточняется по показателям памяти в сиквеле
21 ноя 12, 11:24    [13505646]     Ответить | Цитировать Сообщить модератору
 Re: Triggers - SP:CacheRemove (мутная проблема)  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1014
Здравствуй Crimean и все, кто читает это пост.

Все таки получилось выудит из трасы что именно вызывает выбрасывание планов из кеша.
В большегстве случаев это 2 типа запросов:

1. С приложения
UPDATE nc_client SET update_datetime = {ts '2013-04-08 14:39:35.600'}, update_user = 'HAYDEN.WANSBROUGH', update_process = 'CMS2' WHERE seq_party_id = 2455481 

Который автопараметризирован в следующую форму
(@1 datetime,@2 varchar(8000),@3 varchar(8000),@4 int)UPDATE [nc_client] set [update_datetime] = @1,[update_user] = @2,[update_process] = @3  WHERE [seq_party_id]=@4

2. Из процедуры
UPDATE cl
      SET seq_pay_method_id = chq.seq_pay_method_id,
          update_datetime = GetDate(),
          update_user = Host_Name(),
          update_process = 'CCEXPIRED'
     FROM dbo.crm_activity_type typ
     JOIN inserted              ins ON typ.seq_act_type_id = ins.seq_act_type_id
     JOIN dbo.nc_client         cl  ON ins.seq_party_id = cl.seq_party_id
     JOIN dbo.nc_payment_method pm  ON cl.seq_pay_method_id = pm.seq_pay_method_id
                                   AND pm.pay_method_code = 'CRDCARD'
    CROSS JOIN
          dbo.nc_payment_method chq
    WHERE chq.pay_method_code = 'CHEQUE'
      AND typ.act_type_code = 'Credit Card Expired'


Трасса прикреплена к сообщению.
В ней я посторался собрать 3 основных блока каторые связаны между собой (красным обозначены разрывы в трассе):
- зеленым цветом обозначен план триггера который выкидывается из сеша EventSubClass - 3 QueryStats
- синим указано предыдущее событие, которое инвалидировало тот самый план выполнения триггера EventSubClass - 3 SP:Plan (поиск был по предыдущему такому же PlanHandle и Offset + IntegerData2)
- фиолетовым показан ближайщое событие на SPID которое инвалидировало тот самый план исполнения триггера.

Спрашиваю вашего мнения - может что критичное явно видно из этого анализа?
И как могут выбрасываться планы выполнения триггеров когда другие процессы обнавляют ту самую таблицу где висит триггер?
Есть подозрение на то, что на выбрасывание планов влияет колическо записей в INSERTED и DELETED таблицах, но из-за чего идет выталкивание не совсем понятно, так как теоретически несколько планов могут сосуществовать одновременно для триггера. Или я тут не прав?
В некоторых триггерах так же есть функции. Вспомнил это, так как ранее упоменали, что функции выбрасываются при разных connection settings, но выбрасываются все стейтменты из триггеров, даже те, которые не содержат функций.

Основная проблема в том, что у меня не выходит пока повторить ситуацию локально, да и потенциальных проблем, которые могут влиять на выкидывание есть не мало.
Хочеться все таки увелиить шансы что правится то что надо а не наугад убирать все теоретические проблемы.

Заранее благодарен за коментарии.

К сообщению приложен файл. Размер - 89Kb
9 апр 13, 09:04    [14154682]     Ответить | Цитировать Сообщить модератору
 Re: Triggers - SP:CacheRemove (мутная проблема)  [new]
Crimean
Member

Откуда:
Сообщений: 13147
если возможно - временно отключите распаралеливание
и - проверьте доступность памятишки
9 апр 13, 10:38    [14155101]     Ответить | Цитировать Сообщить модератору
 Re: Triggers - SP:CacheRemove (мутная проблема)  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1014
Распаралеливание отключить возможности нет. Хмммм, но надо попробовать этот вариант как то проверить. Я не уверен, как себя ведут триггеры при изменении не паралельного плана на паралельный.

С памятью все сложно. Ее много, по есть Memory Pressure в CACHESTOREs (все из-за ad-hokс сеша, с процедурным не так все плохо, хотя они и делят общий ресурс памяти выделеный сервером под пулы компилированых запросов). Мы часто на границе срабатывания. Проверено согласно формул приведеных в источниках от Microsoft. Но выталкивание планов триггеров похоже не связано с этим, так как прослеживается четкий патерн (определенные запросы выталкивают планы триггеров (хотя триггеры имеют довольно большую стоимость (64-2000 для current cost) и механизм выталкивания не должен сразу же выпихивать их)).

Чего то у меня заканчиваются идеи.
Благодарен за любой вклад ребята.
10 апр 13, 03:31    [14159653]     Ответить | Цитировать Сообщить модератору
 Re: Triggers - SP:CacheRemove (мутная проблема)  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1014
Что то меня наводит на мысть что собака зарыта в INSERTED и DELETER таблицах триггеров при сильном изменении количества строк:

автор
When the AUTO_UPDATE_STATISTICS database option is SET to ON, queries are recompiled when they target tables or indexed views whose statistics have been updated or whose cardinalities have changed significantly since the last execution. This behavior applies to standard user-defined tables, temporary tables, and the inserted and deleted tables created by DML triggers. If query performance is affected by excessive recompilations, consider changing this setting to OFF. When the AUTO_UPDATE_STATISTICS database option is SET to OFF, no recompilations occur based on statistics or cardinality changes, with the exception of the inserted and deleted tables that are created by DML INSTEAD OF triggers. Because these tables are created in tempdb, the recompilation of queries that access them depends on the setting of AUTO_UPDATE_STATISTICS in tempdb. Note that in SQL Server 2000, queries continue to recompile based on cardinality changes to the DML trigger inserted and deleted tables, even when this setting is OFF. For more information about disabling AUTO_UPDATE_STATISTICS, see Using Statistics to Improve Query Performance.


http://msdn.microsoft.com/en-nz/library/ms181055(v=sql.105).aspx

Но как подтвердить это пока не ясно. Устаревшей статистики на INDERTED и DELETED вроде бы не пахнет. Или я не прав?
Отключать AUTO_UPDATE_STATISTICS не вариант.
Может тут как то может помочь KEEPFIXED PLAN?
11 апр 13, 07:54    [14165119]     Ответить | Цитировать Сообщить модератору
 Re: Triggers - SP:CacheRemove (мутная проблема)  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1014
Это точно не Memory Pressure так как статистика сработывания cache_clock_hands не частая (согласно представления sys.dm_os_memory_cache_clock_hands). Пару раз в час может быть (согласно параметра ronds_count).
Да и то события идут со стороны HAND_EXTERNAL. Похоже буферный пул периодически требудет больше места перераспределяя кеши (точно не от OS, так как используем lock pages in memory). Но точно не из-за роста самих процедурных кешей.

Информация с этого ресурса:
https://www.simple-talk.com/blogs/2010/04/01/the-clock-hands-of-the-buffer-cache/
The hands do relate to memory pressure; the cache "eviction policy" is determined by both global and local memory pressures on SQL Server. The "external" clock hand responds to global memory pressure, in other words pressure on SQL Server to reduce the size of its memory caches as a whole. Global memory pressure – which just to confuse things further seems sometimes to be referred to as physical memory pressure – can be either external (from the OS) or internal (from the process itself, e.g. due to limited virtual address space). The internal clock hand responds to local memory pressure, in other words the need to reduce the size of a single, specific cache. So, for example, if a particular cache, such as the plan cache, reaches a defined "pressure limit" the internal clock hand will start to turn and a memory sweep will be performed on that cache in order to remove plans from the memory store.

К сообщению приложен файл. Размер - 83Kb
12 апр 13, 05:09    [14170610]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить