sys.dm_exec_connections (Transact-SQL)sys.dm_exec_connections (Transact-SQL)

適用於： 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

傳回有關與這個 SQL ServerSQL Server 執行個體建立之連接及每一個連接之詳細資料的資訊。Returns information about the connections established to this instance of SQL ServerSQL Server and the details of each connection. 傳回 SQL Server 的 server 寬的連接資訊。Returns server wide connection information for SQL Server. 傳回目前 SQL database 的資料庫連接資訊。Returns current database connection information for SQL Database.

注意

若要呼叫這個屬性從Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)或是平行處理資料倉儲Parallel Data Warehouse，使用sys.dm_pdw_exec_connections (-)。To call this from Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) or 平行處理資料倉儲Parallel Data Warehouse, use sys.dm_pdw_exec_connections (Transact-SQL).

資料行名稱Column name 資料類型Data type 描述Description
session_idsession_id intint 識別這項連接的相關工作階段。Identifies the session associated with this connection. 可為 Null。Is nullable.
most_recent_session_idmost_recent_session_id intint 代表這項連接最近的相關要求的工作階段識別碼Represents the session ID for the most recent request associated with this connection. （SOAP 連線可以重複使用另一個工作階段。）可為 Null。(SOAP connections can be reused by another session.) Is nullable.
connect_timeconnect_time datetimedatetime 建立連接的時間戳記。Timestamp when connection was established. 不可為 Null。Is not nullable.
net_transportnet_transport nvarchar(40)nvarchar(40) 一律會傳回工作階段當連接已啟用 multiple active result set (MARS)。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. 不可為 Null。Is not nullable.
protocol_typeprotocol_type nvarchar(40)nvarchar(40) 指定裝載的通訊協定類型。Specifies the protocol type of the payload. 它目前會區分 TDS (TSQL) 和 SOAP。It currently distinguishes between TDS (TSQL) and SOAP. 可為 Null。Is nullable.
protocol_versionprotocol_version intint 這項連接的相關資料存取通訊協定的版本。Version of the data access protocol associated with this connection. 可為 Null。Is nullable.
endpoint_idendpoint_id intint 描述其為何種連接類型的識別碼。An identifier that describes what type of connection it is. 這個 endpoint_id 可用來查詢 sys.endpoints 檢視。This endpoint_id can be used to query the sys.endpoints view. 可為 Null。Is nullable.
encrypt_optionencrypt_option nvarchar(40)nvarchar(40) 描述這項連接是否啟用加密的布林值。Boolean value to describe whether encryption is enabled for this connection. 不可為 Null。Is not nullable.
auth_schemeauth_scheme nvarchar(40)nvarchar(40) 指定搭配這個連接使用的 SQL ServerSQL Server/Windows 驗證配置。Specifies SQL ServerSQL Server/Windows Authentication scheme used with this connection. 不可為 Null。Is not nullable.
node_affinitynode_affinity smallintsmallint 識別此連接具有相似性的記憶體節點。Identifies the memory node to which this connection has affinity. 不可為 Null。Is not nullable.
num_readsnum_reads intint 這項連接期間發生的位元組讀取數。Number of byte reads that have occurred over this connection. 可為 Null。Is nullable.
num_writesnum_writes intint 這項連接期間發生的位元組寫入數目。Number of byte writes that have occurred over this connection. 可為 Null。Is nullable.
last_readlast_read datetimedatetime 這項連接期間最後一次讀取的時間戳記。Timestamp when last read occurred over this connection. 可為 Null。Is nullable.
last_writelast_write datetimedatetime 這項連接期間最後一次寫入的時間戳記。Timestamp when last write occurred over this connection. 不可設為 Null。Not Is nullable.
net_packet_sizenet_packet_size intint 用來傳送資訊和資料的網路封包大小。Network packet size used for information and data transfer. 可為 Null。Is nullable.
client_net_addressclient_net_address varchar(48)varchar(48) 連接到這部伺服器之用戶端的主機位址。Host address of the client connecting to this server. 可為 Null。Is nullable.

在 V12 之前的 Azure SQL DatabaseAzure SQL Database 中，此資料行一律會傳回 NULL。Prior to V12 in Azure SQL DatabaseAzure SQL Database, this column always returns NULL.
client_tcp_portclient_tcp_port intint 與這項連接相關聯的用戶端電腦上的通訊埠編號。Port number on the client computer that is associated with this connection. 可為 Null。Is nullable.

在 Azure SQL DatabaseAzure SQL Database，這個資料行一律會傳回 NULL。In Azure SQL DatabaseAzure SQL Database, this column always returns NULL.
local_net_addresslocal_net_address varchar(48)varchar(48) 代表這項連接的目標伺服器的 IP 位址。Represents the IP address on the server that this connection targeted. 只適用於使用 TCP 傳輸提供者的連接。Available only for connections using the TCP transport provider. 可為 Null。Is nullable.

在 Azure SQL DatabaseAzure SQL Database，這個資料行一律會傳回 NULL。In Azure SQL DatabaseAzure SQL Database, this column always returns NULL.
local_tcp_portlocal_tcp_port intint 當這項連接是使用 TCP 傳輸的連接時，代表這項連接的目標伺服器 TCP 埠。Represents the server TCP port that this connection targeted if it were a connection using the TCP transport. 可為 Null。Is nullable.

在 Azure SQL DatabaseAzure SQL Database，這個資料行一律會傳回 NULL。In Azure SQL DatabaseAzure SQL Database, this column always returns NULL.
connection_idconnection_id uniqueidentifieruniqueidentifier 這用來唯一識別各項連接。Identifies each connection uniquely. 不可為 Null。Is not nullable.
parent_connection_idparent_connection_id uniqueidentifieruniqueidentifier 這用來識別 MARS 工作階段在使用的主要連接。Identifies the primary connection that the MARS session is using. 可為 Null。Is nullable.
most_recent_sql_handlemost_recent_sql_handle varbinary(64)varbinary(64) 這項連接所執行之前一項要求的 SQL 控制代碼。The SQL handle of the last request executed on this connection. most_recent_sql_handle 資料行一律與 most_recent_session_id 資料行同步。The most_recent_sql_handle column is always in sync with the most_recent_session_id column. 可為 Null。Is nullable.
pdw_node_idpdw_node_id intint 適用於: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)， 平行處理資料倉儲Parallel Data WarehouseApplies to: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW), 平行處理資料倉儲Parallel Data Warehouse

這個分佈是在節點的識別碼。The identifier for the node that this distribution is on.

PermissionsPermissions

在 SQL ServerSQL Server，需要VIEW SERVER STATE權限。On SQL ServerSQL Server, requires VIEW SERVER STATE permission.
在 SQL DatabaseSQL Database Premium 層需要VIEW DATABASE STATE資料庫的權限。On SQL DatabaseSQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database. 上SQL DatabaseSQL Database標準和基本層，則需要伺服器系統管理員該Azure Active Directory 管理員帳戶。On SQL DatabaseSQL Database Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account.

實體聯結Physical Joins

Sys.dm_exec_connections 的聯結Joins for sys.dm_exec_connections

關聯性基數Relationship Cardinalities
dm_exec_sessions.session_iddm_exec_sessions.session_id dm_exec_connections.session_iddm_exec_connections.session_id 一對一One-to-one
dm_exec_requests.connection_iddm_exec_requests.connection_id dm_exec_connections.connection_iddm_exec_connections.connection_id 多對一Many to one
dm_broker_connections.connection_iddm_broker_connections.connection_id dm_exec_connections.connection_iddm_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

執行相關的動態管理檢視和函式 (Transact-SQL)Execution Related Dynamic Management Views and Functions (Transact-SQL)