Numa Node configuration for SQL server 2016 I have a question on one of my new build sql servers where we are planning to migrate our SQL databases from older SQL2012 running on windows 2012.

New build- SQL2016 on Windows 2016

My current [SQL2012]==> When querying os_nodes dmv shows what i am assuming as 4 numa nodes config with memory node id=0,1,2,3 &amp; processor group 0,1,0,1 and scheduler count= 20 for all id's

My new [SQL2016]==> When querying os_nodes dmv shows what i am assuming as 8 numa nodes config with memory node id=0,0,0,0,1,1,1,1 &amp; processor group 0,0,0,0,1,1,1,1  and scheduler count= 9 for all id's

Below is O/P from os_nodes dmv between old and new configured SQL servers



Questions


  When viewed via task manger/ssms it shows 2 Numa nodes. Is my above
  understanding wrong of 8 Numa nodes?
  
  If those are 2 Numa nodes will i have any performance impact compared
  to current setup?


Also note softnuma_configuration_desc shows ON for sql2016

Please suggest
When viewed via task manger/ssms it shows 2 Numa nodes. Is my above understanding wrong of 8 Numa nodes?


No, SQL Server 2016 started to ship with automatic numa node partitioning (soft numa) which is what you're seeing here.


  If those are 2 Numa nodes will i have any performance impact compared to current setup?


Impossible to say. Automatic Soft-Numa was added as a default to the product starting with 2016 as certain tasks and threads are on a per-numa basis. Thus, in many environments that had high core count and low numa node count there would be an overall performance improvement by using soft-numa.

Your mileage may and will vary. If you're unsure or don't want to test the new configuration, I would suggest you turn the automatic soft-numa off for now until you can test. This is based on absolutely no data but configuration stabilization between upgrades.

If you want to turn soft-numa off, you can run the following command:

ALTER SERVER CONFIGURATION SET SOFTNUMA OFF