MAX NUMA/ “Affinity” in SQL Server
My Google-Fu is failing me...
Is there now, or possibly in the works a way to tell SQL Server to stay inside one NUMA node, either server wide or at the query level? Basically the same functionality as MAXDOP, but MAXNUMA?
Right now, unless I am totally missing it, when a query goes parallel it can use all of the processors it can see. Just wondering if there is a way to restrict it to one or two or 'x' NUMA nodes. I may not really want my queries spread out over 80 logical processors :)
This is mostly a curiosity rather than a "help me break my broken thing" question.
Thanks,
Kevin3NF
sql-server numa
asked Mar 20 '17 at 17:39
Kevin3NFKevin3NF
1,0411614
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
My Google-Fu is failing me...
Is there now, or possibly in the works a way to tell SQL Server to stay inside one NUMA node, either server wide or at the query level? Basically the same functionality as MAXDOP, but MAXNUMA?
Right now, unless I am totally missing it, when a query goes parallel it can use all of the processors it can see. Just wondering if there is a way to restrict it to one or two or 'x' NUMA nodes. I may not really want my queries spread out over 80 logical processors :)
This is mostly a curiosity rather than a "help me break my broken thing" question.
Thanks,
Kevin3NF
sql-server numa
asked Mar 20 '17 at 17:39
Kevin3NFKevin3NF
1,0411614
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
There's no hint that does that, but SQL Server does attempt to do what you describe. Check out this blog: blogs.msdn.microsoft.com/psssql/2016/03/04/…
– Erik Darling
Mar 20 '17 at 17:44
Thanks...safe to assume Node in that link is NUMA node? Not sure how it could be anything else but it wouldn't be the first time Mr. Dorr confused me :)
– Kevin3NF
Mar 20 '17 at 17:56
Yeah, that's a reasonable assumption here!
– Erik Darling
Mar 20 '17 at 17:58
add a comment |
My Google-Fu is failing me...
Is there now, or possibly in the works a way to tell SQL Server to stay inside one NUMA node, either server wide or at the query level? Basically the same functionality as MAXDOP, but MAXNUMA?
Right now, unless I am totally missing it, when a query goes parallel it can use all of the processors it can see. Just wondering if there is a way to restrict it to one or two or 'x' NUMA nodes. I may not really want my queries spread out over 80 logical processors :)
This is mostly a curiosity rather than a "help me break my broken thing" question.
Thanks,
Kevin3NF
sql-server numa
asked Mar 20 '17 at 17:39
Kevin3NFKevin3NF
1,0411614
My Google-Fu is failing me...
Is there now, or possibly in the works a way to tell SQL Server to stay inside one NUMA node, either server wide or at the query level? Basically the same functionality as MAXDOP, but MAXNUMA?
Right now, unless I am totally missing it, when a query goes parallel it can use all of the processors it can see. Just wondering if there is a way to restrict it to one or two or 'x' NUMA nodes. I may not really want my queries spread out over 80 logical processors :)
This is mostly a curiosity rather than a "help me break my broken thing" question.
Thanks,
Kevin3NF
sql-server numa
sql-server numa
asked Mar 20 '17 at 17:39
Kevin3NFKevin3NF
1,0411614
asked Mar 20 '17 at 17:39
Kevin3NFKevin3NF
1,0411614
edited Mar 20 '17 at 17:57
Kevin3NF
asked Mar 20 '17 at 17:39
Kevin3NFKevin3NF
1,0411614
asked Mar 20 '17 at 17:39
Kevin3NFKevin3NF
1,0411614
asked Mar 20 '17 at 17:39
Kevin3NFKevin3NF
1,0411614
1,0411614
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
There's no hint that does that, but SQL Server does attempt to do what you describe. Check out this blog: blogs.msdn.microsoft.com/psssql/2016/03/04/…
– Erik Darling
Mar 20 '17 at 17:44
Thanks...safe to assume Node in that link is NUMA node? Not sure how it could be anything else but it wouldn't be the first time Mr. Dorr confused me :)
– Kevin3NF
Mar 20 '17 at 17:56
Yeah, that's a reasonable assumption here!
– Erik Darling
Mar 20 '17 at 17:58
add a comment |
1
There's no hint that does that, but SQL Server does attempt to do what you describe. Check out this blog: blogs.msdn.microsoft.com/psssql/2016/03/04/…
– Erik Darling
Mar 20 '17 at 17:44
Thanks...safe to assume Node in that link is NUMA node? Not sure how it could be anything else but it wouldn't be the first time Mr. Dorr confused me :)
– Kevin3NF
Mar 20 '17 at 17:56
Yeah, that's a reasonable assumption here!
– Erik Darling
Mar 20 '17 at 17:58
1
1
There's no hint that does that, but SQL Server does attempt to do what you describe. Check out this blog: blogs.msdn.microsoft.com/psssql/2016/03/04/…
– Erik Darling
Mar 20 '17 at 17:44
There's no hint that does that, but SQL Server does attempt to do what you describe. Check out this blog: blogs.msdn.microsoft.com/psssql/2016/03/04/…
– Erik Darling
Mar 20 '17 at 17:44
Thanks...safe to assume Node in that link is NUMA node? Not sure how it could be anything else but it wouldn't be the first time Mr. Dorr confused me :)
– Kevin3NF
Mar 20 '17 at 17:56
Thanks...safe to assume Node in that link is NUMA node? Not sure how it could be anything else but it wouldn't be the first time Mr. Dorr confused me :)
– Kevin3NF
Mar 20 '17 at 17:56
Yeah, that's a reasonable assumption here!
– Erik Darling
Mar 20 '17 at 17:58
Yeah, that's a reasonable assumption here!
– Erik Darling
Mar 20 '17 at 17:58
add a comment |
1 Answer
1
active
oldest
votes
You could use SQL Server's "processor affinity" to limit processing to a single NUMA node. See the <process affinity> section of the ALTER SERVER CONFIGURATION command on this MSDN page for more details.
By default, SQL Server attempts to keep query processing contained inside a given NUMA node if the query goes parallel where MAXDOP <= number of cores per NUMA node. The indication here is to configure MAXDOP to a reasonable value; certainly not more than the number of cores per NUMA node. There are a load of resources to help with calculating a reasonable MAXDOP; including the answers on my question.
edited Apr 13 '17 at 12:42
Community♦
1
answered Mar 23 '17 at 19:12
Max VernonMax Vernon
50.2k13111221
Wouldn't that limit everything to a specific NUMA node? If I have 4 NUMA nodes, I'd like each query to stay in one, but all be usable...this is more of a curiosity than anything else. Was hoping someone would know this was coming in future release/SP :)
– Kevin3NF
Mar 23 '17 at 19:30
Yes, processor affinity, if setup that way, would limit all queries to a specific NUMA node. My 2nd paragraph deals with ensuring SQL Server attempts to keep a query within the bounds of a NUMA node, if MAXDOP is configured correctly. I very much doubt Microsoft will provide a way to ensure a query always runs on a single NUMA node since it's entirely possible you want it to run on two, or more, nodes.
– Max Vernon
Mar 23 '17 at 19:38
Also, if you had 4 NUMA nodes, you could configure 4 SQL Server instances on the server, each with affinity to a single NUMA node; which is how some very extreme servers are configured. Of course, that brings other complexity, such as how to deal with data sharding, etc.
– Max Vernon
Mar 23 '17 at 19:39
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',
autoActivateHeartbeat: false,
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%2f167663%2fmax-numa-affinity-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You could use SQL Server's "processor affinity" to limit processing to a single NUMA node. See the <process affinity> section of the ALTER SERVER CONFIGURATION command on this MSDN page for more details.
By default, SQL Server attempts to keep query processing contained inside a given NUMA node if the query goes parallel where MAXDOP <= number of cores per NUMA node. The indication here is to configure MAXDOP to a reasonable value; certainly not more than the number of cores per NUMA node. There are a load of resources to help with calculating a reasonable MAXDOP; including the answers on my question.
edited Apr 13 '17 at 12:42
Community♦
1
answered Mar 23 '17 at 19:12
Max VernonMax Vernon
50.2k13111221
Wouldn't that limit everything to a specific NUMA node? If I have 4 NUMA nodes, I'd like each query to stay in one, but all be usable...this is more of a curiosity than anything else. Was hoping someone would know this was coming in future release/SP :)
– Kevin3NF
Mar 23 '17 at 19:30
Yes, processor affinity, if setup that way, would limit all queries to a specific NUMA node. My 2nd paragraph deals with ensuring SQL Server attempts to keep a query within the bounds of a NUMA node, if MAXDOP is configured correctly. I very much doubt Microsoft will provide a way to ensure a query always runs on a single NUMA node since it's entirely possible you want it to run on two, or more, nodes.
– Max Vernon
Mar 23 '17 at 19:38
Also, if you had 4 NUMA nodes, you could configure 4 SQL Server instances on the server, each with affinity to a single NUMA node; which is how some very extreme servers are configured. Of course, that brings other complexity, such as how to deal with data sharding, etc.
– Max Vernon
Mar 23 '17 at 19:39
add a comment |
You could use SQL Server's "processor affinity" to limit processing to a single NUMA node. See the <process affinity> section of the ALTER SERVER CONFIGURATION command on this MSDN page for more details.
By default, SQL Server attempts to keep query processing contained inside a given NUMA node if the query goes parallel where MAXDOP <= number of cores per NUMA node. The indication here is to configure MAXDOP to a reasonable value; certainly not more than the number of cores per NUMA node. There are a load of resources to help with calculating a reasonable MAXDOP; including the answers on my question.
edited Apr 13 '17 at 12:42
Community♦
1
answered Mar 23 '17 at 19:12
Max VernonMax Vernon
50.2k13111221
Wouldn't that limit everything to a specific NUMA node? If I have 4 NUMA nodes, I'd like each query to stay in one, but all be usable...this is more of a curiosity than anything else. Was hoping someone would know this was coming in future release/SP :)
– Kevin3NF
Mar 23 '17 at 19:30
Yes, processor affinity, if setup that way, would limit all queries to a specific NUMA node. My 2nd paragraph deals with ensuring SQL Server attempts to keep a query within the bounds of a NUMA node, if MAXDOP is configured correctly. I very much doubt Microsoft will provide a way to ensure a query always runs on a single NUMA node since it's entirely possible you want it to run on two, or more, nodes.
– Max Vernon
Mar 23 '17 at 19:38
Also, if you had 4 NUMA nodes, you could configure 4 SQL Server instances on the server, each with affinity to a single NUMA node; which is how some very extreme servers are configured. Of course, that brings other complexity, such as how to deal with data sharding, etc.
– Max Vernon
Mar 23 '17 at 19:39
add a comment |
You could use SQL Server's "processor affinity" to limit processing to a single NUMA node. See the <process affinity> section of the ALTER SERVER CONFIGURATION command on this MSDN page for more details.
By default, SQL Server attempts to keep query processing contained inside a given NUMA node if the query goes parallel where MAXDOP <= number of cores per NUMA node. The indication here is to configure MAXDOP to a reasonable value; certainly not more than the number of cores per NUMA node. There are a load of resources to help with calculating a reasonable MAXDOP; including the answers on my question.
edited Apr 13 '17 at 12:42
Community♦
1
answered Mar 23 '17 at 19:12
Max VernonMax Vernon
50.2k13111221
You could use SQL Server's "processor affinity" to limit processing to a single NUMA node. See the <process affinity> section of the ALTER SERVER CONFIGURATION command on this MSDN page for more details.
By default, SQL Server attempts to keep query processing contained inside a given NUMA node if the query goes parallel where MAXDOP <= number of cores per NUMA node. The indication here is to configure MAXDOP to a reasonable value; certainly not more than the number of cores per NUMA node. There are a load of resources to help with calculating a reasonable MAXDOP; including the answers on my question.
edited Apr 13 '17 at 12:42
Community♦
1
answered Mar 23 '17 at 19:12
Max VernonMax Vernon
50.2k13111221
edited Apr 13 '17 at 12:42
Community♦
1
edited Apr 13 '17 at 12:42
Community♦
1
edited Apr 13 '17 at 12:42
Community♦
1
1
answered Mar 23 '17 at 19:12
Max VernonMax Vernon
50.2k13111221
answered Mar 23 '17 at 19:12
Max VernonMax Vernon
50.2k13111221
answered Mar 23 '17 at 19:12
Max VernonMax Vernon
50.2k13111221
50.2k13111221
Wouldn't that limit everything to a specific NUMA node? If I have 4 NUMA nodes, I'd like each query to stay in one, but all be usable...this is more of a curiosity than anything else. Was hoping someone would know this was coming in future release/SP :)
– Kevin3NF
Mar 23 '17 at 19:30
Yes, processor affinity, if setup that way, would limit all queries to a specific NUMA node. My 2nd paragraph deals with ensuring SQL Server attempts to keep a query within the bounds of a NUMA node, if MAXDOP is configured correctly. I very much doubt Microsoft will provide a way to ensure a query always runs on a single NUMA node since it's entirely possible you want it to run on two, or more, nodes.
– Max Vernon
Mar 23 '17 at 19:38
Also, if you had 4 NUMA nodes, you could configure 4 SQL Server instances on the server, each with affinity to a single NUMA node; which is how some very extreme servers are configured. Of course, that brings other complexity, such as how to deal with data sharding, etc.
– Max Vernon
Mar 23 '17 at 19:39
add a comment |
Wouldn't that limit everything to a specific NUMA node? If I have 4 NUMA nodes, I'd like each query to stay in one, but all be usable...this is more of a curiosity than anything else. Was hoping someone would know this was coming in future release/SP :)
– Kevin3NF
Mar 23 '17 at 19:30
Yes, processor affinity, if setup that way, would limit all queries to a specific NUMA node. My 2nd paragraph deals with ensuring SQL Server attempts to keep a query within the bounds of a NUMA node, if MAXDOP is configured correctly. I very much doubt Microsoft will provide a way to ensure a query always runs on a single NUMA node since it's entirely possible you want it to run on two, or more, nodes.
– Max Vernon
Mar 23 '17 at 19:38
Also, if you had 4 NUMA nodes, you could configure 4 SQL Server instances on the server, each with affinity to a single NUMA node; which is how some very extreme servers are configured. Of course, that brings other complexity, such as how to deal with data sharding, etc.
– Max Vernon
Mar 23 '17 at 19:39
Wouldn't that limit everything to a specific NUMA node? If I have 4 NUMA nodes, I'd like each query to stay in one, but all be usable...this is more of a curiosity than anything else. Was hoping someone would know this was coming in future release/SP :)
– Kevin3NF
Mar 23 '17 at 19:30
Wouldn't that limit everything to a specific NUMA node? If I have 4 NUMA nodes, I'd like each query to stay in one, but all be usable...this is more of a curiosity than anything else. Was hoping someone would know this was coming in future release/SP :)
– Kevin3NF
Mar 23 '17 at 19:30
Yes, processor affinity, if setup that way, would limit all queries to a specific NUMA node. My 2nd paragraph deals with ensuring SQL Server attempts to keep a query within the bounds of a NUMA node, if MAXDOP is configured correctly. I very much doubt Microsoft will provide a way to ensure a query always runs on a single NUMA node since it's entirely possible you want it to run on two, or more, nodes.
– Max Vernon
Mar 23 '17 at 19:38
Yes, processor affinity, if setup that way, would limit all queries to a specific NUMA node. My 2nd paragraph deals with ensuring SQL Server attempts to keep a query within the bounds of a NUMA node, if MAXDOP is configured correctly. I very much doubt Microsoft will provide a way to ensure a query always runs on a single NUMA node since it's entirely possible you want it to run on two, or more, nodes.
– Max Vernon
Mar 23 '17 at 19:38
Also, if you had 4 NUMA nodes, you could configure 4 SQL Server instances on the server, each with affinity to a single NUMA node; which is how some very extreme servers are configured. Of course, that brings other complexity, such as how to deal with data sharding, etc.
– Max Vernon
Mar 23 '17 at 19:39
Also, if you had 4 NUMA nodes, you could configure 4 SQL Server instances on the server, each with affinity to a single NUMA node; which is how some very extreme servers are configured. Of course, that brings other complexity, such as how to deal with data sharding, etc.
– Max Vernon
Mar 23 '17 at 19:39
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.
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%2f167663%2fmax-numa-affinity-in-sql-server%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
There's no hint that does that, but SQL Server does attempt to do what you describe. Check out this blog: blogs.msdn.microsoft.com/psssql/2016/03/04/…
– Erik Darling
Mar 20 '17 at 17:44
Thanks...safe to assume Node in that link is NUMA node? Not sure how it could be anything else but it wouldn't be the first time Mr. Dorr confused me :)
– Kevin3NF
Mar 20 '17 at 17:56
Yeah, that's a reasonable assumption here!
– Erik Darling
Mar 20 '17 at 17:58