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

Откуда:
Сообщений: 233
Дано: джоба из двух шагов.
на первом выполняется exec sp_updatestats
на втором хп_1, в которой вызывается хп_2. В хп_2 переливается из таблички в табличку порядка 1,5*10^6 строк, создаются индексы и временные таблицы. Время выполнения хп_2 в теле хп_1 порядка 12 минут. И хп_1, и хп_2 созданы с хинтом recompile, вызываются exec ... with recompile.

Найти: почему в случае предварительного dbcc freeproccache время выполнения хп_2 сокращается до 2 минут? Что такого особенного делает freeproccache, чего не делает with recompile? если верить msdn, разница только в охвате. И разница во времени была бы понятна, но в хп_2 больше ничего не вызывается, только запросы select, insert и create index.
28 авг 12, 10:38    [13074085]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
MyNiGoo,

Если задействованы временные таблицы, то возможно проблема в кэшировании статистик, но с другой стороны, вы говорите что процедуры созданы with recompile. При этом, ничего кэширвоаться по-идее не должно. В любом случае, хотелось бы получить действительные планы быстрого и медленного выполнения.
28 авг 12, 11:19    [13074346]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
посмотрел профайлером, нашел самый тяжелый запрос. Планы по нему абсолютно идентичные (событие Showplan All For Query Compile)
28 авг 12, 14:07    [13075985]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
есть вот такое мнение, но я не использую DBCC DROPCLEANBUFFERS. Играет ли это разницу и насколько близко к действительности?
28 авг 12, 14:21    [13076120]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
MyNiGoo
посмотрел профайлером, нашел самый тяжелый запрос. Планы по нему абсолютно идентичные (событие Showplan All For Query Compile)

Так это ж оценочный план. Вам нужно посмотреть Showplan Statistics Profile или лучше Showplan XML Statistics Profile.
Кстати, попробуйте включить еще Sort Warnings, на всякий случай, когда будет выполняться исследуемый запрос.
28 авг 12, 14:52    [13076427]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
В Showplan XML Statistics Profile расхождение есть только в количестве строк на поток, все инструкции одни и те же. Присутствует SP:CacheMiss, а Sort Warnings попался на несколько запросов позже тормозного.
28 авг 12, 15:57    [13077127]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
причем процедура отрабатывает также достаточно быстро, а вот через пару дней постепенно опять выйдет на большую продолжительность.
28 авг 12, 16:12    [13077240]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
MyNiGoo,

У вас план параллельный чтоль?
А расхождений оценки с фактом нету?
28 авг 12, 16:13    [13077256]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
да, параллельный. Промашка есть, в одном hash Match (Aggregate)
Estimated Rows 365 443
Actual Rows 250 880
28 авг 12, 16:26    [13077368]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
это верно и для быстрого, и для медленного плана
28 авг 12, 16:32    [13077424]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Такая промашка, по-идее, не должна оказывать сильного влияния.
MyNiGoo
причем процедура отрабатывает также достаточно быстро, а вот через пару дней постепенно опять выйдет на большую продолжительность

Так в итоге сейчас все работает нормально? Тогда непонятно, зачем вообще сравнивать сейчас, если ситуация не вопроизводится.
28 авг 12, 16:38    [13077488]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
неделю назад я впервые в жизни сделал базе freeproccache и с удивлением увидел, что время выполнения хп резко сократилось (с 12 до 2 минут). К настоящему моменту в течение этой недели я наблюдал увеличение продолжительности выполнения хп, пока она не вышла на прежние значения (12 минут). Я снял трассу при медленном выполнении и стал экспериментировать - сначала с опциями recompile (не помогло), потом с повторным freeproccache. Периодическое выполнение freeproccache мне не кажется хорошей практикой, поэтому я обратился на форум за помощью в анализе.
28 авг 12, 16:54    [13077667]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
Mind
Member

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

Если задействованы временные таблицы, то возможно проблема в кэшировании статистик, но с другой стороны, вы говорите что процедуры созданы with recompile. При этом, ничего кэширвоаться по-идее не должно.
По-идее не должно. Но у разработчиков из MS были другие идеи. Можно хоть закомпилироваться, но статистики на временных таблицах все равно не пересчитаются. Вот нашел такой пример:
+
set nocount on
set statistics time off
set statistics io off
drop table tab7
go
create table tab7 (c1 int primary key clustered, c2 int, c3 char(200))
go
create index test on tab7(c2, c1, c3)
go
begin tran
declare @i int
set @i = 1
while @i <= 50000
begin
insert into tab7 values (@i, 1, 'a')
set @i = @i + 1
end
commit tran
go
insert into tab7 values (50001, 1, 'a')
go
checkpoint
go

drop proc test_slow
go
create proc test_slow @i int
as
begin
declare @j int
create table #temp1 (c1 int primary key)
insert into #temp1 (c1) select @i
select @j = t7.c1 from tab7 t7 inner join #temp1 t on (t7.c2 = t.c1)
end
go

dbcc dropcleanbuffers
set statistics time on
set statistics io on
go
--high reads as expected for parameter '1'
exec test_slow 1
go

dbcc dropcleanbuffers
go
--high reads that are not expected for parameter '2'
exec test_slow 2
go

drop proc test_with_recompile
go
create proc test_with_recompile @i int
as
begin
declare @j int
create table #temp1 (c1 int primary key)
insert into #temp1 (c1) select @i
select @j = t7.c1 from tab7 t7 inner join #temp1 t on (t7.c2 = t.c1)
option (recompile)
end
go

dbcc dropcleanbuffers
set statistics time on
set statistics io on
go
--high reads as expected for parameter '1'
exec test_with_recompile 1
go

dbcc dropcleanbuffers
go
--high reads that are not expected for parameter '2'
--low reads on 3rd execution as expected for parameter '2'
exec test_with_recompile 2
go

drop proc test_with_alter_table_recompile
go
create proc test_with_alter_table_recompile @i int
as
begin
declare @j int
create table #temp1 (c1 int primary key)
--to avoid caching of temporary tables one can create a constraint
--but this might lead to duplicate constraint name error on concurrent usage
alter table #temp1 add constraint test123 unique(c1)
insert into #temp1 (c1) select @i
select @j = t7.c1 from tab7 t7 inner join #temp1 t on (t7.c2 = t.c1)
option (recompile)
end
go

dbcc dropcleanbuffers
set statistics time on
set statistics io on
go
--high reads as expected for parameter '1'
exec test_with_alter_table_recompile 1
go

dbcc dropcleanbuffers
go
--low reads as expected for parameter '2'
exec test_with_alter_table_recompile 2
go

drop proc test_with_index_recompile
go
create proc test_with_index_recompile @i int
as
begin
declare @j int
create table #temp1 (c1 int primary key)
--to avoid caching of temporary tables one can create an index
create index test on #temp1(c1)
insert into #temp1 (c1) select @i
select @j = t7.c1 from tab7 t7 inner join #temp1 t on (t7.c2 = t.c1)
option (recompile)
end
go

set statistics time on
set statistics io on
dbcc dropcleanbuffers
go
--high reads as expected for parameter '1'
exec test_with_index_recompile 1
go

dbcc dropcleanbuffers
go
--low reads as expected for parameter '2'
exec test_with_index_recompile 2
go
Правда по признакам описываемым ТС не похоже что проблема из-за этого. Прозводительность постепенно падает в течение нескольких дней. С кривой статистикой я бы ожидал увидеть другую картину, хотя все может быть. Можно попробовать создать индексы на временных таблицах, чтобы исключить кэширование и не сохранять старые статистики.
28 авг 12, 20:01    [13078574]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
MyNiGoo
неделю назад я впервые в жизни сделал базе freeproccache и с удивлением увидел, что время выполнения хп резко сократилось (с 12 до 2 минут). К настоящему моменту в течение этой недели я наблюдал увеличение продолжительности выполнения хп, пока она не вышла на прежние значения (12 минут). Я снял трассу при медленном выполнении и стал экспериментировать - сначала с опциями recompile (не помогло), потом с повторным freeproccache. Периодическое выполнение freeproccache мне не кажется хорошей практикой, поэтому я обратился на форум за помощью в анализе.
Вообще проблема выглядит весьма интересной и хотелось бы докопаться до причин.

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

Из того что я знаю, разница между freeproccache и with recompile должна быть только в том, что freeproccache прибивает кэши временных таблиц, возможно проблема все таки в том.

А как часто у вас вызывается хп_1?
28 авг 12, 20:10    [13078603]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Mind
По-идее не должно. Но у разработчиков из MS были другие идеи. Можно хоть закомпилироваться, но статистики на временных таблицах все равно не пересчитаются. 

Вы про option recompile, а я про создание процедуры with recompile.
SomewhereSomehow
MyNiGoo,
Если задействованы временные таблицы, то возможно проблема в кэшировании статистик, но с другой стороны, вы говорите что процедуры созданы with recompile. При этом, ничего кэширвоаться по-идее не должно.

Когда процедура создана таким образом, временные таблицы не кэшируются, как и их статистики. В вашем же примере добавьте create proc proc1 with recompile as... - чтения исчезнут.
Но думаю, теперь, это имеет не такое большое значение. Т.к. выяснились подробности, которых не было в первом сообщении про временные таблицы (из-за чего я первоначально предположил, что виновато кэширование).
По этому, требуются собственно планы. Быстрый и медленный. Ну и общий мониторинг сервера, что вообще происходит.
28 авг 12, 20:36    [13078677]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
Mind
можно выделить один запрос время которого увеличивается, к примеру, с 1 минуты до 11

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

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

А может как-то freeproccahe влиять на буфер данных?
28 авг 12, 21:15    [13078757]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SomewhereSomehow
Mind
По-идее не должно. Но у разработчиков из MS были другие идеи. Можно хоть закомпилироваться, но статистики на временных таблицах все равно не пересчитаются. 

Вы про option recompile, а я про создание процедуры with recompile.
Точно. Ваша правда.
28 авг 12, 21:46    [13078812]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
хп_1 - процедура ETL, вызывается во время отсутствия пользователей раз в сутки. Иногда запускается днём по требованию.
запрос имеет две очевидных проблемы - ненужный distinct и джойн с таблицей без индексов, созданной этой же хранимкой чуть выше. distinct убрал, таблицу заменил на таблицу с индексами из источника. План стал симпатичнее, погляжу на производительность.
а пока старый план из медленной трассы во вложении

К сообщению приложен файл (plan.xml.zip - 12Kb) cкачать
28 авг 12, 21:46    [13078813]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
MyNiGoo
Mind
можно выделить один запрос время которого увеличивается, к примеру, с 1 минуты до 11

Именно так и есть.
Про параллельный план и промашку с кардинальностью (если я верно употребил термин) выше я писал именно про конкретный запрос из хп.
Именно по этому запросу медленный и быстрый планы выполнения идентичны с точностью до количества строк на поток.
Вы таки планы выложите или нам за кофе идти?
MyNiGoo
А может как-то freeproccahe влиять на буфер данных?
Не должно. Разве что резко появляется дополнительная свободная память под буфер, но что-то сомнительно, что это может быть причиной. Версия сервера кстати какая?
28 авг 12, 21:53    [13078846]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
+ @@version
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
28 авг 12, 21:56    [13078859]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
Mind
Вы таки планы выложите или нам за кофе идти?

в предыдущем сообщении в 21:46. Брал из события Showplan XML Statistics Profile
28 авг 12, 21:58    [13078874]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
MyNiGoo
Mind
Вы таки планы выложите или нам за кофе идти?

в предыдущем сообщении в 21:46. Брал из события Showplan XML Statistics Profile
Да, я пока писал, вы уже выложили.

Пока что мистика какая-то. Есть предположение, что Key Lookup по BudgetItems плохо делает префетч данных при каких то определенных обстоятельствах. Таблица весьма тяжелая по размеру строки. По оценкам -12Кб, чего вы туда такого напихали, все поля NVARCHAR(500) ? Более того сервер почему то решает делать сик+лукап, хотя выбираются почти все данные, что тоже весьма странно. Итого большой объем данных + рэндомные чтения могут привести к плохой производительности. Можно проверить это предположение сохранив промежуточный результат джойна BudgetItems с BudgetGroups во временную таблицу с кластерным индексом по ID. Естественно из BudgetItems нужно вытягивать только ID. А потом эту временную нужно еще раз джойнить с BudgetItems чтобы получить остальные поля, таким образом должен получится merge или hash, ну или на зудой конец все тот же loops join, но уже по отсортированным ID.
Кстати что у вас с фрагментацией клестерного индекса на BudgetItems?

Как вы сравнивали хороший и плохой планы? Вы уверены что они идентичны?

Сколько памяти на сервере и сколько съденено под кэш процедур, когда все медленно выполняется?

И еще, не понятно почему в плане Hash Match, у вас там что DISTINCT стоит?
29 авг 12, 00:32    [13079297]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
По кластерному у BudgetItems Фрагментация 0,01 Page Fulness 95,92

План сравнивал дважды через TortoiseMerge и модуль Notepad++ Compare =)

Как я писал выше, от лукапа я избавился. В результате одни Index Scan/Seek и Hash Match.
Distinct тоже убрал. Производительность запроса, конечно же, улучшилась в принципе. Но это же всё равно не раскрывает механизм магического воздействия freeproccache :-(

Помониторю ситуацию и параллельно отвечу на ваш вопрос
Mind
Сколько памяти на сервере и сколько съденено под кэш процедур, когда все медленно выполняется?


+ Типы колонок для справки
datetime 1
decimal(24,12) 26
int 7
nvarchar(max) 9
timestamp 1
uniqueidentifier 1
29 авг 12, 09:53    [13079943]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
MyNiGoo,

В итоге. Сейчас какая ситуация? Удалось ли воспроизвести такое поведение? Т.е. два запроса. Запускаем один, работает медленно, запускаем другой работает быстро. Или нет?
Или ситуация такая, что запрос один, постепенно, с течением времени начинает работать все медленнее и медленнее и в какой-то момент доходит до 12 минут, потом делаем freeproccache, и снова работает две минуты? Судя по приведенному плану, временные таблицы в проблемном запросе не участвуют?
29 авг 12, 10:42    [13080301]     Ответить | Цитировать Сообщить модератору
 Re: Разница между freeproccache, recompile, sp_recompile  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
SomewhereSomehow, верно, всё вот так:
SomewhereSomehow
запрос один, постепенно, с течением времени начинает работать все медленнее и медленнее и в какой-то момент доходит до 12 минут, потом делаем freeproccache, и снова работает две минуты

временные таблицы в проблемном запросе не участвуют
29 авг 12, 11:08    [13080566]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить