What is the num of NUMA nodes for MAXDOP calculation when there is discrepancy between various ways to count the NUMA nodes?



  • Querying the dm_os_performance_counters shows that I have 2 NUMA nodes:

    select *
    from sys.dm_os_performance_counters
    where object_name = 'SQLServer:Buffer Node'
    and counter_name = 'Page life expectancy'
    

    Querying the sys.dm_os_sys_info shows:

    Numa node count 4
    CPU count 24
    Softnumae_configuration 1
    Socket_count 24
    Cores per socket 1
    

    enter image description here

    Where as when I right click on SQL server properties, click on Processors it shows 2 NUMA nodes, each with 12 CPUs.

    enter image description here

    enter image description here

    SQL error log:

    SQL Server detected 24 sockets with 1 cores per socket and 1 logical processors 
    per socket. 24 total logical processors.
    

    Automatic soft-NUMA was enabled because SQL server has detected hardware NUMA nodes
    with greater than 8 physical cores.

    enter image description here

    enter image description here

    So what is the final value of NUMA and CPUs?

    I am asking because it is recommended to have the MAX DOP configured based on the number of NUMA nodes and the processors per node ( https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15#Recommendations ). How to decide on the MAX DOP when there is discrepancy between processor count reported in sys.dm_os_sys_info vs server properties vs SQL error logs?



  • You’re seeing soft-NUMA counts.

    As mentioned in this https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option …

    Starting with SQL Server 2016 (13.x), during service startup if the Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default. The Database Engine places logical processors from the same physical core into different soft-NUMA nodes. The recommendations in the table below are aimed at keeping all the worker threads of a parallel query within the same soft-NUMA node.

    In your case, it looks like you have 2 CPUs, each with 12 cores. With those being greater than 8, SQL Server is breaking them up into 4 soft-NUMA nodes at startup, each with 6 cores. With this configuration, you’d want to set MAXDOP to 6.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2