Skip to content

sys-dm-os-nodes-transact-sql.md

Latest commit

 

History

History
74 lines (65 loc) · 5.92 KB

sys-dm-os-nodes-transact-sql.md

File metadata and controls

74 lines (65 loc) · 5.92 KB

title: "sys.dm_os_nodes (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "02/13/2018" ms.prod: "sql" ms.prod_service: "database-engine, sql-database, sql-data-warehouse, pdw" ms.service: "" ms.component: "dmv's" ms.reviewer: "" ms.suite: "sql" ms.technology:

  • "database-engine" ms.tgt_pltfrm: "" ms.topic: "language-reference" f1_keywords:
  • "sys.dm_os_nodes"
  • "dm_os_nodes_TSQL"
  • "dm_os_nodes"
  • "sys.dm_os_nodes_TSQL" dev_langs:
  • "TSQL" helpviewer_keywords:
  • "sys.dm_os_nodes dynamic management view" ms.assetid: c768b67c-82a4-47f5-850b-0ea282358d50 caps.latest.revision: 33 author: "stevestein" ms.author: "sstein" manager: "craigg" ms.workload: "Inactive" monikerRange: ">= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || = sqlallproducts-allversions"

sys.dm_os_nodes (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all-md]

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 [!INCLUDEssSQL15], the [!INCLUDEssDEnoversion] 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 [!INCLUDEssSDWfull] or [!INCLUDEssPDW], use the name sys.dm_pdw_nodes_os_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. 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: [!INCLUDEssSDWfull], [!INCLUDEssPDW]

Permissions

On [!INCLUDEssNoVersion_md], requires VIEW SERVER STATE permission.
On [!INCLUDEssSDS_md], requires the VIEW DATABASE STATE permission in the database.

See Also

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