Share via

sys.dm_os_hosts

  • Article

Returns all the hosts currently registered in an instance of SQL Server. This view also returns the resources that are used by these 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 Native Client Interface

SOSHOST_CLIENTID_SQLOLEDB = SQL 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 SQL Server.

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.

Permissions

Requires VIEW SERVER STATE permission on the server.

Remarks

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.

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.

SELECT h.type, SUM(single_pages_kb + multi_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

Reference

Dynamic Management Views and Functions
sys.dm_os_memory_clerks
SQL Server Operating System Related Dynamic Management Views

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
New content:
  • Added the "Examples" section.
  • Added the "Relationship Cardinalities" section.
Updated content:
  • Corrected the definition of type.

14 April 2006
Updated content:
  • Removed SOSHOST_CLIENTID_SQLCLR from the description of type.