A little while ago, I was doing SQL Server consultancy work for a large organisation here in Perth, Western Australia. They had a sizeable physical SQL Server machine for their business reporting needs: 48 cores (4 sockets of 12 cores each and 384GB of RAM).
I discovered that despite the company having paid for Enterprise licenses for 48 cores, the licensing had not been applied, and so only 40 cores were actually in use! It had been that way for almost 2 years…
In fact, the situation is worse than it first seems: not only were 20% of the cores not being used, but the cores were partitioned into 4 NUMA groups, having 3 groups of 12 cores and one group of 4 cores. With the workload distributed equally over the 4 NUMA groups, this was obviously very unbalanced, and detrimental to the server’s throughput.
In addition, ‘max degree of parallelism’ was set to its default value, which means that a parallel query that spans the 12 core and 4 core NUMA groups will very likely have threads waiting in the 12 core NUMA group and possibly incur foreign memory accesses.
This is just one of the NUMA configuration checks that SQLFrontline runs.