7

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 '18 at 13:44
  • 1
    @George.Palacios I have edited the question to include a link which outlines this option – Matthew Evans Dec 4 '18 at 13:48
  • @Zane see the edit above. Should have included links in the post – Matthew Evans Dec 4 '18 at 13:50
  • Well... Looks like it's time to try and build a test and learn something new. – Zane Dec 4 '18 at 14:00
5

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

3

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 agree to our terms of service, privacy policy and cookie policy

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