Share via

sys.dm_exec_connections (Transact-SQL)

  • Article

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

Column name

Data type

Description

session_id

int

Identifies the session associated with this connection. Is nullable.

most_recent_session_id

int

Represents the session ID for the most recent request associated with this connection. (SOAP connections can be reused by another session.) Is nullable.

connect_time

datetime

Timestamp when connection was established. Is not nullable.

net_transport

nvarchar(40)

Describes the physical transport protocol that is used by this connection. Is not nullable.

Note

Always returns Session when a connection has multiple active result sets (MARS) enabled.

protocol_type

nvarchar(40)

Specifies the protocol type of the payload. It currently distinguishes between TDS (TSQL) and SOAP. Is nullable.

protocol_version

int

Version of the data access protocol associated with this connection. Is nullable.

endpoint_id

int

An identifier that describes what type of connection it is. This endpoint_id can be used to query the sys.endpoints view. Is nullable.

encrypt_option

nvarchar(40)

Boolean value to describe whether encryption is enabled for this connection. Is not nullable.

auth_scheme

nvarchar(40)

Specifies SQL Server/Windows Authentication scheme used with this connection. Is not nullable.

node_affinity

smallint

Identifies the memory node to which this connection has affinity. Is not nullable.

num_reads

int

Number of packet reads that have occurred over this connection. Is nullable.

num_writes

int

Number of data packet writes that have occurred over this connection. Is nullable.

last_read

datetime

Timestamp when last read occurred over this connection. Is nullable.

last_write

datetime

Timestamp when last write occurred over this connection. Not Is nullable.

net_packet_size

int

Network packet size used for information and data transfer. Is nullable.

client_net_address

varchar(48)

Host address of the client connecting to this server. Is nullable.

client_tcp_port

int

Port number on the client computer that is associated with this connection. Is nullable.

local_net_address

varchar(48)

Represents the IP address on the server that this connection targeted. Available only for connections using the TCP transport provider. Is nullable.

local_tcp_port

int

Represents the server TCP port that this connection targeted if it were a connection using the TCP transport. Is nullable.

connection_id

uniqueidentifier

Identifies each connection uniquely. Is not nullable.

parent_connection_id

uniqueidentifier

Identifies the primary connection that the MARS session is using. Is nullable.

most_recent_sql_handle

varbinary(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.

Permissions

Requires VIEW SERVER STATE permission on the server.

Physical Joins

Joins for sys.dm_exec_connections

Relationship Cardinalities

dm_exec_sessions.session_id

dm_exec_connections.session_id

One-to-one

dm_exec_requests.connection_id

dm_exec_connections.connection_id

Many to one

dm_broker_connections.connection_id

dm_exec_connections.connection_id

One to one

Examples

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;

See Also

Reference

Dynamic Management Views and Functions (Transact-SQL)

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

Change History

Updated content

Added the note to the net_transport column about the MARS behavior.