Использует ли хранимая процедура кэш?

Пишет st,

Как бы убедиться попроще, что процедура в 2005 сиквеле использует при вызове процедурный кэш?
План показывает только оценку запросов.
Процедура типа
create procedure Test @Param1, @Param2
as
if @Param1 is not null
select ... where Param1 = @Param1
else if @Param2 is not null
select ... where Param2 = @Param2
go

В syscacheobjects соответствующая запись имеется.
Вопрос, используется ли?

Forums: 

Изображение пользователя st.

SP:Recompile

Пишет st,

Дима подсказал посмотреть в профайлере отлов SP:Recompile

А какое

Пишет succulent (не проверено),

А какое отношение имеет Recompile к использованию процедурного кэша?
Процедурный кэш используется _всегда_, так как только там и размещается откомпилированная (с планами выполнения и т.п.) хп.

Или я не понял вопрос :)

Изображение пользователя st.

usecounts

Пишет st,

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

BOL - Recompiling Stored Procedures

Пишет sukkulente (не проверено),

В разделе BOL - Recompiling Stored Procedures - все неплохо описано.
Перекомпиляция всегда происходит при первом вызове хп после старта сервера. Принять решение о перекомпиляции может оптимизатор (сильно изменилась статистика данных и т.п.), а может и сам пользователь (sp_recompile, WITH RECOMPILE).

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

Кстати, в 2005-м наверное "правильнее" смотреть sys.dm_exec_cached_plans

Изображение пользователя st.

Мой вопрос

Пишет st,

Мой вопрос был как раз в том, чтобы знать, "свежевыпеченный" план или нет. Т.к. частая перекомпиляция бьет по производительности.

sys.dm_exec_query_stats

Пишет succulent (не проверено),

Тогда я бы посмотрел в сторону sys.dm_exec_query_stats, правда в текст процедуры придется добавить "исследовательский" код, например:

create proc test_exec_cached_plans
 as
set nocount on
 
--testing region
declare @execution_count bigint, @creation_time datetime, @last_execution_time datetime
 
select @execution_count=s.execution_count, @creation_time=s.creation_time, @last_execution_time=s.last_execution_time
from sys.dm_exec_query_stats s inner join sys.dm_exec_requests r on s.sql_handle=r.sql_handle
where r.session_id=@@spid
 
if @execution_count is not null begin
	declare @s varchar(max)
	set @s='Процедура живет уже '+convert(varchar,datediff(ms,@creation_time,@last_execution_time))+' ms, вызывалась '+convert(varchar,@execution_count)+' раз.'
	print @s
end else
	print 'Была перекомпиляция!'
--end testing region
 
go

> в текст

Пишет succulent (не проверено),

> в текст процедуры придется добавить "исследовательский" код
Тут я наврал, прошу прощения :)

Пример из BOL:

SELECT creation_time, last_execution_time, execution_count, st.text,
    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
    ((CASE statement_end_offset 
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE qs.statement_end_offset END 
            - qs.statement_start_offset)/2) + 1) as statement_text
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY total_clr_time/execution_count DESC;
GO

sys.syscacheobjects.usecounts

Пишет sukkulente (не проверено),

> Как бы убедиться попроще
Проследить за изменением значения sys.syscacheobjects.usecounts - после каждого вызова увеличивается на единичку (если не было перекомпиляции, разумеется)