I have a question to understand better how NUMA nodes help in SQL Server:
In our current set up, we have 4 NUMA nodes with 20 cores and hyper threading enabled, making a total of 40 logical processors. With memory of 400 GB, each NUMA gets 100 GB each.
It is a physical server with SQL Server 2012 Enterprise (core based licensing) on Windows 2012 R2 Standard. It has 4 sockets with 10 cores per socket and 20 logical processors per socket total 80 from error log what I see.
My understanding is maybe having 4 NUMA nodes might not perform better if we move (future SQL refresh to 2017/19) with same number of NUMA rather than I prefer 2 NUMA.
Why I am saying this because I think with 2 NUMA 400 GB will be allocated as 200 GB in each.
Therefore my understanding is it might perform better or its best to have this configuration based on how queries are switching between nodes for memory. Yes we are seeing some memory issues, I know there are other related issues which we are working but this question is more specific to NUMA set up for SQL Server.
Could someone please share their experience or may be provide more insight as I might be thinking wrong here with the belief query will have to less swapping here and there on 2 NUMA in comparison with 4 NUMA?
Is there a way or a query I can check if queries are spending time in swapping over two different NUMAs for memory and can be an issue?
Is there a way we can measure if two nodes might work better than four nodes for future design (cross-node memory access)?