sys.dm_os_nodes (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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.

Note

Starting with SQL Server 2016 (13.x), the SQL Server Database Engine will automatically use soft-NUMA for certain hardware configurations. For more information, see Automatic Soft-NUMA.

The following table provides information about these nodes.

Note

To call this DMV from Azure Synapse Analytics or Analytics Platform System (PDW), use the name sys.dm_pdw_nodes_os_nodes. This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.

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. 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 three 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.
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 are managed 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.
cpu_count int Number of CPUs available for this node.
pdw_node_id int The identifier for the node that this distribution is on.

Applies to: Azure Synapse Analytics, Analytics Platform System (PDW)

Permissions

On SQL Server and SQL Managed Instance, requires VIEW SERVER STATE permission.

On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

See also

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