2

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 & 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 & 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

dmv os_nodes_o/p

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

3

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

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.