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.