sys.dm_exec_sessions (Transact-SQL)sys.dm_exec_sessions (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

对于 SQL ServerSQL Server 中每个经过身份验证的会话都返回一行。Returns one row per authenticated session on SQL ServerSQL Server. sys.dm_exec_sessions 是服务器范围的视图,显示了有关所有活动用户连接和内部任务的信息。sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. 此信息包含客户端版本、客户端程序名称、客户端登录时间、登录用户、当前会话设置等。This information includes client version, client program name, client login time, login user, current session setting, and more. 使用 sys.dm_exec_sessions,首先可以查看当前的系统负荷并标识相关会话,然后可以通过其他动态管理视图或动态管理函数了解有关该会话的详细信息。Use sys.dm_exec_sessions to first view the current system load and to identify a session of interest, and then learn more information about that session by using other dynamic management views or dynamic management functions.

Sys. dm_exec_connections、dm_exec_sessions 和 sys.databases dm_exec_requests 动态管理视图映射到sysprocesses系统表。The sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests dynamic management views map to the sys.sysprocesses system table.

注意: 若要从 Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data Warehouse中调用此名称,请使用名称sys.databases. dm_pdw_nodes_exec_sessionsNOTE: To call this from Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) or 并行数据仓库Parallel Data Warehouse, use the name sys.dm_pdw_nodes_exec_sessions.

列名Column name 数据类型Data type 说明和特定于版本的信息Description and version-specific information
session_idsession_id intsmallint 标识与每个活动主连接关联的会话。Identifies the session associated with each active primary connection. 不可为 null。Is not nullable.
login_timelogin_time datetimedatetime 建立会话的时间。Time when session was established. 不可为 null。Is not nullable.
host_namehost_name nvarchar(128)nvarchar(128) 特定于会话的客户端工作站名称。Name of the client workstation that is specific to a session. 对于内部会话,该值为 NULL。The value is NULL for internal sessions. 可以为 Null。Is nullable.

安全说明: 客户端应用程序提供了工作站名称并可以提供不准确的数据。Security Note: The client application provides the workstation name and can provide inaccurate data. 不要将 HOST_NAME 作为安全功能使用。Do not rely upon HOST_NAME as a security feature.
program_nameprogram_name nvarchar(128)nvarchar(128) 启动会话的客户端程序的名称。Name of client program that initiated the session. 对于内部会话,该值为 NULL。The value is NULL for internal sessions. 可以为 Null。Is nullable.
host_process_idhost_process_id intint 启动会话的客户端程序的进程 ID。Process ID of the client program that initiated the session. 对于内部会话,该值为 NULL。The value is NULL for internal sessions. 可以为 Null。Is nullable.
client_versionclient_version intint 客户端连接到服务器所用接口的 TDS 协议版本。TDS protocol version of the interface that is used by the client to connect to the server. 对于内部会话,该值为 NULL。The value is NULL for internal sessions. 可以为 Null。Is nullable.
client_interface_nameclient_interface_name nvarchar(32)nvarchar(32) 客户端用于与服务器通信的库/驱动程序的名称。Name of library/driver being used by the client to communicate with the server. 对于内部会话,该值为 NULL。The value is NULL for internal sessions. 可以为 Null。Is nullable.
security_idsecurity_id varbinary(85)varbinary(85) 与登录名关联的 Microsoft Windows 安全 ID。Microsoft Windows security ID associated with the login. 不可为 null。Is not nullable.
login_namelogin_name nvarchar(128)nvarchar(128) 当前执行的会话所使用的 SQL ServerSQL Server 登录名。SQL ServerSQL Server login name under which the session is currently executing. 有关创建此会话的原始登录名,请参阅 original_login_name。For the original login name that created the session, see original_login_name. 可以是 SQL ServerSQL Server 经过身份验证的登录名或 Windows 身份验证的域用户名。Can be a SQL ServerSQL Server authenticated login name or a Windows authenticated domain user name. 不可为 null。Is not nullable.
nt_domainnt_domain nvarchar(128)nvarchar(128) 适用于SQL Server 2008SQL Server 2008 及更高版本。Applies to: SQL Server 2008SQL Server 2008 and later.

客户端的 Windows 域(如果使用 Windows 身份验证或可信连接进行会话)。Windows domain for the client if the session is using Windows Authentication or a trusted connection. 对于内部会话和非域用户,该值为 NULL。This value is NULL for internal sessions and non-domain users. 可以为 Null。Is nullable.
nt_user_nament_user_name nvarchar(128)nvarchar(128) 适用于SQL Server 2008SQL Server 2008 及更高版本。Applies to: SQL Server 2008SQL Server 2008 and later.

客户端的 Windows 用户名(如果使用 Windows 身份验证或可信连接进行会话)。Windows user name for the client if the session is using Windows Authentication or a trusted connection. 对于内部会话和非域用户,该值为 NULL。This value is NULL for internal sessions and non-domain users. 可以为 Null。Is nullable.
statusstatus nvarchar(30)nvarchar(30) 会话的状态。Status of the session. 可能的值:Possible values:

正在运行-当前正在运行一个或多个请求Running - Currently running one or more requests

正在睡眠-当前没有运行任何请求Sleeping - Currently running no requests

休眠-由于连接池而重置会话,并且该会话目前处于预登录状态。Dormant - Session has been reset because of connection pooling and is now in prelogin state.

Preconnect在 Resource Governor 分类器中。Preconnect - Session is in the Resource Governor classifier.

不可为 null。Is not nullable.
context_infocontext_info varbinary(128)varbinary(128) 会话的 CONTEXT_INFO 值。CONTEXT_INFO value for the session. 上下文信息由用户通过使用set CONTEXT_INFO语句设置。The context information is set by the user by using the SET CONTEXT_INFO statement. 可以为 Null。Is nullable.
cpu_timecpu_time intint 该会话所占用的 CPU 时间(毫秒)。CPU time, in milliseconds, that was used by this session. 不可为 null。Is not nullable.
memory_usagememory_usage intint 该会话所占用的 8 KB 内存页数。Number of 8-KB pages of memory used by this session. 不可为 null。Is not nullable.
total_scheduled_timetotal_scheduled_time intint 计划内含请求的会话的执行所耗用的总计时间(毫秒)。Total time, in milliseconds, for which the session (requests within) were scheduled for execution. 不可为 null。Is not nullable.
total_elapsed_timetotal_elapsed_time intint 自会话建立以来已耗用的时间(毫秒)。Time, in milliseconds, since the session was established. 不可为 null。Is not nullable.
endpoint_idendpoint_id intint 与会话关联的端点的 ID。ID of the Endpoint associated with the session. 不可为 null。Is not nullable.
last_request_start_timelast_request_start_time datetimedatetime 最近一次会话请求的开始时间。Time at which the last request on the session began. 这包括当前正在执行的请求。This includes the currently executing request. 不可为 null。Is not nullable.
last_request_end_timelast_request_end_time datetimedatetime 最近一次会话请求的完成时间。Time of the last completion of a request on the session. 可以为 Null。Is nullable.
readsreads bigintbigint 在该会话期间该会话中的请求所执行的读取次数。Number of reads performed, by requests in this session, during this session. 不可为 null。Is not nullable.
Writeswrites bigintbigint 在该会话期间该会话中的请求所执行的写入次数。Number of writes performed, by requests in this session, during this session. 不可为 null。Is not nullable.
logical_readslogical_reads bigintbigint 已对该会话执行的逻辑读取数。Number of logical reads that have been performed on the session. 不可为 null。Is not nullable.
is_user_processis_user_process bitbit 如果会话是系统会话,则为 0。0 if the session is a system session. 否则为 1。Otherwise, it is 1. 不可为 null。Is not nullable.
text_sizetext_size intint 会话的 TEXTSIZE 设置。TEXTSIZE setting for the session. 不可为 null。Is not nullable.
languagelanguage nvarchar(128)nvarchar(128) 会话的 LANGUAGE 设置。LANGUAGE setting for the session. 可以为 Null。Is nullable.
date_formatdate_format nvarchar(3)nvarchar(3) 会话的 DATEFORMAT 设置。DATEFORMAT setting for the session. 可以为 Null。Is nullable.
date_firstdate_first intsmallint 会话的 DATEFIRST 设置。DATEFIRST setting for the session. 不可为 null。Is not nullable.
quoted_identifierquoted_identifier bitbit 会话的 QUOTED_IDENTIFIER 设置。QUOTED_IDENTIFIER setting for the session. 不可为 null。Is not nullable.
arithabortarithabort bitbit 会话的 ARITHABORT 设置。ARITHABORT setting for the session. 不可为 null。Is not nullable.
ansi_null_dflt_onansi_null_dflt_on bitbit 会话的 ANSI_NULL_DFLT_ON 设置。ANSI_NULL_DFLT_ON setting for the session. 不可为 null。Is not nullable.
ansi_defaultsansi_defaults bitbit 会话的 ANSI_DEFAULTS 设置。ANSI_DEFAULTS setting for the session. 不可为 null。Is not nullable.
ansi_warningsansi_warnings bitbit 会话的 ANSI_WARNINGS 设置。ANSI_WARNINGS setting for the session. 不可为 null。Is not nullable.
ansi_paddingansi_padding bitbit 会话的 ANSI_PADDING 设置。ANSI_PADDING setting for the session. 不可为 null。Is not nullable.
ansi_nullsansi_nulls bitbit 会话的 ANSI_NULLS 设置。ANSI_NULLS setting for the session. 不可为 null。Is not nullable.
concat_null_yields_nullconcat_null_yields_null bitbit 会话的 CONCAT_NULL_YIELDS_NULL 设置。CONCAT_NULL_YIELDS_NULL setting for the session. 不可为 null。Is not nullable.
transaction_isolation_leveltransaction_isolation_level intsmallint 会话的事务隔离级别。Transaction isolation level of the session.

0 = 未指定0 = Unspecified

1 = 未提交读取1 = ReadUncomitted

2 = 已提交读取2 = ReadCommitted

3 = 可重复3 = Repeatable

4 = 可序列化4 = Serializable

5 = 快照5 = Snapshot

不可为 null。Is not nullable.
lock_timeoutlock_timeout intint 会话的 LOCK_TIMEOUT 设置。LOCK_TIMEOUT setting for the session. 该值以毫秒计。The value is in milliseconds. 不可为 null。Is not nullable.
deadlock_prioritydeadlock_priority intint 会话的 DEADLOCK_PRIORITY 设置。DEADLOCK_PRIORITY setting for the session. 不可为 null。Is not nullable.
row_countrow_count bigintbigint 到目前为止会话返回的行数。Number of rows returned on the session up to this point. 不可为 null。Is not nullable.
prev_errorprev_error intint 会话返回的最近一个错误的 ID。ID of the last error returned on the session. 不可为 null。Is not nullable.
original_security_idoriginal_security_id varbinary(85)varbinary(85) 与 original_login_name 关联的 MicrosoftMicrosoft Windows 安全 ID。MicrosoftMicrosoft Windows security ID that is associated with the original_login_name. 不可为 null。Is not nullable.
original_login_nameoriginal_login_name nvarchar(128)nvarchar(128) 客户端用于创建此会话的 SQL ServerSQL Server 登录名。SQL ServerSQL Server login name that the client used to create this session. 可以是经过 SQL ServerSQL Server 身份验证的登录名、经过 Windows 身份验证的域用户名,也可以是包含数据库用户。Can be a SQL ServerSQL Server authenticated login name, a Windows authenticated domain user name, or a contained database user. 请注意,此会话在初次连接后可能已进行多次隐式或显式上下文切换。Note that the session could have gone through many implicit or explicit context switches after the initial connection. 例如,如果使用EXECUTE ASFor example, if EXECUTE AS is used. 不可为 null。Is not nullable.
last_successful_logonlast_successful_logon datetimedatetime 适用于SQL Server 2008SQL Server 2008 及更高版本。Applies to: SQL Server 2008SQL Server 2008 and later.

当前会话开始前 original_login_name 上一次成功登录的时间。Time of the last successful logon for the original_login_name before the current session started.
last_unsuccessful_logonlast_unsuccessful_logon datetimedatetime 适用于SQL Server 2008SQL Server 2008 及更高版本。Applies to: SQL Server 2008SQL Server 2008 and later.

当前会话开始前,original_login_name 上一次登录失败的时间。Time of the last unsuccessful logon attempt for the original_login_name before the current session started.
unsuccessful_logonsunsuccessful_logons bigintbigint 适用于SQL Server 2008SQL Server 2008 及更高版本。Applies to: SQL Server 2008SQL Server 2008 and later.

在 last_successful_logon 和 login_time 之间 original_login_name 的登录失败次数。Number of unsuccessful logon attempts for the original_login_name between the last_successful_logon and login_time.
group_idgroup_id intint 此会话所属工作负荷组的 ID。ID of the workload group to which this session belongs. 不可为 null。Is not nullable.
database_iddatabase_id intsmallint 适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

每个会话的当前数据库的 ID。ID of the current database for each session.
authenticating_database_idauthenticating_database_id intint 适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

对主体进行身份验证的数据库的 ID。ID of the database authenticating the principal. 对于登录名,该值将为 0。For Logins, the value will be 0. 对于包含数据库用户,该值将为包含数据库的数据库 ID。For contained database users, the value will be the database ID of the contained database.
open_transaction_countopen_transaction_count intint 适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

每个会话的打开事务数。Number of open transactions per session.
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.
page_server_readspage_server_reads bigintbigint 适用于: Azure SQL 数据库超大规模Applies to: Azure SQL Database Hyperscale

在此会话期间,此会话中的请求所执行的页服务器读取次数。Number of page server reads performed, by requests in this session, during this session. 不可为 null。Is not nullable.

PermissionsPermissions

所有人都可以查看自己的会话信息。Everyone can see their own session information.
SQL ServerSQL Server 需要 SQL ServerSQL Server 上的 VIEW SERVER STATE 权限才能查看服务器上的所有会话。SQL ServerSQL Server: Requires VIEW SERVER STATE permission on SQL ServerSQL Server to see all sessions on the server.
SQL 数据库SQL Database 需要 VIEW DATABASE STATE 才能查看与当前数据库的所有连接。SQL 数据库SQL Database: Requires VIEW DATABASE STATE to see all connections to the current database. 无法在 master 数据库中授予 VIEW DATABASE STATEVIEW DATABASE STATE cannot be granted in the master database.

RemarksRemarks

启用 "符合标准符合性" 的服务器配置选项时,将在以下各列中显示登录统计信息。When the common criteria compliance enabled server configuration option is enabled, logon statistics are displayed in the following columns.

  • last_successful_logonlast_successful_logon

  • last_unsuccessful_logonlast_unsuccessful_logon

  • unsuccessful_logonsunsuccessful_logons

如果未启用此选项,则这些列将返回 Null 值。If this option is not enabled, these columns will return null values. 有关如何设置此服务器配置选项的详细信息,请参阅 "已启用通用标准符合性" 服务器配置选项For more information about how to set this server configuration option, see common criteria compliance enabled Server Configuration Option.

Azure SQL 数据库上的管理员连接将在每个经过身份验证的会话中显示一行。The admin connections on Azure SQL Database will see one row per authenticated session. 在结果集中出现的 "sa" 会话不会对会话的用户配额产生任何影响。The "sa" sessions that appear in the resultset, do not have any impact on the user quota for sessions. 非管理员连接将只能看到与它们的数据库用户会话相关的信息。The non-admin connections will only see information related to their database user sessions.

关系基数Relationship Cardinalities

From 若要To 对于/应用On/Apply “关系”Relationship
sys.dm_exec_sessionssys.dm_exec_sessions sys.dm_exec_requestssys.dm_exec_requests session_idsession_id 一对零或一对多One-to-zero or one-to-many
sys.dm_exec_sessionssys.dm_exec_sessions sys.dm_exec_connectionssys.dm_exec_connections session_idsession_id 一对零或一对多One-to-zero or one-to-many
sys.dm_exec_sessionssys.dm_exec_sessions sys.dm_tran_session_transactionssys.dm_tran_session_transactions session_idsession_id 一对零或一对多One-to-zero or one-to-many
sys.dm_exec_sessionssys.dm_exec_sessions sys.dm_exec_cursors(session_id | 0)sys.dm_exec_cursors(session_id | 0) session_id CROSS APPLYsession_id CROSS APPLY

OUTER APPLYOUTER APPLY
一对零或一对多One-to-zero or one-to-many
sys.dm_exec_sessionssys.dm_exec_sessions sys.dm_db_session_space_usagesys.dm_db_session_space_usage session_idsession_id 一对一One-to-one

示例Examples

A.A. 查找连接到服务器的用户Finding users that are connected to the server

下例将查找连接到服务器的用户并返回每个用户的会话数。The following example finds the users that are connected to the server and returns the number of sessions for each user.

SELECT login_name ,COUNT(session_id) AS session_count   
FROM sys.dm_exec_sessions   
GROUP BY login_name;  

B.B. 查找长时间运行的游标Finding long-running cursors

下例将查找打开时间超过指定时间段的游标、创建游标的用户以及游标所在的会话。The following example finds the cursors that have been open for more than a specific period of time, who created the cursors, and what session the cursors are on.

USE master;  
GO  
SELECT creation_time ,cursor_id   
    ,name ,c.session_id ,login_name   
FROM sys.dm_exec_cursors(0) AS c   
JOIN sys.dm_exec_sessions AS s   
   ON c.session_id = s.session_id   
WHERE DATEDIFF(mi, c.creation_time, GETDATE()) > 5;  

C.C. 查找具有已打开事务的空闲会话Finding idle sessions that have open transactions

下例将查找具有已打开事务的空闲会话。The following example finds sessions that have open transactions and are idle. 空闲会话是当前未运行请求的会话。An idle session is one that has no request currently running.

SELECT s.*   
FROM sys.dm_exec_sessions AS s  
WHERE EXISTS   
    (  
    SELECT *   
    FROM sys.dm_tran_session_transactions AS t  
    WHERE t.session_id = s.session_id  
    )  
    AND NOT EXISTS   
    (  
    SELECT *   
    FROM sys.dm_exec_requests AS r  
    WHERE r.session_id = s.session_id  
    );  

D.D. 查找查询自有连接的有关信息Finding information about a queries own connection

收集查询自有连接有关信息的典型查询。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) Dynamic Management Views and Functions (Transact-SQL)
与执行相关的动态管理视图和函数 (Transact-SQL)Execution Related Dynamic Management Views and Functions (Transact-SQL)