SQL SERVER – Parameter Sniffing and Bad Plan

Another day and another interesting question by a client of Comprehensive Database Performance Health Check – Do we still get a bad plan after disabling Parameter Sniffing?

SQL SERVER - Parameter Sniffing and Bad Plan sniff-800x189

Yes, it is possible to get a bad execution plan after disabling parameter sniffing.

Well, let us discuss this now. Lots of people think that parameter sniffing is the root cause of the bad execution plan. However, the truth is way far from it. The real culprit is the execution plan caching itself. When you keep the parameter sniffing on, the SQL Server engine takes the first parameter passed to the query or stored procedure and builds a plan. Later on, it uses the same plan most of the time for similar queries.

When you turn off the parameter sniffing, SQL Server takes the density vector or a fixed value as guessing of the best possible value. Once the execution plan is built with this so-called average value, the execution plan is cached in the memory and used for future values. Essentially, when you turn off the parameter sniffing of the database scoped configuration, it just tells the SQL Server engine to use the average value available for the parameters.

In simple words, if you have a parameter sniffing ON or OFF, there are always possibilities that you may end up with a poor plan. There is no replacement of OPTION (RECOMPILE).

If you liked this blog, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

, , ,
Previous Post
SQL SERVER – Parallelism in Express Edition
Next Post
SQL SERVER – Using NOEXPAND with Indexed View

Related Posts

Leave a Reply

Menu