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?
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.
- Queries Using Specific Index – SQL in Sixty Seconds #180
- Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
- One Scan for 3 Count Sum – SQL in Sixty Seconds #178
- SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
- COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
Reference:Â Pinal Dave (http://blog.SQLAuthority.com)
