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


Parameter sniffing


Parameter sniffing

Author
Message
mtassin
mtassin
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13200 Visits: 72528
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
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34153 Visits: 13105
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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
sqlnaive
sqlnaive
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10875 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
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2565 Visits: 638
Maximum wrong reply on this question
Mikael Eriksson SE
Mikael Eriksson SE
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1198 Visits: 1009
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 (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: General Forum Members
Points: 144682 Visits: 13349
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
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: 12214 Visits: 2897
very nice question
SqlMel
SqlMel
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1531 Visits: 482
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
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6001 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
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22292 Visits: 5130
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