sys.dm_os_nodes (Transact-SQL)
An internal component named the SQL OS creates node structures that mimic hardware processor locality. These structures can be changed by using soft-NUMA to create custom node layouts.
The following table provides information about these 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: ValueDescription ONLINENode is online OFFLINENode is offline IDLENode has no pending work requests, and has entered an idle state. IDLE_READYNode has no pending work requests, and is ready to enter an idle state. There are three combinable node_state_desc values: ValueDescription DACThis node is reserved for the Dedicated Administrative Connection. THREAD_RESOURCES_LOWNo new threads can be created on this node because of a low-memory condition. HOT ADDEDIndicates the nodes were added in response to a hot add CPU event. |
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 aremanaged 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. |
