THIS TOPIC APPLIES TO: SQL Server (starting with 2008) Azure SQL Database Azure SQL Data Warehouse Parallel 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.
To call this from Azure SQL Data Warehouse Public Preview or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_os_hosts.
Internal memory address of the host object.
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 of the host.
Total number of tasks that this host has placed onto queues in SQL Server.
Number of currently running tasks that this host has placed onto queues.
Total number of I/Os issued and completed through this host.
Total byte count of the I/Os completed through this host.
Total number of I/O requests related to this host that are currently waiting to complete.
Memory address of the memory clerk object associated with this host. For more information, see sys.dm_os_memory_clerks (Transact-SQL).
Applies to: Azure SQL Data Warehouse Public Preview, Parallel Data Warehouse
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.
one to one
one 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;