Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
Crimean
Member

Откуда:
Сообщений: 13147
как следствие - куча процессов ждет компиляции, в основном на ad-hoc, но они на представления, представления на udf, все это жутко "пляшет" в кеш / из кеша. CU7 ставить сразу или тоньше отстраиваться? в трассе массовых рекомпиляций нет при этом
6 май 11, 20:26    [10619346]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
Crimean
Member

Откуда:
Сообщений: 13147
нашел. ждите описание в очередной KB
11 май 11, 10:39    [10633386]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
Crimean
Member

Откуда:
Сообщений: 13147
ладно, MS тормозят по полной. ситуация - если есть конкурирующие конекты, обращающиеся к одним и тем же объектам БД и у коннектов разные сеттинги - планы объектов из кеша выбиваются для всех (!) процессов.
то есть
пришел процесс 1. сеты 1. вызвал объект 1. получил кеш плана 1
пришел процесс 2. сеты 2. вызвал объект 1. должен получить кеш плана 2. но! убивает кеш плана 1 для процесса 1 и ставит в кеш свой план 2. ессно, когда процесс 1 хочет опять вызвать объект 1 - все идет по-новой

касается 2008 и 2008 R2 систем. саппорты сваливают это все на memory pressure, но это нифига не оно. отличительный признак - в профайлере будет куча cache remove / cache insert. а в случае обычного memory pressure - будут только cache insert
ну и внешне процессы стоят на sos-scheduler + ждут компиляций на чем попало. на таблицах, на объектах и т.д.
ка кследствие - серверу "хуже всех", поскольку компиляции не конкурентны. то есть у вас загружено 1 ядро и привет. все стоит, процы (остальные ядра) как и вся железка - курят, очереди на ресурсы огромные и т.п.
в общем весело
касается в первую очередь UDF, но захватывает еще как минимум триггера, но уже более хитро. скорее всего затрагивает и остальные объекты, но наиболее очевидно косячит именно на UDF
16 июн 11, 18:03    [10823965]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
Crimean,

кейз в CSS открывали?
Репро просто сделать?
Могу разработчикам "форварднуть"...
16 июн 11, 20:02    [10824429]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
Crimean
Member

Откуда:
Сообщений: 13147
девы в курсе, в SQL11 пофиксено, в 2008/R2 "впадлу" было фиксить - команды не было. есть внутренний кейз.
я кейз открыл в саппорте. саппорты "динамят" второй месяц уже. регионалы сначала имитировали бурную деятельность, потом ушли на мороз. хоть как-то сдвинулось после того, как ихним же саппортам дал ихний же внутренний кейз. до того был один ответ "by design".
репро в 3 строки: делаем UDF и пускаем в 2 ветках ее вызов в бесконечном цикле с разными сетами, например арифаборт
видим cache remove / insert постоянные. пускаем > 2 веток и наслаждаемся состоянием процессоров и прочими сопутствующими. напоминаю про мои поиски временнЫх затрат - они именно тут были.
репро для триггеров сложнее - там не все сеты влияют и вымывается кеш не для всех, а только для последнего коннекта, который пришел с другими сетами перед "контрольным". причем важен именно "переход" состояния сета. с арифабортом точно есть
для хранимок не упрощал до 2 строк, но проскакивают и они в продакшене
попутно нашел, но не упрощал еще, паразитные рекомпиляции типа 1 для объектов, работающих с tempdb, хотя код "идеальный"
в общем "оптимизнули" 2008 от души, как оказалось..
16 июн 11, 20:31    [10824491]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
Александр Волок (def1983)
Member

Откуда: Rotterdam
Сообщений: 4959
Свои пять копеек:

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

Factors that affect plan-reuse:
...

SET options

As already mentioned in Section 6, changing one or more of the following SET options after a batch has started execution will cause a recompilation: ANSI_NULL_DFLT_OFF, ANSI_NULL_DFLT_ON, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, DATEFIRST, DATEFORMAT, FORCEPLAN, LANGUAGE, NO_BROWSETABLE, NUMERIC_ROUNDABORT, QUOTED_IDENTIFIER.

Best Practice: To avoid SET option-related recompilations, establish SET options at connection time, and ensure that they do not change for the duration of the connection.
...

SET options. Some of the SET options affect query results. If the setting of such a plan-reuse-affecting SET option is changed inside of a batch, a recompilation happens.

т.е. это:

пришел процесс 1. сеты 1. вызвал объект 1. получил кеш плана 1
пришел процесс 2. сеты 2. вызвал объект 1. должен получить кеш плана 2. но! убивает кеш плана 1 для процесса 1 и ставит в кеш свой план 2. ессно, когда процесс 1

чистой воды рекомпиляция.

ну, а Вам же, господа, известно, что одна sql комманда (после возможной автопараметризации) может иметь максимум 2 инстанса планов в кеше:
1) для серийного выполнения
2) для паралельного выполнения (появляется, ествественно, не автоматически, а лишь в следствие факта самого выполнения)

Поэтому, собственно, вы и наблюдаете вытеснение старого плана новым.

Другое дело, если бы SQL Server для каждого адхок выполнения хранил отдельный план (а не execution context), как было в 2005 (до SP2) но тогда были проблемы с быстрым заполнением процедурного кэша всяким хламом...
16 июн 11, 22:12    [10824866]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
Ну с одной стороны вроде да, но с другой - зачем вытеснять - то?
Мне эта штука (set рекомпиляция) в свое время немало крови попила.
16 июн 11, 22:25    [10824914]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
Александр Волок (def1983)
Member

Откуда: Rotterdam
Сообщений: 4959
DeColo®es
Ну с одной стороны вроде да, но с другой - зачем вытеснять - то?
Мне эта штука (set рекомпиляция) в свое время немало крови попила.


Потому что эти SET'ы влияют на конечный результат.

Грубо говоря, при изменении SET параметра старый план (скомпилированный с предыдущим SET значением) уже не пригоден для выполнения, т.к. он теоретически может вернуть некорректные (для нового set значения) результаты.

SET настроек тоже ведь много.. и если разрешать сохранять план под конкретный вариант SET настроек, то конечное количество одного и того же плана может увеличиться с максимум двух, как сейчас, до 196 (14 в квадрате) + 2. Что не есть хорошо...
16 июн 11, 22:35    [10824961]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
Александр Волок (def1983)
Member

Откуда: Rotterdam
Сообщений: 4959
Александр Волок (def1983)
SET настроек тоже ведь много.. и если разрешать сохранять план под конкретный вариант SET настроек, то конечное количество одного и того же плана может увеличиться с максимум двух, как сейчас, до 196 (14 в квадрате) + 2. Что не есть хорошо...

Пардон, до (2 в 14 степени, количество SET параметров) * 2 (серийный, паралельный) что равно 32 768

Что врядли MS допустят..
16 июн 11, 22:39    [10824983]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
Александр Волок (def1983)
Александр Волок (def1983)
SET настроек тоже ведь много.. и если разрешать сохранять план под конкретный вариант SET настроек, то конечное количество одного и того же плана может увеличиться с максимум двух, как сейчас, до 196 (14 в квадрате) + 2. Что не есть хорошо...

Пардон, до (2 в 14 степени, количество SET параметров) * 2 (серийный, паралельный) что равно 32 768

Что врядли MS допустят..
Знаете, если подсчитать количество возможных текстов запросов, то их намного больше, чем 2 в 14 степени * 2. Это же не повод, например, хранить кеш только для одного текста запроса :-)

Не вижу никаких причин удалять кеш при изменении настроек. Просто стандартные правила - старый кеш вытесняется новым. Это, конечно, приведёт к вымыванию кеша "по текстам", но это нестрашно.

Это просто обычная бага, просто кто-то не подумал, прежде чем программировать...

Но всё равно для 2008 и 2008 R2 не исправят - изменения серьёзные, а в денали это уже поправлено.
Так что можно расслабиться :-)

Собственно, очень редко возникает необходимость работать под разными SET-ами, видимо поэтому и не правили раньше.
17 июн 11, 09:15    [10825959]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
Crimean
Member

Откуда:
Сообщений: 13147
скалярные UDF - суть хранимки, ага? я не трогаю табличные инлайны. триггера тоже суть хранимки, ага? так какого одни хранимки спокойно живут себе в кеше, а вторые - то туда то обратно то туда то обратно? в ОДНОЙ ситуации, ага? да еще и с раскладами хитрыми?

подготовка

use master
go

create database a1
go

use a1
go

create table dbo.t1 ( id int primary key, flag int )
go

insert into dbo.t1( id, flag ) select id, id from sysobjects
go

create function dbo.f1 ( @id int ) 
returns int 
as 
begin
declare @r int
select top 1 @r = id from dbo.t1 where @id is null or @id = id
return @r
end
go

create procedure dbo.p1 @id int = null as
set nocount on
select top 2 id, flag from dbo.t1 where id = ( select dbo.f1( @id ))
return 1
go

первая ветка

use a1
go

SET ARITHABORT off
go

set nocount on
while     1=1        exec dbo.p1
go

вторая ветка

use a1
go

SET ARITHABORT on
go

set nocount on
while     1=1        exec dbo.p1
go

пробуйте. и расскажите мне разницу между p1 и f1
17 июн 11, 09:45    [10826063]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
Crimean
Member

Откуда:
Сообщений: 13147
alexeyvg
Собственно, очень редко возникает необходимость работать под разными SET-ами, видимо поэтому и не правили раньше.


кроме ситуаций когда с одной базой работает куча систем разных вендоров через разные либы. никогда не пробовали "внешними" средствами (не трогая экзешника) повлиять на конекшен сеттинги разных прог? попробуйте! очень увлекательно. тот же арифаборт поставить в sqlncli, к примеру.
17 июн 11, 09:47    [10826074]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
Crimean
Member

Откуда:
Сообщений: 13147
2 Александр Волок (def1983)

> Грубо говоря, при изменении SET параметра старый план (скомпилированный с предыдущим SET значением)
> уже не пригоден для выполнения, т.к. он теоретически может вернуть некорректные (для нового set значения) результаты.

да что вы говорите?? то есть процесс 1 имеет свои сеты 1, получил свои планы для объектов и работает себе. тут приходит другой процесс с другими сетами и оказывается, что процессу 1 его старые планы больше нельзя использовать?? класс!
17 июн 11, 09:49    [10826085]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
НеПонимаю
Guest
Crimean
пробуйте. и расскажите мне разницу между p1 и f1


А в чем хитрость?

select @@VERSION

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2425.0 (Intel X86)
Apr 6 2011 21:10:02
Copyright (c) Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
17 июн 11, 10:47    [10826444]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
НеПонимаю
Guest


К сообщению приложен файл. Размер - 55Kb
17 июн 11, 10:48    [10826449]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
Crimean
Member

Откуда:
Сообщений: 13147
НеПонимаю
А в чем хитрость?


в поведении кеша. запускаем профайлер и смотрим за cache insert / remove, которые есть если сеты разные и нет, если сеты одинаковые. ну и как следствие - на изменение поведения сервера и на увеличение времени выполнения запросов
17 июн 11, 11:32    [10826770]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
Crimean
alexeyvg
Собственно, очень редко возникает необходимость работать под разными SET-ами, видимо поэтому и не правили раньше.


кроме ситуаций когда с одной базой работает куча систем разных вендоров через разные либы. никогда не пробовали "внешними" средствами (не трогая экзешника) повлиять на конекшен сеттинги разных прог? попробуйте! очень увлекательно. тот же арифаборт поставить в sqlncli, к примеру.
Не, я понимаю, но это всё таки можно отнести к проблемам коннекта к сиквелу, а не к самому сиквелу. Вот в микрософте и относят :-)
Ведь можно было и правильно клиента написать. Повлиять на конекшен сеттинги разных прог можно, если в прогах разработчики предусмотрели возможность влияния. Необязательно кодить так, что сеттинги остаются тайной даже для разработчиков проги :-)

Я тоже сразу назвал это багой; я просто рассказал, почему именно МС не будет править эту багу. Но могу на всякий проголосовать, если ссылку дадите на коннект.
17 июн 11, 12:52    [10827551]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
Crimean
Member

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

> это всё таки можно отнести к проблемам коннекта к сиквелу, а не к самому сиквелу.

нет. ибо либы рулят сетами сами (1) и для разных задач нужны разные сеты (2). перечислять не буду, ладно?

> Ведь можно было и правильно клиента написать.

и всяческие "линки" и прочие обертки - тоже?? да ладно! :)

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

ага, щаз, кому-то надо null + '' = null, кому-то = '', кому-то надо на "1/0" отвал батча, а кому-то - в лог ошибку после этого записать. продолжать?

> почему именно МС не будет править эту багу

ой, не надо начинать. почему статистики перестали ренеймиться в 2008 или почему мультисерверный дата коллекшен не работает в р2 или..

> Но могу на всякий проголосовать, если ссылку дадите на коннект

будет и в коннекте, если до того дойдет, но уже 2 номера инцидентов есть. внутренний МС-ный и внешний мой у них же как саппорт инцидент
17 июн 11, 12:59    [10827642]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
Crimean
для разных задач нужны разные сеты
Вот это я и назвал "редкий случай"
Crimean
либы рулят сетами сами
...
всяческие "линки" и прочие обертки - тоже?? да ладно!
Ну, в общем, согласитесь, что это недостаток обёрток/либов. Как можно применять средства для работы с БД, которые программист не может контролировать???

И собственно, противоречие с п. 1 - для разных задач нужны разные сеты, но повлиять на них вы не можете.
Нестыковочка :-)

Ещё насчёт обёрток/либов. Большинство приложений - это одна программа, работающая с одной базой, которая использует одну либу. Соответственно, для большинства проблем нету. Я собственно только про это хотел сказать, не надо меня убеждать, что это бага, я и так это знаю.
17 июн 11, 13:06    [10827722]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
Crimean
Member

Откуда:
Сообщений: 13147
база - "моя". клиентский софт - нет + он от разных вендоров. спеки давали как набор хранимок / представлений. а ходят всем от ODBC до jTDS. базовое, конечно, SQLNCLI, но кому от этого легче? web / win / ... прикладуха + межсерверные взаимодействия
все это нормально работало под 2000 и загнулось под р2
17 июн 11, 13:23    [10827919]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
Александр Волок (def1983)
Member

Откуда: Rotterdam
Сообщений: 4959
Crimean
2 Александр Волок (def1983)

> Грубо говоря, при изменении SET параметра старый план (скомпилированный с предыдущим SET значением)
> уже не пригоден для выполнения, т.к. он теоретически может вернуть некорректные (для нового set значения) результаты.

да что вы говорите?? то есть процесс 1 имеет свои сеты 1, получил свои планы для объектов и работает себе. тут приходит другой процесс с другими сетами и оказывается, что процессу 1 его старые планы больше нельзя использовать?? класс!


Ну дак что планы уже привязываются к процессам?
17 июн 11, 15:37    [10829178]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
Александр Волок (def1983)
Member

Откуда: Rotterdam
Сообщений: 4959
Crimean
все это нормально работало под 2000 и загнулось под р2


Что под 2000 каждая сессия, имеющая отличные сет значения имела свой план??

Или же скл сервер 2000 удавалось использовать план с Set arith_abort on в сессии где эта опция отключена???

Не верю (с)


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

Майкрософт пишет ведь:

Best Practice: To avoid SET option-related recompilations, establish SET options at connection time, and ensure that they do not change for the duration of the connection.
17 июн 11, 15:45    [10829274]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Александр Волок (def1983)
Майкрософт пишет ведь:

Best Practice: To avoid SET option-related recompilations, establish SET options at connection time, and ensure that they do not change for the duration of the connection.


так у меня и нет смены сетов "for the duration of the connection". или репро мы не выполняли, в профайлер мы не смотрели? каждый отдельно взятый конекшен работает "статично" в смысле сетов. беда в том, что приходит ДРУГОЙ конекшен и как только он что-то вызывает - "хорошо" становится всем уже существующим конекшенам
17 июн 11, 16:04    [10829523]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
Александр Волок (def1983)
Member

Откуда: Rotterdam
Сообщений: 4959
Crimean
Александр Волок (def1983)
Майкрософт пишет ведь:

Best Practice: To avoid SET option-related recompilations, establish SET options at connection time, and ensure that they do not change for the duration of the connection.


так у меня и нет смены сетов "for the duration of the connection". или репро мы не выполняли, в профайлер мы не смотрели? каждый отдельно взятый конекшен работает "статично" в смысле сетов. беда в том, что приходит ДРУГОЙ конекшен и как только он что-то вызывает - "хорошо" становится всем уже существующим конекшенам


Угу, согласен. Тогда я дополню..

SET значения должны (судя по логике и документации разроботчика) быть одинаковы для ВСЕХ соединений чтобы планы повторно использовались, причем не только в пределах одной сессии, но и другими соединениями, а иначе - рекомпиляции..
17 июн 11, 16:31    [10829841]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2008 R2 CU2 вымывается кеш планов без видимых причин  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Crimean
> Повлиять на конекшен сеттинги разных прог можно, если в прогах разработчики предусмотрели возможность влияния.
> Необязательно кодить так, что сеттинги остаются тайной даже для разработчиков проги :-)

ага, щаз, кому-то надо null + '' = null, кому-то = '', кому-то надо на "1/0" отвал батча, а кому-то - в лог ошибку после этого записать. продолжать?



Crimean - вот ето как по мне таки бардак, ты просто разбаловал своих вендоров . И если уж так сильно надо - то писть пищут сеты в хранимках и не парят мозки.
17 июн 11, 17:40    [10830626]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить