| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sys.dm_os_hosts (Transact-SQL) |
sys.dm_os_hosts (Transact-SQL) |
rwestMSFT |
randolphwest |
02/27/2023 |
sql |
system-objects |
reference |
|
|
|
>=sql-server-2016||>=sql-server-linux-2017||>=aps-pdw-2016||=azure-sqldw-latest |
[!INCLUDE sql-asa-pdw-asdbmi]
Returns all the hosts currently registered in an instance of [!INCLUDEssNoVersion]. This view also returns the resources that are used by these hosts.
Note
To call this from [!INCLUDEssazuresynapse-md] or [!INCLUDEssPDW], use the name sys.dm_pdw_nodes_os_hosts. [!INCLUDEsynapse-analytics-od-unsupported-syntax]
| Column name | Data type | Description |
|---|---|---|
| host_address | varbinary(8) | Internal memory address of the host object. |
| type | nvarchar(60) | Type of hosted component. For example, SOSHOST_CLIENTID_SERVERSNI= SQL Server Native Interface SOSHOST_CLIENTID_SQLOLEDB = SQL Server Native Client OLE DB Provider SOSHOST_CLIENTID_MSDART = Microsoft Data Access Run Time |
| name | nvarchar(32) | Name of the host. |
| enqueued_tasks_count | int | Total number of tasks that this host has placed onto queues in [!INCLUDEssNoVersion]. |
| active_tasks_count | int | Number of currently running tasks that this host has placed onto queues. |
| completed_ios_count | int | Total number of I/Os issued and completed through this host. |
| completed_ios_in_bytes | bigint | Total byte count of the I/Os completed through this host. |
| active_ios_count | int | Total number of I/O requests related to this host that are currently waiting to complete. |
| default_memory_clerk_address | varbinary(8) | Memory address of the memory clerk object associated with this host. For more information, see sys.dm_os_memory_clerks (Transact-SQL). |
| pdw_node_id | int | Applies to: [!INCLUDEssazuresynapse-md], [!INCLUDEssPDW] The identifier for the node that this distribution is on. |
On [!INCLUDEssNoVersion_md] 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.
Requires VIEW SERVER PERFORMANCE STATE permission on the server.
[!INCLUDEssNoVersion] allows components, such as an OLE DB provider, that are not part of the [!INCLUDEssNoVersion] executable to allocate memory and participate in non-preemptive scheduling. These components are hosted by [!INCLUDEssNoVersion], and all resources allocated by these components are tracked. Hosting allows [!INCLUDEssNoVersion] to better account for resources used by components external to the [!INCLUDEssNoVersion] executable.
| From | To | Relationship |
|---|---|---|
| sys.dm_os_hosts. default_memory_clerk_address | sys.dm_os_memory_clerks. memory_clerk_address | one to one |
| sys.dm_os_hosts. host_address | sys.dm_os_memory_clerks. host_address | one to one |
The following example determines the total amount of memory committed by a hosted component.
| Applies to: [!INCLUDEssSQL11] and later. |
SELECT h.type, SUM(mc.pages_kb) AS commited_memory
FROM sys.dm_os_memory_clerks AS mc
INNER JOIN sys.dm_os_hosts AS h
ON mc.memory_clerk_address = h.default_memory_clerk_address
GROUP BY h.type;
sys.dm_os_memory_clerks (Transact-SQL)
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)