These two settings seem to contradict each other. One forces plan parameterization so that only one plan gets created. The other allows for multiple plans

If you have Parameterization = forced, should parameter sniffing be false, or does one take precedence?

Additional details

Regarding the comments below, parameter sniffing is a database scoped option as of SS 2016. (was a surprise to me too). See also: SQLShack: SQL Server 2016 Parameter Sniffing

  • 1
    Could you post what setting name the parameter sniffing one is you're referring to? I'm not aware of such a setting. – George.Palacios Dec 4 at 13:44
  • 1
    @George.Palacios I have edited the question to include a link which outlines this option – Matthew Evans Dec 4 at 13:48
  • @Zane see the edit above. Should have included links in the post – Matthew Evans Dec 4 at 13:50
  • Well... Looks like it's time to try and build a test and learn something new. – Zane Dec 4 at 14:00
up vote 5 down vote accepted

These do not conflict. Parameter sniffing is the process that uses the parameter values when building a plan. Forced parameterization turns literals into parameters in queries.

So having both on will turn literals into parameters, but not use those values when estimating cardinality.

More on parameter sniffing

More on forced parameterization

Parameterization and sniffing are separate activities. An ad-hoc statement can be parameterized by SQL Server without parameter values being sniffed.

Paul White has an excellent blog post on : Parameter Sniffing, Embedding, and the RECOMPILE Options which covers both Parameter Sniffing and parameterization in detail with examples.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.