sys.dm_os_nodes (Transact-SQL)


Updated: August 9, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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.

NOTE: To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_os_nodes.

Column nameData typeDescription
node_idsmallintID of the node.
node_state_descnvarchar(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.

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 five combinable node_state_desc values, listed below with their descriptions.

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.

AUTOSOFT: This node is created by automatic soft-NUMA.

MANUAL SOFT: This node is created by soft-NUMA with customer defined node size.
memory_object_addressvarbinary(8)Address of memory object associated with this node. One-to-one relation to sys.dm_os_memory_objects.memory_object_address.
memory_clerk_addressvarbinary(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_addressvarbinary(8)Address of worker assigned to IO completion for this node. One-to-one relation to sys.dm_os_workers.worker_address.
memory_node_idsmallintID of the memory node this node belongs to. Many-to-one relation to sys.dm_os_memory_nodes.memory_node_id.
cpu_affinity_maskbigintBitmap identifying the CPUs this node is associated with.
online_scheduler_countsmallintNumber of online schedulers that aremanaged by this node.
idle_scheduler_countsmallintNumber of online schedulers that have no active workers.
active_worker_countintNumber of workers that are active on all schedulers managed by this node.
avg_load_balanceintAverage number of tasks per scheduler on this node.
timer_task_affinity_maskbigintBitmap identifying the schedulers that can have timer tasks assigned to them.
permanent_task_affinity_maskbigintBitmap identifying the schedulers that can have permanent tasks assigned to them.
resource_monitor_statebitEach 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_maskbigintIdentifies the process affinity mask for this node.
processor_groupsmallintIdentifies the group of processors for this node.
pdw_node_idintThe 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 the server.

On SQL Database Premium Tiers requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers requires the SQL Database admin account.

Dynamic Management Views and Functions (Transact-SQL)
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)
Soft-NUMA (SQL Server)

Community Additions