sys.dm_exec_sessions

Returns one row per authenticated session.

sys.dm_exec_sessions is a server-scope view that shows information about all active user connections. This information includes client version, client program name, client login time, login user, current session setting, and more. 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.

Column name	Data type	Nullable	Description
session_id	smallint	No	Identifies the session associated with each active primary connection.
login_time	datetime	No	Time when session was established. Sessions that haven’t completely logged in at the time this DMV is queried, are shown with a login time of 1900-01-01.
host_name	nvarchar(128)	Yes	Name of the client workstation that’s specific to a session. The value is NULL for internal sessions. Security note: The client application provides the workstation name and can provide inaccurate data. Don’t rely on HOST_NAME as a security feature.
program_name	nvarchar(128)	Yes	Name of client program that initiated the session. The value is NULL for internal sessions.
host_process_id	int	Yes	Process ID of the client program that initiated the session. The value is NULL for internal sessions.
client_version	int	Yes	TDS protocol version of the interface used by the client to connect to the server. The value is NULL for internal sessions.
client_interface_name	nvarchar(32)	Yes	Name of library/driver being used by the client to communicate with the server. The value is NULL for internal sessions.
security_id	varbinary(85)	No	Windows security ID associated with the login.
login_name	nvarchar(128)	No	SQL Server login name under which the session is currently executing. For the original login name that created the session, see original_login_name. Can be a SQL Server authenticated login name or a Windows authenticated domain user name.
nt_domain	nvarchar(128)	Yes	Windows domain for the client if the session is using Windows Authentication or a trusted connection. This value is NULL for internal sessions and non-domain users.
nt_user_name	nvarchar(128)	Yes	Windows user name for the client if the session is using Windows Authentication or a trusted connection. This value is NULL for internal sessions and non-domain users.
status	nvarchar(30)	No	Status of the session. Possible values: Running - Currently running one or more requests Sleeping - Currently running no requests Dormant - Session was reset because of connection pooling and is now in prelogin state.
context_info	varbinary(128)	Yes	CONTEXT_INFO value for the session. The context information is set by the user by using the SET CONTEXT_INFO statement.
cpu_time	int	No	CPU time, in milliseconds, used by this session.
memory_usage	int	No	Number of 8-KB pages of memory used by this session.
total_scheduled_time	int	No	Total time, in milliseconds, for which the session (requests within) were scheduled for execution.
total_elapsed_time	int	No	Time, in milliseconds, since the session was established.
endpoint_id	int	No	ID of the endpoint associated with the session.
last_request_start_time	datetime	No	Time at which the last request on the session began. This time includes the currently executing request.
last_request_end_time	datetime	Yes	Time of the last completion of a request on the session.
reads	bigint	No	Number of physical reads performed, by requests in this session, during this session.
writes	bigint	No	Number of physical writes performed, by requests in this session, during this session.
logical_reads	bigint	No	Number of logical reads performed, by requests in this session, during this session.
is_user_process	bit	No	0 if the session is a system session. Otherwise, it’s 1.
text_size	int	No	TEXTSIZE setting for the session.
language	nvarchar(128)	Yes	LANGUAGE setting for the session.
date_format	nvarchar(3)	Yes	DATEFORMAT setting for the session.
date_first	smallint	No	DATEFIRST setting for the session.
quoted_identifier	bit	No	QUOTED_IDENTIFIER setting for the session.
arithabort	bit	No	ARITHABORT setting for the session.
ansi_null_dflt_on	bit	No	ANSI_NULL_DFLT_ON setting for the session.
ansi_defaults	bit	No	ANSI_DEFAULTS setting for the session.
ansi_warnings	bit	No	ANSI_WARNINGS setting for the session.
ansi_padding	bit	No	ANSI_PADDING setting for the session.
ansi_nulls	bit	No	ANSI_NULLS setting for the session.
concat_null_yields_null	bit	No	CONCAT_NULL_YIELDS_NULL setting for the session.
transaction_isolation _level	smallint	No	Transaction isolation level of the session. 0 = Unspecified 1 = ReadUncommitted 2 = ReadCommitted 3 = RepeatableRead 4 = Serializable 5 = Snapshot
lock_timeout	int	No	LOCK_TIMEOUT setting for the session. The value is in milliseconds.
deadlock_priority	int	No	DEADLOCK_PRIORITY setting for the session.
row_count	bigint	No	Number of rows returned on the session up to this point.
prev_error	int	No	ID of the last error returned on the session.
original_security_id	varbinary(85)	No	Windows security ID that is associated with the original_login_name.
original_login_name	nvarchar(128)	No	SQL Server login name that the client used to create this session. Can be a SQL Server authenticated login name, a Windows authenticated domain user name, or a contained database user. The session might have been through many implicit or explicit context switches after the initial connection.
last_successful_logon	datetime	Yes	Time of the last successful logon for the original_login_name before the current session started.
last_unsuccessful_logon	datetime	Yes	Time of the last unsuccessful logon attempt for the original_login_name before the current session started.
unsuccessful_logons	bigint	Yes	Number of unsuccessful logon attempts for the original_login_name between the last_successful_logon and login_time.
group_id	int	No	ID of the workload group to which this session belongs.
database_id	smallint	No	ID of the current database for each session.
authenticating_database _id	int	Yes	ID of the database authenticating the principal. For logins, the value is 0. For contained database users, the value is the database ID of the contained database.
open_transaction_count	int	No	Number of open transactions per session.
pdw_node_id	int	No	The identifier for the node that this distribution is on.
page_server_reads	bigint	No	Number of page server reads performed, by requests in this session, during this session.
contained_availability _group_id	uniqueidentifier	Yes	ID of the contained availability group.

Permissions

Everyone can see their own session information.

Requires VIEW SERVER STATE to see all sessions on the server.

Relationship cardinalities

From	To	On/Apply	Relationship
sys.dm_exec_sessions	[sys.dm_exec_requests](sys-dm-exec-requests)	session_id	One-to-zero or one-to-many
sys.dm_exec_sessions	[sys.dm_exec_connections](sys-dm-exec-connections)	session_id	One-to-zero or one-to-many

Examples

A. Find 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. Find information about a query’s own connection

The following example gathers information about a query’s 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
 INNER sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
 WHERE c.session_id = @@SPID;

See also

• Dynamic Management Views