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

Может ли временные таблицы привести к перекомпиляции?

SQL SERVER 2014
14 дек 15, 23:39    [18560195]     Ответить | Цитировать Сообщить модератору
 Re: Перекомпиляция хранимых процедур при использоании временных таблиц  [new]
SomewhereSomehow
Member

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

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

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

Далее откроем профайлер, выберем событие SQL:StmtRecompile и выполним скрипт.
+
use opt;
go
------------------------------------------------------------------------------------------
-- 1. Таблица #t заполняется, но не используется
create proc p_1_notused @a int
as
create table #t(a int, b int);
insert #t(a,b)
select t1.a, t2.b from t1 join t2 on t1.a = t2.b where t1.a <= @a;
go

-- 2. Таблица #t заполняется, но используется в тривиальном плане (решения на основе статистики не принимаются)
create proc p_2_trivial @a int
as
create table #t(a int, b int);
insert #t(a,b)
select t1.a, t2.b from t1 join t2 on t1.a = t2.b where t1.a <= @a;
select count(*) from #t t where t.b > 1; -- <-- Trivial Plan
go

-- 3. Таблица #t заполняется и используется
create proc p_3_full @a int
as
create table #t(a int, b int);
insert #t(a,b)
select t1.a, t2.b from t1 join t2 on t1.a = t2.b where t1.a <= @a;
select count(*) from #t t join t1 on t1.a = t.a where t.b > 1;
go

------------------------------------------------------------------------------------------

-- 1.
-- 1.1
exec p_1_notused @a = 1;

-- 1.2
exec p_1_notused @a = 500;

-- 1.3
exec p_1_notused @a = 1000;


-- 2.
-- 2.1
exec p_2_trivial @a = 1;

-- 2.2
exec p_2_trivial @a = 500;

-- 2.3
exec p_2_trivial @a = 1000;


-- 3.
-- 3.1
exec p_3_full @a = 1;

-- 3.3
exec p_3_full @a = 500;

-- 3.3
exec p_3_full @a = 1000;
go

------------------------------------------------------------------------------------------
drop proc p_1_notused, p_2_trivial, p_3_full;


Вот что вернул профайлер. Посмотрим на результаты.
Картинка с другого сайта.

Для процедуры 1.
При первом выполнении строится план запросов в процедуре, на момент построения плана временной таблицы еще нет, так что вместо полноценного плана записывается некая «заглушка». Ок, план построен, начинаем выполнять. Доходим до запроса с временной таблицей, к этому моменту таблица уже создана, можем построить полноценный план. Так что рекомпилируем план-заглушку, в обычный план. Происходит рекомпиляция с причиной Deffered Compile (отложенная компиляция, т.к. мы отложили компиляцию плана в момент построения плана для процедуры перед ее выполнением). Все последующие вызовы не генерируют никаких рекомпиляций, т.к. план кеширован, временная таблица тоже, кроме того, она нигде больше не используется, кроме как для заполнения, а значит не важно, что там у нее со статистикой.

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

Для процедуры 3.
Первое выполнение как для процедуры 2.
Второе выполнение уже отличается. Во время него, в таблицу записывается 500 строк, это пересекает границы рекомпиляции для временных таблиц:
Recompilation Threshold (RT), RT is calculated as follows. n refers to a table's cardinality when a query plan is compiled. Temporary table If n < 6, RT = 6. If 6 <= n <= 500, RT = 500. If n > 500, RT = 500 + 0.20 * n.

Поэтому, происходит рекомпиляция, а причина указана как Statistics changed.

Третье выполнение также приводит к рекомпиляции, по той же причине (см. формулу).

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

Тема сложная, в одном посте не объяснишь.
Но надеюсь дал основное представление.

Более подробно читайте вот в этом документе: Plan Caching and Recompilation in SQL Server 2012
15 дек 15, 08:39    [18560819]     Ответить | Цитировать Сообщить модератору
 Re: Перекомпиляция хранимых процедур при использоании временных таблиц  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 205
MKDT, также SomewhereSomehow описывал этот процесс немного с другого ракурса тут: 18197438

SomewhereSomehow, спасибо большое!
15 дек 15, 09:11    [18560903]     Ответить | Цитировать Сообщить модератору
 Re: Перекомпиляция хранимых процедур при использоании временных таблиц  [new]
привести к перекомпиля
Guest
MKDT,

есть небольшой трюк - можно некоторые фрагменты работы с временными таблицами заключать в динамику, тогда (в некоторых случаях) рекомпилироваться будет только фрагмент с динамикой (как отдельный вложенный маленький такой батч)
15 дек 15, 10:59    [18561395]     Ответить | Цитировать Сообщить модератору
 Re: Перекомпиляция хранимых процедур при использоании временных таблиц  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
привести к перекомпиля,

Рекомпиляция в процедурах идет по statement-ам, а не целиком для всей процедуры, если я правильно помню, начиная с 2005-го. У ТС 2014, так что не понимаю, в чем смысл такого трюка.
15 дек 15, 11:23    [18561548]     Ответить | Цитировать Сообщить модератору
 Re: Перекомпиляция хранимых процедур при использоании временных таблиц  [new]
MKDT
Guest
SomewhereSomehow, спасибо за ответ!

Начинает потихоньку проясняться. Т.е. получается табличные переменные вызывают меньше рекомпиляции ХП чем временные таблицы из-за того, что у табличных переменных статистика не полная -> не нужно делать рекомпиляцию из-за "Statistics changed"
15 дек 15, 12:48    [18562107]     Ответить | Цитировать Сообщить модератору
 Re: Перекомпиляция хранимых процедур при использоании временных таблиц  [new]
SomewhereSomehow
Member

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

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

В любом из способов есть способ уменьшить/увеличить число рекомпиляций, уменьшить/увеличить точность оценок. Например, если в третьей процедуре моего примера, в конце последнего запроса дописать option(keep plan) - то будет всего одна рекомпиляция из-за статистики, если дописать option(keepfixed plan) их не будет совсем. И наоборот, если бы был запрос с табличной переменной и мы хотим прослушать число строк в ней во время выполнения (хотя это, конечно, не полноценная статистика) - можно дописать option(recompile), но тогда запрос будет рекомпилироваться каждый раз. Либо в запросе с табличной переменной использовать флаг трассировки 2453 (https://support.microsoft.com/en-us/kb/2952444), чтобы заставить запрос с табличной перемнной перекомпилироваться, если изменилось значительное число строк (т.е. вести себя похоже на временную таблицу).

В общем, все зависит от задачи. Обычно рекомпиляция не такая проблема, как выбор плохого плана и его выполнение. С другой стороны, бывает и критично. Смотрите сами, и главное, не пытайтесь выработать "золотое правило: А всегда лучше В", а потом по нему все заранее "оптимизировать", т.е., например, начать резко переписывать весь код с временных таблиц на переменные или наоборот.
15 дек 15, 13:23    [18562320]     Ответить | Цитировать Сообщить модератору
 Re: Перекомпиляция хранимых процедур при использоании временных таблиц  [new]
SomewhereSomehow
Member

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

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

В любом из способов есть способ уменьшить/увеличить число рекомпиляций, уменьшить/увеличить точность оценок. Например, если в третьей процедуре моего примера, в конце последнего запроса дописать option(keep plan) - то будет всего одна рекомпиляция из-за статистики, если дописать option(keepfixed plan) их не будет совсем. И наоборот, если бы был запрос с табличной переменной и мы хотим прослушать число строк в ней во время выполнения (хотя это, конечно, не полноценная статистика) - можно дописать option(recompile), но тогда запрос будет рекомпилироваться каждый раз. Либо в запросе с табличной переменной использовать флаг трассировки 2453 (https://support.microsoft.com/en-us/kb/2952444), чтобы заставить запрос с табличной перемнной перекомпилироваться, если изменилось значительное число строк (т.е. вести себя похоже на временную таблицу).

В общем, все зависит от задачи. Обычно рекомпиляция не такая проблема, как выбор плохого плана и его выполнение. С другой стороны, бывает и критично. Смотрите сами, и главное, не пытайтесь выработать "золотое правило: А всегда лучше В", а потом по нему все заранее "оптимизировать", т.е., например, начать резко переписывать весь код с временных таблиц на переменные или наоборот.
15 дек 15, 13:23    [18562321]     Ответить | Цитировать Сообщить модератору
 Re: Перекомпиляция хранимых процедур при использоании временных таблиц  [new]
Re: Перекомпиляция хранимых проц
Guest
SomewhereSomehow
привести к перекомпиля,

Рекомпиляция в процедурах идет по statement-ам, а не целиком для всей процедуры, если я правильно помню, начиная с 2005-го. У ТС 2014, так что не понимаю, в чем смысл такого трюка.

тот самый случай когда знания тянутся-тянутся черт знает откуда (с версии 2000), и уже как-то даже под сомнение их не ставишь.
мысль понял.
15 дек 15, 19:16    [18564650]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить