Parameter sniffing = On with Parameterization = forced. Which takes precedence?

up vote
7
down vote

favorite

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

edited Dec 4 at 18:30

MDCCL

6,65731744

asked Dec 4 at 13:31

Matthew Evans

197116

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

add a comment |

up vote
7
down vote

favorite

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

edited Dec 4 at 18:30

MDCCL

6,65731744

asked Dec 4 at 13:31

Matthew Evans

197116

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

add a comment |

up vote
7
down vote

favorite

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

edited Dec 4 at 18:30

MDCCL

6,65731744

asked Dec 4 at 13:31

Matthew Evans

197116

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

sql-server sql-server-2017 parameter-sniffing

edited Dec 4 at 18:30

MDCCL

6,65731744

asked Dec 4 at 13:31

Matthew Evans

197116

edited Dec 4 at 18:30

MDCCL

6,65731744

asked Dec 4 at 13:31

Matthew Evans

197116

edited Dec 4 at 18:30

MDCCL

6,65731744

edited Dec 4 at 18:30

MDCCL

6,65731744

edited Dec 4 at 18:30

MDCCL

6,65731744

asked Dec 4 at 13:31

Matthew Evans

197116

asked Dec 4 at 13:31

Matthew Evans

197116

asked Dec 4 at 13:31

Matthew Evans

197116

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

add a comment |

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

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

@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

add a comment |

2 Answers
2

active

oldest

votes

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

edited Dec 4 at 14:34

answered Dec 4 at 14:28

Forrest

1,787516

add a comment |

up vote
3
down vote

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.

answered Dec 4 at 14:41

Kin

52.5k478187

add a comment |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f224088%2fparameter-sniffing-on-with-parameterization-forced-which-takes-precedence%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

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

edited Dec 4 at 14:34

answered Dec 4 at 14:28

Forrest

1,787516

add a comment |

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

edited Dec 4 at 14:34

answered Dec 4 at 14:28

Forrest

1,787516

add a comment |

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

edited Dec 4 at 14:34

answered Dec 4 at 14:28

Forrest

1,787516

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

edited Dec 4 at 14:34

answered Dec 4 at 14:28

Forrest

1,787516

edited Dec 4 at 14:34

answered Dec 4 at 14:28

Forrest

1,787516

answered Dec 4 at 14:28

Forrest

1,787516

answered Dec 4 at 14:28

Forrest

1,787516

add a comment |

up vote
3
down vote

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.

answered Dec 4 at 14:41

Kin

52.5k478187

add a comment |

up vote
3
down vote

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.

answered Dec 4 at 14:41

Kin

52.5k478187

add a comment |

up vote
3
down vote

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.

answered Dec 4 at 14:41

Kin

52.5k478187

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.

answered Dec 4 at 14:41

Kin

52.5k478187

answered Dec 4 at 14:41

Kin

52.5k478187

answered Dec 4 at 14:41

Kin

52.5k478187

answered Dec 4 at 14:41

Kin

52.5k478187

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Database Administrators Stack Exchange!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Cfrgtkky