Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как понять рекомпелируется ли план процедуры  [new]
proccompile4
Guest
Как понять рекомпелируется ли план процедуры
25 сен 15, 16:42    [18196002]     Ответить | Цитировать Сообщить модератору
 Re: Как понять рекомпелируется ли план процедуры  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
по профайлеру...по системным представлениям видно использовался ли план повторно
25 сен 15, 16:57    [18196112]     Ответить | Цитировать Сообщить модератору
 Re: Как понять рекомпелируется ли план процедуры  [new]
proccompile4
Guest
Ладно, вопрос меняю на более детальный.

Создаю процедуру с случайным заполнением временной таблицы и с опцией recompile:


автор
create procedure abc as
create table #t (a float, b char(3))


declare @r float
,@i int = 0

set @r = rand()*rand()*10*rand()*10

while @i < @r
begin

-- Make greater than 6 changes to #t
insert #t values (rand(), 'abc')


set @i = @i + 1
end
-- Now reference #t
select count(*) from #t
option (recompile)

select * from #t

go

exec abc

exec abc




Запускаю два и более раз, смотрю в системные представления:

select execution_count, * from sys.dm_exec_procedure_stats
where object_id in (select object_id from sys.objects
where name = 'abc'
)


А там одна запись - один план и execution_count равен кол-ву запуска.

Как так, если я использую option (recompile)? Может быть я не туда смотрю?
25 сен 15, 17:01    [18196134]     Ответить | Цитировать Сообщить модератору
 Re: Как понять рекомпелируется ли план процедуры  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
Recompile у вас относится к конкретному запросу, а не ко всей процедуре.
Планы запросов/модулей с recompile не кешируются.
25 сен 15, 17:11    [18196195]     Ответить | Цитировать Сообщить модератору
 Re: Как понять рекомпелируется ли план процедуры  [new]
o-o
Guest
вы ему делаете query recompile, а не recompile всей процедуры.
а переделайте на
alter procedure abc with recompile 

будет целиком рекомпилировать.
и план за собой вычищать
25 сен 15, 17:14    [18196207]     Ответить | Цитировать Сообщить модератору
 Re: Как понять рекомпелируется ли план процедуры  [new]
proccompile4
Guest
invm, o-o

А как понять, что план рекомпелировался у конкретного запроса?
25 сен 15, 17:22    [18196242]     Ответить | Цитировать Сообщить модератору
 Re: Как понять рекомпелируется ли план процедуры  [new]
SomewhereSomehow
Member

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

select 
	qs.plan_generation_num,
	qs.execution_count,	
	convert(xml,qp.query_plan),
	txt.stmt
from 
	sys.dm_exec_procedure_stats ps
	join sys.dm_exec_query_stats qs on ps.plan_handle = qs.plan_handle
	cross apply sys.dm_exec_text_query_plan (qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) qp
	outer apply sys.dm_exec_sql_text(qs.[sql_handle]) st
	outer apply (
		select 
			substring(
				st.text, 
				(qs.statement_start_offset/2) + 1, 
				((case when qs.statement_end_offset > 0 then qs.statement_end_offset else datalength(st.text) end - qs.statement_start_offset)/2) + 1
			)
	) txt(stmt)
where
	ps.[object_id] = object_id('abc','P')
25 сен 15, 17:24    [18196259]     Ответить | Цитировать Сообщить модератору
 Re: Как понять рекомпелируется ли план процедуры  [new]
proccompile4
Guest
SomewhereSomehow,

Спасибо большое.



Уважаемые коллеги, ответьте пожалуйста еще на один опрос: ре-компилируется ли план выполнения при использовании в процедуре\функции временной таблицы.

Прочитал статью:
https://support.microsoft.com/en-us/kb/243586

Там в частности утверждается, что при использовании временной таблицы у нас план перекомпилируется и приведен пример:
use pubs
go
drop procedure RecompProc 
go
create procedure RecompProc as
create table #t (a int)
select * from #t
go
exec RecompProc



Но вот я запускаю запрос SomewhereSomehow после N-ого запуска процедуры из примера и вижу, что executon_count вырос, а plan_generation_num остался прежним. Почему?

Пробовал на MS SQL 2012 SP1 и MS SQL 2014.
25 сен 15, 17:58    [18196444]     Ответить | Цитировать Сообщить модератору
 Re: Как понять рекомпелируется ли план процедуры  [new]
o-o
Guest
proccompile4
Пробовал на MS SQL 2012 SP1 и MS SQL 2014.

А они пробовали на 2000-ом, база pubs :)
В конце там написано applies to sql server 7, 2000.
Вот на них прокатит.
А начиная с 2005 уже появился statement level recompilation,
что вы уже видели в своем примере.
Не всю процедуру перекомпилируют.
25 сен 15, 18:48    [18196743]     Ответить | Цитировать Сообщить модератору
 Re: Как понять рекомпелируется ли план процедуры  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
proccompile4
Но вот я запускаю запрос SomewhereSomehow после N-ого запуска процедуры из примера и вижу, что executon_count вырос, а plan_generation_num остался прежним. Почему?


Кэширование и рекомпиляция в общем – довольно сложный механизм, а кэширование временных таблиц – еще более сложный, с множеством нюансов.

В вашем примере из статьи работает кэширование временных таблиц, поэтому, серверу не надо каждый раз перестраивать план.
автор
Даже в приведенной вами статье написано:
Note that if you execute this particular stored procedure multiple times, SQL Server will reuse the existing plan for this procedure. You will only see the recompile event on the first execution of the procedure, or if you drop and re-create the procedure each time you execute the script.

Правда статья старенькая и кое чего в ней нет.

Вот на вашем же примере.

Я оставил процедуру ту же самую, только добавил в нее два параметра, первый создает индекс на таблице – операция DDL отключает кэширование, вторая вставляет в таблицу число строк большее чем порог рекомпиляции. Кроме того, я добавил в сам запрос подзапрос (select 1), который предотвращает тривиальный план, т.к. с тривиальным планом отдельная история. Также добавил вывод ИД объекта временной таблицы.

Еще добавил процедуру, которая просто выводит plan_generation_num и execution_count.

+
use tempdb;
go
--------------------------------
-- diagnostic proc
if object_id('diag') is not null drop procedure diag;
go
create proc diag as
select 
	qs.plan_generation_num,
	qs.execution_count
from 
	sys.dm_exec_procedure_stats ps
	join sys.dm_exec_query_stats qs on ps.plan_handle = qs.plan_handle
where
	ps.[object_id] = object_id('RecompProc','P')
go
--------------------------------
-- test proc
if object_id('RecompProc') is not null drop procedure RecompProc;
go
create procedure RecompProc 
	@disable_caching bit = 0, 
	@recompilation_thershold int = 0
as
create table #t (a int);

	-- test options
	if @disable_caching = 1 create index ix on #t(a);
	if @recompilation_thershold > 0 insert #t select top(@recompilation_thershold) number from master..spt_values where type = 'P';
	select object_id('tempdb..#t');
	-- 

select *, (select 1) from #t;
go


Тест 1.

--------------------------------
-- 1. 
dbcc freeproccache;

-- plan_generation_num = 2, execution_count = 1
exec RecompProc @disable_caching = 0, @recompilation_thershold = 0;
exec diag;
go
-- plan_generation_num = 2, execution_count = 2
exec RecompProc @disable_caching = 0, @recompilation_thershold = 0;
exec diag;
go


plan_generation_num = 2 – один, это при создании плана процедуры с заглушками для временных объектов, второй при первом выполнении и рекомпиляции когда дело доходит до временной таблицы. Обратите внимание на одинаковые ИД объектов, при двух разных вызовах хранимой процедуры. Хотя кажется, что при каждом вызове должна создаваться новая временная таблица (как написано в коде), на деле работает кэширование и структура используется повторно.

Тест 2.

----------------
-- 2.
dbcc freeproccache;

-- plan_generation_num = 2, execution_count = 1
exec RecompProc @disable_caching = 1, @recompilation_thershold = 0;
exec diag;
go
-- plan_generation_num = 2, execution_count = 2
exec RecompProc @disable_caching = 1, @recompilation_thershold = 0;
exec diag;
go


Отключим кэширование при помощи операции DDL. Видим, что теперь ИД объектов разные, но этого недостаточно для рекомпиляции, plan_generation_num = 2 по прежнему

Тест 3.

----------------
-- 3.
dbcc freeproccache;

-- plan_generation_num = 3, execution_count = 1
exec RecompProc @disable_caching = 1, @recompilation_thershold = 1;
exec diag;
go
-- plan_generation_num = 4, execution_count = 1
exec RecompProc @disable_caching = 1, @recompilation_thershold = 1000;
exec diag;
go

-- plan_generation_num = 5, execution_count = 1
exec RecompProc @disable_caching = 1, @recompilation_thershold = 1;
exec diag;
go


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

Появляется по две строки из диагностической процедуры – первая добавилась из-за того, что появился запрос на вставку строк – на ее мы не смотрим. Смотрим на последнюю и видим, что да, происходят рекомпиляции при каждом вызове (plan_generation_num = 3,4,5), когда число строк значительно меняется, т.е. переваливает за порог рекомпиляции, при этом число выполнений равно 1, т.к. план новый.

В целом, тема довольно сложная много всяких нюансов. Если захотите «поломать голову», вот вам очень интересные статейки:
https://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx
http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx
http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx
25 сен 15, 21:53    [18197438]     Ответить | Цитировать Сообщить модератору
 Re: Как понять рекомпелируется ли план процедуры  [new]
proccompile4
Guest
SomewhereSomehow,

спасибо за развернутый и подробный ответ.



SomewhereSomehow
Появляется по две строки из диагностической процедуры – первая добавилась из-за того, что появился запрос на вставку строк – на ее мы не смотрим. Смотрим на последнюю и видим, что да, происходят рекомпиляции при каждом вызове (plan_generation_num = 3,4,5), когда число строк значительно меняется, т.е. переваливает за порог рекомпиляции, при этом число выполнений равно 1, т.к. план новый.


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

Прочитал про хинты keep plan и keepfixed plan, соорудил пробный пример:

create procedure dbo.initialization
as 
declare @r float = rand()
if @r > 0 and @r < 0.333
insert into #t(a)
select 1
option (KEEPFIXED PLAN )
--option (keep plan)
else if @r > 0.333 and @r < 0.666
insert into #t(a)
select 2
union all
select -1
union all
select -2
union all
select -3
union all
select -4
option (KEEPFIXED PLAN )
--option (keep plan)
else 
insert into #t(a)
select 3
union all
select 10
option (KEEPFIXED PLAN )
--option (keep plan)
go




create procedure RecompProc as
create table #t (a int)
exec dbo.initialization
select * from #t

go

dbcc freeproccache
go
--Запускаем процедуру N раз:
exec RecompProc
go 40


select 
       qs.plan_generation_num,
       qs.execution_count, 
       object_name(ps.[object_id]),
       convert(xml,qp.query_plan),
       txt.stmt
from 
       sys.dm_exec_procedure_stats ps
       join sys.dm_exec_query_stats qs on ps.plan_handle = qs.plan_handle
       cross apply sys.dm_exec_text_query_plan (qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) qp
       outer apply sys.dm_exec_sql_text(qs.[sql_handle]) st
       outer apply (
             select 
                    substring(
                           st.text, 
                           (qs.statement_start_offset/2) + 1, 
                           ((case when qs.statement_end_offset > 0 then qs.statement_end_offset else datalength(st.text) end - qs.statement_start_offset)/2) + 1
                    )
       ) txt(stmt)
where
       ps.[object_id] in (object_id('RecompProc','P') 
       ,  object_id('initialization','P')     )


Но, увы, keepfixed plan не работает для этого примера, каждый новый вызов процедуры initialization - перекомпиляция.
30 сен 15, 15:32    [18215368]     Ответить | Цитировать Сообщить модератору
 Re: Как понять рекомпелируется ли план процедуры  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
proccompile4
каждый новый вызов процедуры initialization - перекомпиляция.
В initialization #t - внешняя временная таблица. Перекомпиляция будет всегда.
30 сен 15, 15:54    [18215554]     Ответить | Цитировать Сообщить модератору
 Re: Как понять рекомпелируется ли план процедуры  [new]
proccompile4
Guest
SomewhereSomehow,

спасибо за ссылки, очень интересно
30 сен 15, 16:37    [18215885]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить