sys.dm_exec_connections
Returns server-level information about the connections to SQL Server.
The sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests dynamic management views map to the sys.sysprocesses system table.
| 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.
| |
|
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) |
The SQL Server authentication scheme for a connection. The mechanism for Windows authentication (NTLM, KERBEROS, DIGEST, BASIC, NEGOTIATE) or SQL for SQL Server authentication. 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(40) (SQL Server 2005) varchar(48) (SQL Server 2005 Service Pack 1) |
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(40) (SQL Server 2005) varchar(48) (SQL Server 2005 Service Pack 1) |
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. |
Note: | From | To | On | Relationship |
|---|---|---|---|
|
sys.dm_exec_connections |
sys.dm_exec_connections |
parent_connection_id = connection_id |
One-to-zero or one-to-many |
|
sys.dm_exec_sessions |
sys.dm_exec_connections |
session_id |
One-to-zero or one-to-many |
|
sys.dm_exec_connections |
sys.dm_exec_requests |
connection_id |
One-to-zero or one-to-one |
|
sys.dm_exec_connections |
sys.dm_broker_connections |
connection_id |
One-to-zero or one-to-one |
A. Returning information about the current connection
The following example returns basic information about the current connection: network transport protocol, authentication mechanism, and encryption setting.
SELECT net_transport, auth_scheme, encrypt_option FROM sys.dm_exec_connections WHERE session_id=@@SPID; GO
B. Identifying clients that are connected to server
The following example returns the number of connections to the server and displays the originating IP address and port of each connection.
SELECT session_id, client_net_address, client_tcp_port FROM sys.dm_exec_connections; GO
| Release | History |
|---|---|
|
12 December 2006 |
|
|
14 April 2006 |
|
