October 17, 2013 at 8:44 am
pchirags (10/17/2013)
sqlnaive (10/17/2013)
Agreed. ParameterCompiledValue is the one which should be considered to answer this question (as I got it and please correct me if I'm wrong).With my earlier attempt it was giving "Pelle" in both attempts (may be because of my multiple runs of procedure before checking the xml. So now I dropped and created the proc and table again and checked the execution plan. And the xml gave me following in both attempts:
<ColumnReference Column="@Name" ParameterCompiledValue="'Kalle'" ParameterRuntimeValue="'Pelle'" />So should the answer be "Kalle", "Kalle" instead of "Kalle", "Pelle" ?
or you tried asking what hsould be ParameterCompiledValue and ParameterRuntimeValue ?
Apologies but I'm just try to clear my doubt here. Interesting question though.
+1
The question is clear: What will the parameter compiled values (sniffed values) for the parameter @Name
If you use exec sp_recompile 'T'
you'll get results (compiled values) "Kelle" and "Pelle"
If you use exec sp_recompile 'GetIt'
you'll get results (compiled values) "Kelle" and "Urban"
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
October 17, 2013 at 8:54 am
Wow, what an interesting question. Thank you for posting this!
October 17, 2013 at 9:06 am
Richard Warr (10/17/2013)
How nice not to see page on page of "Easy peasy" comments. 🙂Interesting question which opens up an area still uncharted for many developers.
What I really hate is when I miss the question (and sometimes several others do as well) and then see all of the "easy peasy" comments. Sometimes, how easy or hard it is depends upon how long you've been doing this.
October 17, 2013 at 9:07 am
Mikael Eriksson SE (10/17/2013)
It is the same in SQL Server 2005, 2008 and 2012.If you want to check what the compiled value is you can use this.
create table T(Name varchar(25));go
create procedure GetIt
@Name varchar(25)
as
set @Name = 'Pelle';
select *
from T
where 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;
exec sp_recompile 'T';
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;
I see what you are saying but when I run this under version 10.0.2531.0, both of my compiled values are Pelle. Again, like others, just clearing the air but a very nice, thought provoking QoTD you provided.
October 17, 2013 at 9:15 am
You need to make sure that the query plan is not already generated and in cache before first execution of the procedure.
October 17, 2013 at 9:32 am
marcia.j.wilson (10/17/2013)
Richard Warr (10/17/2013)
How nice not to see page on page of "Easy peasy" comments. 🙂Interesting question which opens up an area still uncharted for many developers.
What I really hate is when I miss the question (and sometimes several others do as well) and then see all of the "easy peasy" comments. Sometimes, how easy or hard it is depends upon how long you've been doing this.
Don't worry Marcia. Hope you have not seen much of "EZ PZ" comments in this discussion and lion's share of incorrect answers proves that. 🙂
It was an interesting question and would like to see many more like this... 🙂
October 17, 2013 at 9:52 am
Mikael Eriksson SE (10/17/2013)
You need to make sure that the query plan is not already generated and in cache before first execution of the procedure.
Mikael, thanks. Just before you posted, I ran a DBCC FREEPROCCACHE WITH NO_INFOMSGS; and I am now returning the proper values. But, of course, I ran it a second time without freeing my proc cache and the results are "Pelle, Pelle".
Thanks
October 17, 2013 at 10:42 am
Jeff Atherton (10/17/2013)
L' Eomot Inversé (10/17/2013)
Arrgh! I got this wrong by a careless assumption. I assumed that sp_recompile('T') would the same effect as would sp_recompile('GetIt') in this context. So now I've learnt not to make silly assumptions.Good question for me, since I learnt something.
How are those 2 different?
sp_recompile('GetIt') will mark that stored procedure to be recompiled the next time it's run. sp_recompile('T') will mark all the stored procedures and triggers that reference that table to be recompiled they next time they are run. Since 'GetIt' references table 'T' the effect on 'GetIt' should be the same with either of those statements.
What am I missing?
I'm not sure, but it looks to me as if sp_recompile('T') causes recompilation of a query involving T when it's run, and in theis Sp the query run after the assignment; sp_recompile('Getit') seems to recompile the SP earlier than that - possibly straight away (not even waiting for next call) so the thing sniffed is 'unknown'. When I saw I had the wrong answer, I wrote some test code to see what happens (don't trust documentation for this stuff) and estimated rows for the query are different according to whether the parameter of sp_recompile was the procedure name or the table name. Quite interesting, and I am going to have play around with some more code to make sure I understand it fully.
Tom
October 17, 2013 at 10:52 am
When you ask for a recompile of the stored procedure it is recompiled on the next execution. The compiled value of @Name for the query will then be what is passed to the procedure as an argument.
October 17, 2013 at 11:49 am
Nice question, didn't think of the statement LEVEL recompile caused by the force of recompile of all statements that uses the table.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
October 17, 2013 at 1:31 pm
IgorMi (10/17/2013)
If you use exec sp_recompile 'T'you'll get results (compiled values) "Kelle" and "Pelle"
If you use exec sp_recompile 'GetIt'
you'll get results (compiled values) "Kelle" and "Urban"
Which is what I did... 🙂 forgot about statement recompiles.
October 17, 2013 at 3:02 pm
A good question. I missed my point, because I once again made the mistake of trusting the documentation.
http://technet.microsoft.com/en-us/library/ms181647.aspx says that if sp_recompile specifies a table or view, all the stored procedures or triggers that reference the table or view will be recompiled the next time that they are run (emphasis mine). It doesn't mention that this is actually a statement-level recompile.
Great to know - never needed this until now, but you never know when it comes in handy. Thanks, Mikael! 😉
October 17, 2013 at 10:28 pm
Hugo Kornelis (10/17/2013)
A good question. I missed my point, because I once again made the mistake of trusting the documentation.http://technet.microsoft.com/en-us/library/ms181647.aspx says that if sp_recompile specifies a table or view, all the stored procedures or triggers that reference the table or view will be recompiled the next time that they are run (emphasis mine). It doesn't mention that this is actually a statement-level recompile.
Great to know - never needed this until now, but you never know when it comes in handy. Thanks, Mikael! 😉
+1
October 17, 2013 at 11:14 pm
Maximum wrong reply on this question
October 17, 2013 at 11:56 pm
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;
Viewing 15 posts - 31 through 45 (of 56 total)
You must be logged in to reply to this topic. Login to reply