Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
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.
