2

We have clustered servers, each of which has 32 CPU cores and 512 GB memory. The cluster is home to 7 instances of SQL Server 2012 Standard Edition.

With Standard Edition allowing a maximum of 64 GB assigned to any one instance, I was wondering if we should be changing the NUMA setting to maximize utilization of CPU cores? Specifically, I'm thinking of modifying processor affinity.

I have done loads of reading where foreign memory access has an overhead to that of local memory access.

|improve this question
  • 4
    Are you facing any issue as of now, and specifically one due to foreign memory access – Shanky Mar 21 '17 at 5:10
  • Which setting and what are you considering changing it to? – Kevin3NF Mar 21 '17 at 12:00
  • @Shanky, not too sure; but when i span out the memory allocation and map it to default 16 COR utilized by Standard edition. – Devendra Singh Mar 22 '17 at 6:29
  • 1
    @Kevin3NF, I refering to processor affinity mask – Devendra Singh Mar 22 '17 at 6:30
  • Are all 7 instances serving unrelated databases, or are the databases shards of the same database? – Max Vernon Mar 22 '17 at 12:26
0

While it is true that accessing memory in a remote NUMA node is slower than accessing memory in the local NUMA node, the difference is typically so slight as to be completely eclipsed by other performance or configuration issues.

Confirm you have configured the maximum degree of parallelism correctly, since that controls how many threads SQL Server will use for queries that will benefit from parallel processing. SQL Server will attempt to process an individual query within NUMA node memory boundaries, if the number of threads is less than or equal to the number of cores in a NUMA node.

You'll also want to ensure you have the maximum server memory configured correctly, leaving enough room for each SQL Server instance, and the operating system, etc.

You should also probably have lock pages in memory configured, to ensure memory is not being stolen from SQL Server.

|improve this answer
  • Thanks Max. I agree with what you mentioned up there and were part of our stabilization process. we have configured MAXDOP to restrict the parallel query thread along with Cost threshold of Parallelism (50). we also ensured to review the min and max memory configured of all our sql boxes. To confirm we lock pages in memory configured as well. So if i understand it correctly you are implying that we let sql server decide what it wants to do with the resources available? – Devendra Singh Mar 22 '17 at 6:27
  • In all but the most extreme cases, SQL Server does an excellent job managing resources, assuming you have made the tweaks mentioned in my answer. You may want to look at configuring processor affinity if you're concerned about extreme CPU contention. If you have a specific performance issue or concern, please add the details to your question. – Max Vernon Mar 22 '17 at 12:20
  • Will do Max.. i was just looking for general advise over the subject. Appreciate the advise received from the community. – Devendra Singh Mar 24 '17 at 10:00

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.