sys.dm_exec_connections (Transact-SQL)

 

Updated: August 2, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns information about the connections established to this instance of SQL Server and the details of each connection.

NOTE: To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use sys.dm_pdw_exec_connections (Transact-SQL).

Column nameData typeDescription
session_idintIdentifies the session associated with this connection. Is nullable.
most_recent_session_idintRepresents the session ID for the most recent request associated with this connection. (SOAP connections can be reused by another session.) Is nullable.
connect_timedatetimeTimestamp when connection was established. Is not nullable.
net_transportnvarchar(40)Always returns Session when a connection has multiple active result sets (MARS) enabled.

 Note: Describes the physical transport protocol that is used by this connection. Is not nullable.
protocol_typenvarchar(40)Specifies the protocol type of the payload. It currently distinguishes between TDS (TSQL) and SOAP. Is nullable.
protocol_versionintVersion of the data access protocol associated with this connection. Is nullable.
endpoint_idintAn identifier that describes what type of connection it is. This endpoint_id can be used to query the sys.endpoints view. Is nullable.
encrypt_optionnvarchar(40)Boolean value to describe whether encryption is enabled for this connection. Is not nullable.
auth_schemenvarchar(40)Specifies SQL Server/Windows Authentication scheme used with this connection. Is not nullable.
node_affinitysmallintIdentifies the memory node to which this connection has affinity. Is not nullable.
num_readsintNumber of packet reads that have occurred over this connection. Is nullable.
num_writesintNumber of data packet writes that have occurred over this connection. Is nullable.
last_readdatetimeTimestamp when last read occurred over this connection. Is nullable.
last_writedatetimeTimestamp when last write occurred over this connection. Not Is nullable.
net_packet_sizeintNetwork packet size used for information and data transfer. Is nullable.
client_net_addressvarchar(48)Host address of the client connecting to this server. Is nullable.

Prior to V12 in Azure SQL Database, this column always returns NULL.
client_tcp_portintPort number on the client computer that is associated with this connection. Is nullable.

In Azure SQL Database, this column always returns NULL.
local_net_addressvarchar(48)Represents the IP address on the server that this connection targeted. Available only for connections using the TCP transport provider. Is nullable.

In Azure SQL Database, this column always returns NULL.
local_tcp_portintRepresents the server TCP port that this connection targeted if it were a connection using the TCP transport. Is nullable.

In Azure SQL Database, this column always returns NULL.
connection_iduniqueidentifierIdentifies each connection uniquely. Is not nullable.
parent_connection_iduniqueidentifierIdentifies the primary connection that the MARS session is using. Is nullable.
most_recent_sql_handlevarbinary(64)The SQL handle of the last request executed on this connection. The most_recent_sql_handle column is always in sync with the most_recent_session_id column. Is nullable.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

Requires VIEW SERVER STATE permission on the server.

Joins for sys.dm_exec_connections

dm_exec_sessions.session_iddm_exec_connections.session_idOne-to-one
dm_exec_requests.connection_iddm_exec_connections.connection_idMany to one
dm_broker_connections.connection_iddm_exec_connections.connection_idOne to one

Typical query to gather information about a queries own connection.

SELECT   
    c.session_id, c.net_transport, c.encrypt_option,   
    c.auth_scheme, s.host_name, s.program_name,   
    s.client_interface_name, s.login_name, s.nt_domain,   
    s.nt_user_name, s.original_login_name, c.connect_time,   
    s.login_time   
FROM sys.dm_exec_connections AS c  
JOIN sys.dm_exec_sessions AS s  
    ON c.session_id = s.session_id  
WHERE c.session_id = @@SPID;  

Dynamic Management Views and Functions (Transact-SQL)
Execution Related Dynamic Management Views and Functions (Transact-SQL)

Community Additions

ADD
Show: