patternsqlMinor
MAX NUMA/ "Affinity" in SQL Server
Viewed 0 times
numaaffinitysqlmaxserver
Problem
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.
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.
Solution
You could use SQL Server's "processor affinity" to limit processing to a single NUMA node. See the `
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.
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.
Context
StackExchange Database Administrators Q#167663, answer score: 2
Revisions (0)
No revisions yet.