October 17, 2013 at 3:49 am
No.
October 17, 2013 at 5:00 am
Mikael Eriksson SE (10/17/2013)
sqlnaive (10/17/2013)
It gave me "Pelle" two times for both executions.That is as it should be. The question was about what value was used as input to the query optimizer when it builds the execution plan.
The value used as the "compiled value" is not the same as the value actually used in execution.
Cool. That's something I didn't get and hopefully many of others as well.
One more thing, how can we check which parameter value is getting using during compilation ? I checked the execution plan as xml for both of procedure executes and found following:
<ColumnReference Column="@Name" ParameterCompiledValue="'Pelle'" ParameterRuntimeValue="'Pelle'" />
October 17, 2013 at 5:07 am
It is ParameterCompiledValue.
October 17, 2013 at 5:13 am
Good question
and thanks SqlNaive for sharing the info about how to check value in XML plan
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 17, 2013 at 5:13 am
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.
October 17, 2013 at 5:34 am
Check this post for a query the fetches the values from the execution plan.
url=http://www.sqlservercentral.com/Forums/FindPost1505494.aspx
October 17, 2013 at 6:14 am
Thanks Mikael. I tried for some time and was getting same "Kalle" for both proc execution from the xml (though the query you provided was giving "Kalle" and "Pelle").
Now it is clear. I ran all of it at once and then fetched the xml plan out of the execution plan, which in each time, was picking up the latest one showing "Kalle". On executing first (and checking it's xml plan) and then executing second (and checking it's xml plan as well), I got it.
It was a very good question and cleared many of my doubts while doing little R&D. Cheers. 🙂
October 17, 2013 at 6:21 am
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.
Tom
October 17, 2013 at 6:57 am
This was removed by the editor as SPAM
October 17, 2013 at 7:07 am
Excellent question. I did get it wrong (most from not paying enough attention...I was in the "Pelle, Pelle" group), but excellent question nevertheless. While I know you did not ask for performance optimization, here is a nice link to optimize parameter sniffing since SQL Server 2005 (short and to the point).
http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx
October 17, 2013 at 7:16 am
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?
October 17, 2013 at 7:54 am
Using sp_recompile 'GetIt' will make the entire stored procedure to be recompiled on next execution.
sp_recompile 'T' recompiles only the queries in the stored procedure that references the table T (statement level recompile).
The difference is not clear in the documentation of sp_recompile.
October 17, 2013 at 8:36 am
Mikael Eriksson SE (10/17/2013)
Using sp_recompile 'GetIt' will make the entire stored procedure to be recompiled on next execution.sp_recompile 'T' recompiles only the queries in the stored procedure that references the table T (statement level recompile).
The difference is not clear in the documentation of sp_recompile.
exec sp_recompile 'GetIt' removes the execution plan from the procedure cache
exec sp_recompile 'T' does not remove the plan from the procedure cache
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
October 17, 2013 at 8:38 am
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
October 17, 2013 at 8:42 am
Excellent question and explanation...
got it wrong and glad that learnt something.
Thanks Mikael..
Viewing 15 posts - 16 through 30 (of 56 total)
You must be logged in to reply to this topic. Login to reply