THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

Misconceptions on parameter sniffing

This blog has moved! You can find this content at the following new location:

Published Thursday, November 3, 2016 1:55 AM by Hugo Kornelis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Emphyrio said:

Thank you. very clear explanation.

November 3, 2016 4:54 AM

Grant Fritchey said:

Awesome post. Excellent in every way.

Minor quibble: I'm not a parameter sniffing fan boy. I probably over-emphasize the benefits though. It's just unfortunate that in the inherited language we use, "parameter sniffing" doesn't refer to the mechanism, but the bad effects that the mechanism sometimes has. I'm just trying to reclaim the term.

November 3, 2016 8:41 AM

cinahcaM madA said:

"Until recently when I was trying to find a few examples to actually illustrate the benefit of parameter sniffing – and failed to find any!"

Are you perchance conflating "parameter sniffing" with "plan re-use where the plan was generated using a sniffed parameter?" I think these are two different things, although most people seem to talk about them as equivalent. The former, I would argue, is undoubtedly useful. The latter is where we can see trouble. I think it's an important distinction. What do you say on the matter?


November 3, 2016 9:44 AM

Hugo Kornelis said:

Good comments, people!

I received a comment off-list about a new feature in SQL Server 2016, ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING [ ON | OFF ] that has the same effect as trace flag 4136, but only for a single database instead of all databases on the instance. This option had slipped my mind when I was writing the post; sorry for not including it. I dislike this option only very slightly less than setting the trace flag. I do not want to throw out the child with the bathwater; I prefer to handle sniffing issues on a case by case basis. However, if you do want to turn off parameter sniffing completely, then I agree that this option is at least a bit more granular than the trace flag. (And more exposed, as you can even see it in the database properties dialog of SSMS).

@Grant: I know, especially after our talk last week at the Pass summit, that you are not really a "fan boy".

I sometimes like to provoke in my posts, which is why I chose to use extrame words. If you, or anyone else, feels offended - my apologies.

@Adam: I could have phrased that better. What I intended to write is that, while there are many examples out there to illustrate "bad parameter sniffing" (or, as you would say it, "bad results of re-use of a plan generated by sniffing"), there were no examples to back the claim that in most other situation the parameter sniffing has a beneficial effect.

For parameter sniffing to be really beneficial, you'll have to produce an example where a plan generated for a sniffed value is better for that specific value than a non-sniffed plan would be, without being worse for other values (because that is already classified as "bad parameter sniffing"). My experience when trying to find such an example is that a lot of procedures actually produce the same plan with or without sniffing, and that those procedures that do produce different plans tend to expose bad performance as soon as they are called again with other values.

(The exceptions that I found where parameter sniffing was actually beneficial are in the third section of my post above).

November 3, 2016 3:56 PM

Leave a Comment


About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Privacy Statement