3

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.

1

...a way to tell SQL Server to stay inside one NUMA node...

There's no hint that does that, but SQL Server can be configured to do what you describe. See SQL Server Parallel Query Placement Decision Logic by the Microsoft SQL Server Customer Service and Support Team for details. The table below is reproduced from that post:

Thread placement logic

Also, from NUMA Connection Affinity and Parallel Queries on the same blog:

  • If the DOP for the query can be satisfied by a single node the ideal node (most workers, schedulers and memory available at the time the query is started) is selected. The controlling worker can be on a different node than the ideal. For example you could have NUMA port affinity established for NODE 1 but the parallel query executes on NODE 4 because 4 was the ideal node. This is not a problem because the majority of the work is done by the parallel workers which are located together on a node.
2

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.

  • 1
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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