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

Anonymous 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

123 said:">">">">

July 1, 2018 11:59 PM

hzx said:


July 11, 2018 11:42 PM

Yeezy Supply said: Jordan 11 Win Like 82 Nike Outlet Store Nike Outlet Store Yeezy Yeezy Moncler UK Yeezy Boost 350 Yeezy Boost 350 V2 Birkenstock Oakley Outlet Nike Outlet Online"> nike clearance Off White Jordan 1 Nike Air Max Yeezy Boost 350 Air Max 97 jordan 12 bordeaux Yeezys Nike Shoes Nike Outlet Air Max 2017 Moncler Jackets Yeezy Boost 350 V2 Jordan 12 Win Like 82 Jordan 11 Win Like 82 jordan 4 Nike Clearance Moncler UK Yeezy Nike Outlet Store Online Shoping Birkenstock Shoes jordan 12 ovo Nike Air Max 2017

August 23, 2018 5:17 AM

Kiaan Roy said:

There are a number of factors to think about when looking at a substantial position for any regional submission factory. Whereas cost may drive initial locality options, within that framework a number of concerns require to be included. These may be

September 4, 2018 10:30 PM

Kiaan Roy said:

How to Maximize Your Manufacturer Space:  Adding to that stress is the current closing down of many submission centers and the consolidation of factory position for nationwide distributions centers. Here are a many recommendations on ways to get the for the most part of your factory position and stretch those submission center budgets.

September 4, 2018 10:31 PM

Kiaan Roy said:

A little market Protection Tips: The need for a little market every day checklist in any factory is a given, until now it is often missing. There are a few essentials when it comes to a little market protection that just about any factory should be capable to implement. Looking at the moment a little market disaster statistics, any factory that uses the little market protection information at the moment available can generate software to build up their factory a safer surroundings for all workers.

September 4, 2018 10:31 PM

Kiaan Roy said:

An effective part like bringing in a certified a little market trainer for a burglar class can build a big difference in the a little market protection history of any of appropriate reputation factory.

September 4, 2018 10:31 PM

Kiaan Roy said:

This is no rocket science, so there is no reason for you to consider too much into it. Conform to easy rules, and you will land where you want. There are many wonderful certified moving and overall look outfits out there in the Bangalore providing best and reliable alternatives. You want to locate them, approach them and strike an agreement with them. That would suffice everything and build sure you are on track with your moving method.

September 4, 2018 10:32 PM

Arnav Nihaliay said:

September 6, 2018 11:35 PM

Arnav Nihaliay said:

September 6, 2018 11:35 PM

Arnav Nihaliay said:

September 6, 2018 11:36 PM

Arnav Nihaliay said:

September 6, 2018 11:37 PM

chenjinyan said:


October 9, 2018 6:41 PM

chenqiuying said:


October 10, 2018 6:20 PM

aaaa said:

October 17, 2018 11:58 PM

Kiaan Roy said:

November 1, 2018 1:02 AM

chenlixiang said:


November 20, 2018 10:48 PM

Riya said:

January 15, 2019 10:02 PM

Marc Davis said:

It is one of the astounding web website page in my viewpoint to the sum my appreciation minds over this sort web page. Commitment of thankfulness is all together to share!

February 19, 2019 10:05 AM

Packers and Movers Chandigarh said:

Hi! I read your blog its useful information given by you.I am visit first time and your blog  attracked me to visited again and again.You Can also visit

March 15, 2019 2:32 AM

Packers and Movers Chandigarh said:

Hi! I read your blog its useful information given by you.I am visit first time and your blog  attracked me to visited again and again.You Can also visit

March 15, 2019 2:33 AM

Packers and Movers Hyderabad said:

Hi! I read your blog its useful information given by you.I am visit first time and your blog  attracked me to visited again and again.You Can also visit

March 15, 2019 2:51 AM

Packers and Movers Delhi said:

Hi!!! I read your blog its useful information given by you.I am visit first time and your blog  attracked me to visited again and again.You Can also visit

March 17, 2019 11:34 PM

qqq said:


March 27, 2019 12:43 AM said:

Check our website

by : RBN

March 27, 2019 1:34 PM

Tom Daniel said:

We do not have particular timelines yet for Insider Slow clients or Production, but we’ll upgrade you again when we’re near to releasing to those clients.

What the consumer encounter will be

When a person is migrated to REST, they will be prompted to reboot Perspective. Once they reboot, their calendars will begin syncing via REST.

April 1, 2019 4:42 AM said:

Check our website

by: RBN

April 3, 2019 12:07 PM said:

Check our website

by: RBN

April 16, 2019 1:35 PM

yanmaneee said:">">

June 29, 2020 10: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