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

description	sys.dm_os_hosts (Transact-SQL)
title	sys.dm_os_hosts (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	08/18/2017
ms.prod	sql
ms.reviewer
ms.technology	system-objects
ms.topic	reference
f1_keywords	sys.dm_os_hosts_TSQL dm_os_hosts dm_os_hosts_TSQL sys.dm_os_hosts
dev_langs	TSQL
helpviewer_keywords	sys.dm_os_hosts dynamic management view
ms.assetid	a313ff3b-1fe9-421e-b94b-cea19c43b0e5
author	WilliamDAssafMSFT
ms.author	wiassaf

sys.dm_os_hosts (Transact-SQL)

[!INCLUDE SQL Server]

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

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: [!INCLUDEssSDWfull], [!INCLUDEssPDW] The identifier for the node that this distribution is on.

Permissions

On [!INCLUDEssNoVersion_md], requires VIEW SERVER STATE permission.
On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account or the Azure Active Directory admin account is required. On all other SQL Database service objectives, the VIEW DATABASE STATE permission is required in the database.

Remarks

[!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.

Relationship Cardinalities

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

Examples

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;  

See Also

sys.dm_os_memory_clerks (Transact-SQL)
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)