Share via

sys.dm_os_nodes (Transact-SQL)

  • Article

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.

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:

Value

Description

ONLINE

Node is online

OFFLINE

Node is offline

IDLE

Node has no pending work requests, and has entered an idle state.

IDLE_READY

Node has no pending work requests, and is ready to enter an idle state.

There are three combinable node_state_desc values:

Value

Description

DAC

This node is reserved for the Dedicated Administrative Connection.

THREAD_RESOURCES_LOW

No new threads can be created on this node because of a low-memory condition.

HOT ADDED

Indicates 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.

online_scheduler_mask

bigint

Identifies the process affinity mask for this node.

processor_group

smallint

Identifies the group of processors for this node.

Permissions

Requires VIEW SERVER STATE permission on the server.

See Also

Tasks

Configure SQL Server to Use Soft-NUMA (SQL Server)

Reference

Dynamic Management Views and Functions (Transact-SQL)

SQL Server Operating System Related Dynamic Management Views (Transact-SQL)