Does the New Cardinality Estimator Reduce Bad Parameter Sniffing

Home / SQL Server 2014 / Does the New Cardinality Estimator Reduce Bad Parameter Sniffing

parameter sniffing Next question.

Although, that answer can be slightly, ever so slightly, nuanced…

Parameter sniffing is a good thing. But, like a good wine, parameter sniffing can go bad. It always comes down to your statistics. A very accurate set of statistics with very little data skew (some values that have radically more/less data than other values) and a very even distribution (most values have approximately similar cardinality), and parameter sniffing is your bestest buddy on the planet (next to a tested backup). But, introduce some data skew, let the stats get wildly out of date, or suffer from seriously uneven distribution, and suddenly your best friend is doing unspeakable things to your performance (kind of like multi-statement table valued user defined functions).

SQL Server 2014 has the first upgrade to the cardinality estimator since SQL Server 7. To get the best possible understanding of what that means and how it works, I can only recommend Joseph Sack’s white paper. It’s THE source of good information. All the new functionality within the estimator, for the most part, in most situations, will probably lead to more accurate row estimates. This means, in theory, less bad parameter sniffing. But, if you are benefiting from the old estimation engine, and in some cases you absolutely are, then you may see an increase in bad parameter sniffing. It comes right back around to your statistics and your data distribution.

So, does the new cardinality estimator lead to a reduction in bad parameter sniffing? It still mostly depends on your statistics. So, I can’t say, with certainty that the new cardinality estimator in SQL Server 2014 literally reduces bad parameter sniffing. In many instances, probably even most, the changes to the model are going to resort in better row estimates which means better choices by the optimizer. In some instances this will reverse and you may see pain, directly related to the changes in the cardinality estimator. But, overall, the fundamental behavior of parameter sniffing, again, a very positive thing, are unchanged. So, to a large degree, at least in my mind, no, the new cardinality estimator doesn’t reduce bad parameter sniffing because, most of the time, it’s due to the three causes mentioned before, out of date statistics, data skew and uneven data distribution. The new cardinality estimator doesn’t change these fundamentals, so, mostly, it won’t reduce bad parameter sniffing.

OK, fine, but what do you think?