Hugo Kornelis (10/17/2013)
Great to know - never needed this until now, but you never know when it comes in handy. Thanks, Mikael! 😉
The point I was getting at was not really the behaviour of sp_recompile but the how parameter sniffing works when you compile a stored procedure compared to a statement level compile.
There are other reasons that will cause a statement level compile that will show the exact same behaviour.
One scenario is on auto update statistics. That happens for instance when you add the first row to the table. So instead of sp_recompile I could have used insert into T values(''); between the executions of the stored procedure.
But I also need to change the query in the question because a "trivial plan" is not recompiled on update statistics.
This would perhaps have been a better question (and perhaps harder), removing the focus from sp_recompile.
create table T(Name varchar(25));
go
create procedure GetIt
@Name varchar(25)
as
set @Name = 'Pelle';
select T1.*
from T as T1
inner join T as T2
on T1.Name = T2.Name
where T1.Name = @Name
go
exec GetIt 'Kalle';
insert into T values('');
exec GetIt 'Urban';
With queries to get the Compiled Value
create table T(Name varchar(25));
go
create procedure GetIt
@Name varchar(25)
as
set @Name = 'Pelle';
select T1.*
from T as T1
inner join T as T2
on T1.Name = T2.Name
where T1.Name = @Name
go
exec GetIt 'Kalle';
with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select T.qp.value('(//ColumnReference/@ParameterCompiledValue)[1]', 'varchar(25)') as ParameterCompiledValue
from
(
select cast(qp.query_plan as xml) as qp
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) as qp
where object_name(st.objectid) = 'GetIt'
) as T;
insert into T values('');
exec GetIt 'Urban';
with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select T.qp.value('(//ColumnReference/@ParameterCompiledValue)[1]', 'varchar(25)') as ParameterCompiledValue
from
(
select cast(qp.query_plan as xml) as qp
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) as qp
where object_name(st.objectid) = 'GetIt'
) as T;