An internal component named the SQLOS creates node structures that mimic hardware processor locality. These structures can be changed by using soft-NUMA to create custom node layouts.
Starting with SQL Server 2016 (13.x), the SQL Server Database Engine will automatically use soft-NUMA for certain hardware configurations. For more information, see Automatic Soft-NUMA.
The following table provides information about these nodes.
To call this DMV from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_os_nodes.
|Column name||Data type||Description|
|node_id||smallint||ID of the node.|
|node_state_desc||nvarchar(256)||Description of the node state. Values are displayed with the mutually exclusive values first, followed by the combinable values. For example:
Online, Thread Resources Low, Lazy Preemptive
There are four mutually exclusive node_state_desc values. They are listed below with their descriptions.
There are three combinable node_state_desc values, listed below with their descriptions.
|memory_object_address||varbinary(8)||Address of memory object associated with this node. One-to-one relation to sys.dm_os_memory_objects.memory_object_address.|
|memory_clerk_address||varbinary(8)||Address of memory clerk associated with this node. One-to-one relation to sys.dm_os_memory_clerks.memory_clerk_address.|
|io_completion_worker_address||varbinary(8)||Address of worker assigned to IO completion for this node. One-to-one relation to sys.dm_os_workers.worker_address.|
|memory_node_id||smallint||ID of the memory node this node belongs to. Many-to-one relation to sys.dm_os_memory_nodes.memory_node_id.|
|cpu_affinity_mask||bigint||Bitmap identifying the CPUs this node is associated with.|
|online_scheduler_count||smallint||Number of online schedulers that are managed by this node.|
|idle_scheduler_count||smallint||Number of online schedulers that have no active workers.|
|active_worker_count||int||Number of workers that are active on all schedulers managed by this node.|
|avg_load_balance||int||Average number of tasks per scheduler on this node.|
|timer_task_affinity_mask||bigint||Bitmap identifying the schedulers that can have timer tasks assigned to them.|
|permanent_task_affinity_mask||bigint||Bitmap identifying the schedulers that can have permanent tasks assigned to them.|
|resource_monitor_state||bit||Each node has one resource monitor assigned to it. The resource monitor can be running or idle. A value of 1 indicates running, a value of 0 indicates idle.|
|online_scheduler_mask||bigint||Identifies the process affinity mask for this node.|
|processor_group||smallint||Identifies the group of processors for this node.|
|cpu_count||int||Number of CPUs available for this node.|
|pdw_node_id||int||The identifier for the node that this distribution is on.
Applies to: Azure SQL Data Warehouse, Parallel Data Warehouse
On SQL Server, requires
VIEW SERVER STATE permission.
On SQL Database, requires the
VIEW DATABASE STATE permission in the database.