Skip to content

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

Latest commit

 

History

History
84 lines (68 loc) · 4.85 KB

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

File metadata and controls

84 lines (68 loc) · 4.85 KB
title sys.dm_os_hosts (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 08/18/2017
ms.prod sql-non-specified
ms.reviewer
ms.suite
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic language-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
caps.latest.revision 35
author JennieHubbard
ms.author jhubbard
manager jhubbard

sys.dm_os_hosts (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx-md]

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 [!INCLUDEssSDS_md] Premium Tiers, requires the VIEW DATABASE STATE permission in the database. On [!INCLUDEssSDS_md] Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account.

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] through [!INCLUDEssCurrent]. 
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)