This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 40%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
May I know if Parameter sniffing will only happen on Store procedure (with parameter) and also prepared sql ??
will parameter sniffing happens on adhoc sql ?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 96%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EUD%3C/text%3E%3C/svg%3E)
Please read this link
https://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options
/An ad-hoc statement can be parameterized by SQL Server without parameter values being sniffed/
Hi @sakuraime ,
>if Parameter sniffing will only happen on Store procedure (with parameter)
No, it can also occurs on T-SQL Functions or when a batch is explicitly parameterized for reuse using sp_executesql etc.
>will parameter sniffing happens on adhoc sql ?
Yes. An ad-hoc T-SQL statement containing constant literal values. A statement parameterized in this way is also subject to parameter sniffing.
Please refer to the blog Parameter Sniffing, Embedding, and the RECOMPILE Options to get more information.
Best regards,
Cathy
do you have an example for that ?
"Yes. An ad-hoc T-SQL statement containing constant literal values. A statement parameterized in this way is also subject to parameter sniffing."
I found that I written about this here: http://www.sommarskog.se/query-plan-mysteries.html#autoparameterisation
I already shared your blog but seems like OP does not wants to read
Apart from stored procedures functions can also suffer from parameter sniffing. For prepared SQL if there is a parameter yes it can, I have read this but you will see this seldom. For Adhoc plans again if there is a parameter there can be sniffing.
Two blogs for you
if I just type
select * from tableA where col1=A
and
Select * from tableA where col1=B
this will also cause parameter sniffing ?
No, I do not think so.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 30%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETH%3C/text%3E%3C/svg%3E)
Would A and B be literals (i.e., not T-SQL parameters or T-SQL variables)? Like if you say WHERE lastname = 'Smith'. If so, yes the value will be "sniffed", in the sense that the optimizer can use the histogram to calculate how many Smith there are.
Actually I would not call it as sniffing that would either lead to index scan or seek but since you are not passing parameter it would not be sniffing. This is as far as i think
Agreed. Just a matter of terminology. :-)
Everything that has a parameter can be sniffed. So a prepared statement can run into parameter sniffing, and so can dynamic SQL executed through sp_executesql if there are parameters.
As for a pure ad-hoc statement, sniffing could be an issue if force parameterisation is in force. I don't think it can happen with simple parameterisation; if SQL Server finds that here are more than one plan choice, it gets second thought on the parameterisation.