| 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.reviewer | |||||
| ms.technology | system-objects | ||||
| ms.topic | language-reference | ||||
| f1_keywords |
|
||||
| dev_langs |
|
||||
| helpviewer_keywords |
|
||||
| ms.assetid | c768b67c-82a4-47f5-850b-0ea282358d50 | ||||
| author | stevestein | ||||
| ms.author | sstein | ||||
| monikerRange | >=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!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.
There are three combinable node_state_desc values, listed below with their descriptions.
|
| 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] |
On [!INCLUDEssNoVersion_md], requires VIEW SERVER STATE permission.
On [!INCLUDEssSDS_md], requires the VIEW DATABASE STATE permission in the database.
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)
Soft-NUMA (SQL Server)