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
sql-server sql-server-2017 parameter-sniffing
edited Dec 4 at 18:30
MDCCL
6,68731744
asked Dec 4 at 13:31
Matthew Evans
197116
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
sql-server sql-server-2017 parameter-sniffing
edited Dec 4 at 18:30
MDCCL
6,68731744
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
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
sql-server sql-server-2017 parameter-sniffing
edited Dec 4 at 18:30
MDCCL
6,68731744
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
sql-server sql-server-2017 parameter-sniffing
edited Dec 4 at 18:30
MDCCL
6,68731744
asked Dec 4 at 13:31
Matthew Evans
197116
edited Dec 4 at 18:30
MDCCL
6,68731744
asked Dec 4 at 13:31
Matthew Evans
197116
edited Dec 4 at 18:30
MDCCL
6,68731744
edited Dec 4 at 18:30
MDCCL
6,68731744
edited Dec 4 at 18:30
MDCCL
6,68731744
6,68731744
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
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
1
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
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
1
@George.Palacios I have edited the question to include a link which outlines this option
– Matthew Evans
Dec 4 at 13:48
@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
@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
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
answered Dec 4 at 14:28
Forrest
1,862517
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.6k478187
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
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
answered Dec 4 at 14:28
Forrest
1,862517
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
answered Dec 4 at 14:28
Forrest
1,862517
add a comment |
up vote
5
down vote
accepted
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
answered Dec 4 at 14:28
Forrest
1,862517
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
answered Dec 4 at 14:28
Forrest
1,862517
edited Dec 4 at 14:34
answered Dec 4 at 14:28
Forrest
1,862517
answered Dec 4 at 14:28
Forrest
1,862517
answered Dec 4 at 14:28
Forrest
1,862517
1,862517
add a comment |
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.6k478187
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.6k478187
add a comment |
up vote
3
down vote
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.6k478187
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.6k478187
answered Dec 4 at 14:41
Kin
52.6k478187
answered Dec 4 at 14:41
Kin
52.6k478187
answered Dec 4 at 14:41
Kin
52.6k478187
52.6k478187
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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