TechNet
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

sys.dm_os_hosts (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns all the hosts currently registered in an instance of SQL Server. This view also returns the resources that are used by these hosts.

System_CAPS_ICON_note.jpg Note


To call this from Azure SQL Data Warehouse Public Preview or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_os_hosts.

Column nameData typeDescription
host_addressvarbinary(8)Internal memory address of the host object.
typenvarchar(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
namenvarchar(32)Name of the host.
enqueued_tasks_countintTotal number of tasks that this host has placed onto queues in SQL Server.
active_tasks_countintNumber of currently running tasks that this host has placed onto queues.
completed_ios_countintTotal number of I/Os issued and completed through this host.
completed_ios_in_bytesbigintTotal byte count of the I/Os completed through this host.
active_ios_countintTotal number of I/O requests related to this host that are currently waiting to complete.
default_memory_clerk_addressvarbinary(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_idintApplies to: Azure SQL Data Warehouse Public Preview, Parallel Data Warehouse

The identifier for the node that this distribution is on.

Requires VIEW SERVER STATE permission on the server.

SQL Server allows components, such as an OLE DB provider, that are not part of the SQL Server executable to allocate memory and participate in non-preemptive scheduling. These components are hosted by SQL Server, and all resources allocated by these components are tracked. Hosting allows SQL Server to better account for resources used by components external to the SQL Server executable.

FromToRelationship
sys.dm_os_hosts. default_memory_clerk_addresssys.dm_os_memory_clerks. memory_clerk_addressone to one
sys.dm_os_hosts. host_addresssys.dm_os_memory_clerks. host_addressone to one

The following example determines the total amount of memory committed by a hosted component.

Applies to: SQL Server 2012 through SQL Server 2016.
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;

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

Community Additions

ADD
Show:
© 2016 Microsoft