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. |
Permissions
Requires VIEW SERVER STATE permission on the server.