HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

MAX NUMA/ "Affinity" in SQL Server

Submitted by: @import:stackexchange-dba··
0
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.

Solution

You could use SQL Server's "processor affinity" to limit processing to a single NUMA node. See the ` 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.