SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parameter sniffing


Parameter sniffing

Author
Message
mtassin
mtassin
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7068 Visits: 72521
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... Smile forgot about statement recompiles.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18313 Visits: 12426
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! Wink


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
sqlnaive
sqlnaive
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6259 Visits: 2774
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! Wink


+1
T.Ashish
T.Ashish
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1323 Visits: 607
Maximum wrong reply on this question
Mikael Eriksson SE
Mikael Eriksson SE
SSC Eights!
SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)

Group: General Forum Members
Points: 866 Visits: 994
Hugo Kornelis (10/17/2013)
Great to know - never needed this until now, but you never know when it comes in handy. Thanks, Mikael! Wink


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;





Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59563 Visits: 13297
Very interesting question, thanks.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
twin.devil
twin.devil
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5126 Visits: 2692
very nice question
SqlMel
SqlMel
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1037 Visits: 480
Great question, learned something today.

Got it right but didn't know that by recompiling the table only the statement referencing that table inside the SP would be recompiled!!

I recently watched one of SQLSentry's videos on query tuning in which they stated that if a table was recompiled all "objects" that depend on a table would be recompiled so I figured the whole SP would recompile. My thought was that the entire execution plan would be thrown away and recreated.

Thanks!

---------------
Mel. Cool
Thomas Abraham
Thomas Abraham
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3773 Visits: 2256
Posting a bit late to the party, as Thursday was a hectic day. Thanks for this very interesting question. I figured there was a command option to force the defaults to be applied to existing records. Thanks to your question, I know what it is. Questions like this help me learn stuff ahead of when I need them, and that's what keeps me coming back to QOTD.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Revenant
Revenant
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12203 Visits: 5010
Two-pointer... Got it right only because I ran into this about three weeks ago.

Thanks, Mikael!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search