Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Кэш планов - помогите найти план выполненного запроса в кэше  [new]
Любитель смотреть в кэш
Guest
Вот скрипты :
1. Создание БД и что там в ней надо
+
--#region описание задачи
/*
*/

--#endregion
set nocount on
--#region create db Tst_PlanCache2
use master;
go
if exists(select * from sys.databases d where d.name = 'Tst_PlanCache2')
drop database Tst_PlanCache2;
go

create database Tst_PlanCache2 on 
primary 
( name = N'Tst_PlanCache2_f1', filename = N'C:\Tst_PlanCache2_f1.mdf' ,
  SIZE = 8124KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)
 LOG ON 
( NAME = N'Tst_PlanCache2_log', FILENAME = N'C:\Tst_PlanCache2_log.ldf'
, SIZE = 1024KB , MAXSIZE = UNLIMITED , FILEGROWTH = 10%)
go
--#endregion
--#region create tables
use Tst_PlanCache2
go
if object_id('dbo.test1', 'U') is not null
drop table dbo.test1;
create table dbo.test1(
  _id		int identity(1,1) not null
, _guid		uniqueidentifier not null default(newid())
, _dt		datetime not null default(getdate())
, num		int
);
go
alter table dbo.test1 add constraint
pk_test1 primary key clustered
  (_dt, _id);
go
create index idx_test1 on test1 (_guid);
go
--#endregion
--#region добавляем данные
use Tst_PlanCache2
go
declare @i int
set @i = 0
while @i < 10000 
begin
	insert into dbo.test1(num)
	select @i;
	set @i = @i + 1;
end;
--#endregion
--#region create proc dbo.test
if object_id('dbo.test', 'P') is null
exec ('create procedure dbo.test as return')
go
alter procedure dbo.test
@action tinyint = 1
as
set nocount on;
begin
if @action = 1
begin
	select t.num from dbo.test1 t where t._guid = cast('3EDBE7FE-6AA0-40B8-9E82-14E674646F2C' as uniqueidentifier)
end
if @action = 2
begin
	select t._guid from dbo.test1 t where _dt = getdate() 
end
if @action = 3
begin
	select top 5 t.* from dbo.test1 t order by t.num
end;
end;
go
--#endregion
use [master]
go

2. выполнение запросов (Ахтунг, dbcc freeproccache)
+
use Tst_PlanCache2
go

dbcc freeproccache
exec dbo.test @action = 1;
exec dbo.test @action = 2;
exec dbo.test @action = 3;
exec dbo.test @action = 4;

select top 5 * from dbo.test1 order by _guid;
select t.num from dbo.test1 t where t.num = 557;
select t.num from dbo.test1 t where 1 = 2;
go
select top 5 * from dbo.test1
select t.num from dbo.test1 t
select t.num from dbo.test1 t where 1 = 5


use [master]
go

3. Просмотр что получилось
+
use Tst_PlanCache2
go

-- select * from sys.dm_exec_query_stats deqs

select top 250
  db_name(sql_text.dbid)
, sql_text.objectid 
, EQS.plan_generation_num
, EQS.execution_count
, substring(sql_text.text
	,(EQS.statement_start_offset + 2)/2
	,(case 
		when EQS.statement_end_offset = -1 then datalength(sql_text.text)
		else EQS.statement_end_offset    
	  end - EQS.statement_start_offset) /2  )
, sql_text.text
, EQS.statement_start_offset
, EQS.statement_end_offset  
, EQS.sql_handle
, sql_plan.query_plan
, EQS.plan_handle
from sys.dm_exec_query_stats EQS
	outer apply sys.dm_exec_sql_text(EQS.sql_handle) as sql_text
	outer apply sys.dm_exec_query_plan(EQS.plan_handle) as sql_plan
where
(sql_text.dbid is null) or (sql_text.dbid = db_id());

select 
  ECP.refcounts, ECP.usecounts, ECP.objtype, ECP.plan_handle
, EQP.query_plan, EQP.objectid
, O.[name]
, ECP.*
from sys.dm_exec_cached_plans ECP
outer apply sys.dm_exec_query_plan(ECP.plan_handle) EQP
left join sys.objects O on EQP.objectid = O.[object_id]

use [master]
go
drop database Tst_PlanCache2
9 сен 11, 15:09    [11254454]     Ответить | Цитировать Сообщить модератору
 Re: Кэш планов - помогите найти план выполненного запроса в кэше  [new]
Любитель смотреть в кэш
Guest
Ну а теперь вопросы:
После выполнения такого батча
dbcc freeproccache
exec dbo.test @action = 1;
exec dbo.test @action = 2;
exec dbo.test @action = 3;
exec dbo.test @action = 4;

select top 5 * from dbo.test1 order by _guid;
select t.num from dbo.test1 t where t.num = 557;
select t.num from dbo.test1 t where 1 = 2;
я ожидаю увидеть в кэше планов следующее :
планы стэйтментов из хранимки, плюс планы последних трёх запросов.
Вижу только планы стэйтментов из процедуры

Сразу после первого выполняется второй батч
select top 5 * from dbo.test1
select t.num from dbo.test1 t
select t.num from dbo.test1 t where 1 = 5
Планы этих трёх стэйтментов в кэше уже присутствуют.

Основной вопрос - где планы запросов из первого батча (тех, что после exec)
9 сен 11, 15:10    [11254464]     Ответить | Цитировать Сообщить модератору
 Re: Кэш планов - помогите найти план выполненного запроса в кэше  [new]
Любитель смотреть в кэш
Guest
Всем доброе утро!
Вопрос остался.
12 сен 11, 10:19    [11262378]     Ответить | Цитировать Сообщить модератору
 Re: Кэш планов - помогите найти план выполненного запроса в кэше  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Любитель смотреть в кэш,

хм..а ничего что у вас очистка кеша и последующие команды выполняются в одном пакете?
разделите пакеты поставив GO между стейтментами и сразу найдете и планы запросов и планы вызова процедур.
use Tst_PlanCache2
go
dbcc freeproccache
go
exec dbo.test @action = 1;
...
12 сен 11, 11:24    [11262718]     Ответить | Цитировать Сообщить модератору
 Re: Кэш планов - помогите найти план выполненного запроса в кэше  [new]
Любитель смотреть в кэш
Guest
SomewhereSomehow
хм..а ничего что у вас очистка кеша и последующие команды выполняются в одном пакете?
разделите пакеты поставив GO между стейтментами и сразу найдете и планы запросов и планы вызова процедур.

хм.
Разделитель поставил, планы нашел.
Разъясните теперь плиз почему так? Очистил я кэш планов и очистил, почему по завершению пакета мои Adhoc-запросы этого пакета не остались в кэше? А планы кода процедуры остались.
12 сен 11, 11:42    [11262835]     Ответить | Цитировать Сообщить модератору
 Re: Кэш планов - помогите найти план выполненного запроса в кэше  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Любитель смотреть в кэш,

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

все, в кэше остается только тот план который сервер создал после удаления при выполнении хп.
весь этот увлекательный процесс вы можете пронаблюдать сами заюзав профайлер и включив события: sp:StmtSTarting, Starting, StmtComplited, CacheInsert, CacheRemove; SQL: BatchStarting, BatchCompleted, StmtCompleted, StmtStarting...
12 сен 11, 12:04    [11263005]     Ответить | Цитировать Сообщить модератору
 Re: Кэш планов - помогите найти план выполненного запроса в кэше  [new]
Любитель смотреть в кэш
Guest
SomewhereSomehow,
Мдя. Как то интересно.
Что же понимается под "Entire Procedure Cache Flushed"?

В данном случае получается, что DBEngine
1. находит план в кэше или строит этот план
2. помещает его в некоторую внутреннюю структуру памяти для выполнения
3. чистит кэш
4. далее работает со своей копией плана, полученного на шаге 1 и сохранённого где-то на шаге 2.
Сможет кто сказать, я правильно понял ситуацию?
Где нибудь в печатном виде это описано, сможет кто сказать?
12 сен 11, 12:37    [11263256]     Ответить | Цитировать Сообщить модератору
 Re: Кэш планов - помогите найти план выполненного запроса в кэше  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Любитель смотреть в кэш,

посмотреть последовательность шагов, которую я описал, вы можете при помощи профайлера сами. Что касается внутренних структур памяти, алгоритмов и прочих конкретных подробностей - тут, лично я, пас. Наверное такие вещи должны быть известны только разработчикам сиквела, хотя периодически встречаются публикации по всяким интересным внутренним штучкам, попробуйте поискать, если вам что-то еще осталось непонятным...
12 сен 11, 12:49    [11263340]     Ответить | Цитировать Сообщить модератору
 Re: Кэш планов - помогите найти план выполненного запроса в кэше  [new]
Любитель смотреть в кэш
Guest
SomewhereSomehow
посмотреть последовательность шагов, которую я описал, вы можете при помощи профайлера сами. Что касается внутренних структур памяти, алгоритмов и прочих конкретных подробностей - тут, лично я, пас. Наверное такие вещи должны быть известны только разработчикам сиквела, хотя периодически встречаются публикации по всяким интересным внутренним штучкам, попробуйте поискать, если вам что-то еще осталось непонятным...
Да с профайлером то я уже по коду прошелся. Просто удивительно, что после выполнения dbcc freeproccache и записи в профайлере Entire Procedure Cache Flushed запросы этого батча ещё как то выполняются. Получается, что в момент выполнения они не в кэше планов, а где то ещё, возможно в области, связанной с конкретной сессией.
Ну ладно впрочем, может ещё кто напишет по делу.

SomewhereSomehow - спасибо!
12 сен 11, 12:57    [11263395]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить