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

1) когда компилится батч или процедура. в sys.dm_exec_cached_plans мне будет виден план целиком для процедуры/батча? Каждый такой план состоит из планов операторов? Но в sys.dm_exec_cached_plans можно увидеть только целиком план для батча и процедуры? но не для отдельного оператора (про sys.dm_exec_text_query_plan в курсе)

2) если у меня N батчей и M процедур и в них входит запрос select * from mytable where id = @id_param. предположим что параметры сессии одинаковы также оператор safe. вопрос планы этих N батчей и M процедур будут содержать свою копию плана select'а или для этого селекта будет создан общий план, а цельные планы батчей и процедур - будут переиспользовать их?

3) что означает refcounts в sys.dm_exec_cached_plans?

3) еще не пойму, что хешируется перед поиском плана - весь батч исключая литералы или отдельные операторы плана исключая литералы? или если батчи хоть немного различаются (количеством операторов например), то план батча не будет найден и будет создан новый план для батча с копиями однотипных планов для всех операторов батча? или планы операторов впределах батча переиспользуются, но не переиспользуются между планами батчей?
13 окт 15, 11:23    [18271429]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Владислав Колосов
Member

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

Вероятно, Ваши вопросы связаны с незнанием того, что компилируются планы только для запросов, код процедур выполняется интерпретатором, максимум, во что н может превратиться - это P-Code. План пакета - это выдумка.

1),3) ответы в справке.
13 окт 15, 14:32    [18272803]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 205
Fiona1993, Почитайте. Многие вопросы сами собой отпадут

http://www.somewheresomehow.ru/fast-in-ssms-slow-in-app-part1/
http://www.somewheresomehow.ru/fast-in-ssms-slow-in-app-part2/
http://www.somewheresomehow.ru/fast-in-ssms-slow-in-app-part2/
13 окт 15, 14:35    [18272815]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Fiona1993
Guest
Pavel1211
Fiona1993, Почитайте. Многие вопросы сами собой отпадут

http://www.somewheresomehow.ru/fast-in-ssms-slow-in-app-part1/
http://www.somewheresomehow.ru/fast-in-ssms-slow-in-app-part2/
http://www.somewheresomehow.ru/fast-in-ssms-slow-in-app-part2/


читала, не помогает. там нет ответов на мои вопросы...
13 окт 15, 15:04    [18273004]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Fiona1993
Guest
Владислав Колосов
Fiona1993,

Вероятно, Ваши вопросы связаны с незнанием того, что компилируются планы только для запросов, код процедур выполняется интерпретатором, максимум, во что н может превратиться - это P-Code. План пакета - это выдумка.

1),3) ответы в справке.


читала, не однозначно все.

вопрос такой - будут ли два различных батча имеющих одинаковый запрос (по-мимо прочих различных запросов в этих двух батчах) использовать один и тот же план (не копию, а один и тот же). предполагаем, что set опции одинаковы план запрос safe....
13 окт 15, 15:06    [18273018]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Fiona1993
Guest
Fiona1993
Владислав Колосов
Fiona1993,

Вероятно, Ваши вопросы связаны с незнанием того, что компилируются планы только для запросов, код процедур выполняется интерпретатором, максимум, во что н может превратиться - это P-Code. План пакета - это выдумка.

1),3) ответы в справке.


читала, не однозначно все.

вопрос такой - будут ли два различных батча имеющих одинаковый запрос (по-мимо прочих различных запросов в этих двух батчах) использовать один и тот же план (не копию, а один и тот же). предполагаем, что set опции одинаковы план запрос safe....


причем я спрашивала не о execution plan (основанный на compiled), а именно о compiled plan.

или вот с такой стороны зайду:

1) для батча есть план в cached_plans, батч состоял из операторов T-SQL и потому план батча состоит из планов этих операторов
2) будут ли эти планы операторов из которого состоит план батча переиспользоваться другими батчами?

судя по тому что читала - нет.

проясните пожалуйста (без ссылок своими словами). всю голову сломала уже.
13 окт 15, 15:11    [18273048]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
если хотите гарантированного использования конкретного плана для отдельных запросов, то используйте хинты с указанием плана
13 окт 15, 15:15    [18273084]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Glory
Member

Откуда:
Сообщений: 104751
Fiona1993
вопрос такой - будут ли два различных батча имеющих одинаковый запрос (по-мимо прочих различных запросов в этих двух батчах) использовать один и тот же план (не копию, а один и тот же).

В хелпе же написано про plan reusing
https://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx
13 окт 15, 15:29    [18273180]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Fiona1993
Guest
Konst_One
если хотите гарантированного использования конкретного плана для отдельных запросов, то используйте хинты с указанием плана


спасибо, мне известен такой способ и плангайды тоже. но интересует внутреннее устройство. этот недостаток инфы делает меня не уверенной.

все говорят о plan reuse / plan reuse. я поняла ну план reuse. а какого плана - плана батча или отдельного оператора батча или и того и другого - не так уж и однозначно описано.
13 окт 15, 15:30    [18273187]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Владислав Колосов
Member

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

почему Вы все время пишите о плане пакета выполнения? Нет никакого плана выполнения пакета.
13 окт 15, 15:48    [18273346]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 205
Владислав Колосов,

+1. Я уже запутался в постановке вопроса ))
Также непонятно, что такое "план отдельного оператора"
13 окт 15, 15:49    [18273353]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
Статья "Медленно в приложении, быстро в SSMS (часть 1)", кстати, не загружается в PDF, приходит файл без наполнения, только колонтитулы.
13 окт 15, 15:50    [18273358]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Glory
Member

Откуда:
Сообщений: 104751
Владислав Колосов
Fiona1993,

почему Вы все время пишите о плане пакета выполнения? Нет никакого плана выполнения пакета.

Может просто Fiona1993 путает компиляцию пакета с построением плана выполнения запроса ?
13 окт 15, 15:55    [18273397]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Fiona1993
Guest
Владислав Колосов
Fiona1993,

почему Вы все время пишите о плане пакета выполнения? Нет никакого плана выполнения пакета.


а вы отправьте батч из нескольких операторов на выполнение и посмотрите на то, что вернет по plan_handle из sys.dm_exec_cached_plans вот эти две функции

sys.dm_exec_query_plan - вернет план батча
sys.dm_exec_text_query_plan - вернет план отдельного оператора в плане батча (нужно еще stmt_start, stmt_end можно взять из sys.dm_exec_query_stats)
13 окт 15, 15:57    [18273416]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Fiona1993
Guest
Glory
Владислав Колосов
Fiona1993,

почему Вы все время пишите о плане пакета выполнения? Нет никакого плана выполнения пакета.

Может просто Fiona1993 путает компиляцию пакета с построением плана выполнения запроса ?


даже не знаю. вот с однооператорными батчами все совпадает с документацией. тогда план батча тоже что и план оператора. а вот с батчами имеющими несколько T-SQL операторов че-то не поняла.
13 окт 15, 16:04    [18273486]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Glory
Member

Откуда:
Сообщений: 104751
A batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution. SQL Server compiles the statements of a batch into a single executable unit, called an execution plan. The statements in the execution plan are then executed one at a time.
13 окт 15, 16:12    [18273551]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Fiona1993
Guest
Glory
A batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution. SQL Server compiles the statements of a batch into a single executable unit, called an execution plan. The statements in the execution plan are then executed one at a time.


начинаю соображать

-- marker 00001
insert into t1(id) values(1);
insert into t1(id) values(2);
insert into t1(id) values(3);

select * from t1 where id = 5

select * from t1 where id = 7

select * from t1 where id = 20
go


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="12.0.4100.1">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="-- marker 00001
insert into t1(id) values(1)" StatementId="1" StatementCompId="1" StatementType="INSERT" RetrievedFromCache="true" ParameterizedPlanHandle="0x060007000185710560BEEA620000000001000000000000000000000000000000000000000000000000000000" ParameterizedText="(@1 int)INSERT INTO [t1]([id]) values(@1)" />
        <StmtSimple StatementText=";
insert into t1(id) values(2)" StatementId="2" StatementCompId="2" StatementType="INSERT" RetrievedFromCache="true" ParameterizedPlanHandle="0x060007000185710560BEEA620000000001000000000000000000000000000000000000000000000000000000" ParameterizedText="(@1 int)INSERT INTO [t1]([id]) values(@1)" />
        <StmtSimple StatementText=";
insert into t1(id) values(3)" StatementId="3" StatementCompId="3" StatementType="INSERT" RetrievedFromCache="true" ParameterizedPlanHandle="0x060007000185710560BEEA620000000001000000000000000000000000000000000000000000000000000000" ParameterizedText="(@1 int)INSERT INTO [t1]([id]) values(@1)" />
        <StmtSimple StatementText=";

select * from t1 where id = 5" StatementId="4" StatementCompId="4" StatementType="SELECT" RetrievedFromCache="true" ParameterizedPlanHandle="0x0600070069D3912450F9945F0000000001000000000000000000000000000000000000000000000000000000" ParameterizedText="(@1 tinyint)SELECT * FROM [t1] WHERE [id]=@1" />
        <StmtSimple StatementText="

select * from t1 where id = 7" StatementId="5" StatementCompId="5" StatementType="SELECT" RetrievedFromCache="true" ParameterizedPlanHandle="0x0600070069D3912450F9945F0000000001000000000000000000000000000000000000000000000000000000" ParameterizedText="(@1 tinyint)SELECT * FROM [t1] WHERE [id]=@1" />
        <StmtSimple StatementText="

select * from t1 where id = 20" StatementId="6" StatementCompId="6" StatementType="SELECT" RetrievedFromCache="true" ParameterizedPlanHandle="0x0600070069D3912450F9945F0000000001000000000000000000000000000000000000000000000000000000" ParameterizedText="(@1 tinyint)SELECT * FROM [t1] WHERE [id]=@1" />
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>


-- marker 00001
insert into t1(id) values(1);
insert into t1(id) values(2);
insert into t1(id) values(3);

select * from t1 where id = 5

select * from t1 where id = 7

go


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="12.0.4100.1">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="-- marker 00001
insert into t1(id) values(1)" StatementId="1" StatementCompId="1" StatementType="INSERT" RetrievedFromCache="true" ParameterizedPlanHandle="0x060007000185710560BEEA620000000001000000000000000000000000000000000000000000000000000000" ParameterizedText="(@1 int)INSERT INTO [t1]([id]) values(@1)" />
        <StmtSimple StatementText=";
insert into t1(id) values(2)" StatementId="2" StatementCompId="2" StatementType="INSERT" RetrievedFromCache="true" ParameterizedPlanHandle="0x060007000185710560BEEA620000000001000000000000000000000000000000000000000000000000000000" ParameterizedText="(@1 int)INSERT INTO [t1]([id]) values(@1)" />
        <StmtSimple StatementText=";
insert into t1(id) values(3)" StatementId="3" StatementCompId="3" StatementType="INSERT" RetrievedFromCache="true" ParameterizedPlanHandle="0x060007000185710560BEEA620000000001000000000000000000000000000000000000000000000000000000" ParameterizedText="(@1 int)INSERT INTO [t1]([id]) values(@1)" />
        <StmtSimple StatementText=";

select * from t1 where id = 5" StatementId="4" StatementCompId="4" StatementType="SELECT" RetrievedFromCache="true" ParameterizedPlanHandle="0x0600070069D3912450F9945F0000000001000000000000000000000000000000000000000000000000000000" ParameterizedText="(@1 tinyint)SELECT * FROM [t1] WHERE [id]=@1" />
        <StmtSimple StatementText="

select * from t1 where id = 7" StatementId="5" StatementCompId="5" StatementType="SELECT" RetrievedFromCache="true" ParameterizedPlanHandle="0x0600070069D3912450F9945F0000000001000000000000000000000000000000000000000000000000000000" ParameterizedText="(@1 tinyint)SELECT * FROM [t1] WHERE [id]=@1" />
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
13 окт 15, 17:00    [18273925]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Fiona1993
Guest
1) похоже, на то что для каждого батча создается свой план со ссылками на планы операторов sql его составляющих, для каждого оператора sql свой план (в данном случае параметризованный)
2) если тексты батчей побуквенно совпадают - то план батча переиспользуется, иначе создается еще один со ссылками на планы операторов его составляющих
3) по крайней мере, параметризованные планы отдельных операторов переиспользуются разными батчами.

а в sys.dm_exec_cached_plans будут как планы батчей (со ссылками на планы операторов) так и планы операторов (параметризованных по крайней мере).
13 окт 15, 17:05    [18273970]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Владислав Колосов
Member

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

сохранение в кэш еще и зависит от стратегии кэширования разовых запросов (ad hoc).
13 окт 15, 17:10    [18273999]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
Однако, в кэше плана выполнения сохраняются только планы запросов, но не весь пакет.
13 окт 15, 17:12    [18274008]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Fiona1993
Guest
Владислав Колосов
Однако, в кэше плана выполнения сохраняются только планы запросов, но не весь пакет.


верхние 3 строчки - это вот такие планы пустышки. обратите внимание на size и usecounts. когда я запускала батчи повторно usecounts этих adhoc'ов увеличивались.


cacheobjtype objtype plan_handle size_in_bytes usecounts
-------------------------------------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------- ------------- -----------
Compiled Plan Adhoc 0x06000700892A8B0140E0C55E0000000001000000000000000000000000000000000000000000000000000000 131072 3
Compiled Plan Adhoc 0x060007002AB176004018455F0000000001000000000000000000000000000000000000000000000000000000 114688 10
Compiled Plan Adhoc 0x0600070094C6A30530F3945F0000000001000000000000000000000000000000000000000000000000000000 98304 1
Compiled Plan Prepared 0x0600070069D3912450F9945F0000000001000000000000000000000000000000000000000000000000000000 32768 9
Compiled Plan Prepared 0x060007000185710560BEEA620000000001000000000000000000000000000000000000000000000000000000 32768 15
13 окт 15, 17:25    [18274098]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
Fiona1993
Guest
оно получается хранит как это называется plan shell (не plan stub как при optimize for adhoc), и если есть побуквенное совпадение батча использует его опять. (я не ставила optimize for adhoc). размер какой большой у этих adhoc'ов. а там всего лишь пять шесть операторов.
13 окт 15, 17:30    [18274128]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Владислав Колосов
План пакета - это выдумка.
Владислав Колосов
Однако, в кэше плана выполнения сохраняются только планы запросов, но не весь пакет.
А это тогда что:
/*This is a batch*/
select count(*) from sys.objects where type = 'P';
exec sys.sp_help 'int';
go

select
 qp.query_plan, t.text
from
 sys.dm_exec_cached_plans cp cross apply
 sys.dm_exec_sql_text(cp.plan_handle) t cross apply
 sys.dm_exec_query_plan(cp.plan_handle) qp
where
 t.text like '%/*This is a batch*/%'and t.text not like '%query[_]plan%';
?
13 окт 15, 18:04    [18274279]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Не был на форуме т.к. был занят, в том числе подготовкой к #sqlsatMoscow, и, возможно, мой ответ уже не актуален, но пусть будет, может потом кому-то пригодится.

План в кэше строится для всего пакета (батча), либо для всей процедуры. Не важно, как идет execution flow, план строится для всего. То, что ТС называет «операторами», на самом деле называется statement-ами. План может содержать несколько стейтментов.

Каждый стейтмент внутри сиквела представляется классом CStmt. План для всего батча содержит массив этих стейтментов, каждый из которых содержит план своей ветки, а сам «план батча», который может содержать стейтменты является костяком или skeleton планом.

Подробнее об этом можно почитать в следующих статьях:
http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/09/1-0-structure-of-the-plan-cache-and-types-of-cached-objects.aspx
http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/04/ketan-duvedi.aspx

Там же есть про Execution Context-ы и про то, как выполняется план. А те, кто смотрел мой доклад, знают об Execution Context даже немного подробнее (в частности что есть два типа MXC b Query XC) и более того, видели MXC своими глазами, когда я делал live демо с WinDbg.

Из ссылки выше, вот структура объектов в памяти, которая представляет запись в кэше для плана

К сообщению приложен файл. Размер - 64Kb
18 окт 15, 17:12    [18293829]     Ответить | Цитировать Сообщить модератору
 Re: вопрос про кэш планов  [new]
SomewhereSomehow
Member

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

Этот рисунок их vsd документа из блога разработчиков сиквела.
Те контексты что слева вверху (MXC 1, MXC N) - это один тип, те что обозначены QueryContextForSE - второй.
18 окт 15, 17:16    [18293836]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить