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

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

2 Answers 2

Reset to default
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

Improve this answer
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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