sys.dm_exec_sessions
Returns one row per authenticated session on SQL Server. 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. 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.
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 |
smallint |
Identifies the session associated with each active primary connection. Not NULLABLE. |
login_time |
datetime |
Time when session was established. Not NULLABLE. |
host_name |
nvarchar(128) |
Name of the client workstation that is specific to a session. The value is NULL for internal sessions. NULLABLE. |
program_name |
nvarchar(128) |
Name of client program that initiated the session. The value is NULL for internal sessions. NULLABLE. |
host_process_id |
int |
Process ID of the client program that initiated the session. The value is NULL for internal sessions. NULLABLE. |
client_version |
int |
TDS protocol version of the interface that is used by the client to connect to the server. The value is NULL for internal sessions. NULLABLE. |
client_interface_name |
nvarchar(32) |
Protocol name that is used by the client to connect to the server. The value is NULL for internal sessions. NULLABLE. |
security_id |
varbinary(85) |
Microsoft Windows security ID associated with the login. Not NULLABLE. |
login_name |
nvarchar(128) |
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. Not NULLABLE. |
nt_domain |
nvarchar(128) |
Windows domain for the client if the session is using Windows Authentication or a trusted connection. This value is NULL for internal sessions and nondomain users. NULLABLE. |
nt_user_name |
nvarchar(128) |
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. NULLABLE. |
status |
nvarchar(30) |
Status of the session. Possible values:
Not NULLABLE. |
context_info |
varbinary(128) |
CONTEXT_INFO value for the session. The context information is set by the user by using the SET CONTEXT_INFO statement. NULLABLE. |
cpu_time |
int |
CPU time, in milliseconds, that was used by this session. Not NULLABLE. |
memory_usage |
int |
Number of 8-KB pages of memory used by this session. Not NULLABLE. |
total_scheduled_time |
int |
Total time, in milliseconds, for which the session (requests within) were scheduled for execution. Not NULLABLE. |
total_elapsed_time |
int |
Time, in milliseconds, since the session was established. Not NULLABLE. |
endpoint_id |
int |
ID of the Endpoint associated with the session. Not NULLABLE. |
last_request_start_time |
datetime |
Time at which the last request on the session began. This includes the currently executing request. Not NULLABLE. |
last_request_end_time |
datetime |
Time of the last completion of a request on the session. NULLABLE. |
reads |
bigint |
Number of reads performed, by requests in this session, during this session. Not NULLABLE. |
writes |
bigint |
Number of writes performed, by requests in this session, during this session. Not NULLABLE. |
logical_reads |
bigint |
Number of logical reads that have been performed on the session. Not NULLABLE. |
is_user_process |
bit |
0 if the session is a system session. Otherwise, it is 1. Not NULLABLE. |
text_size |
int |
TEXTSIZE setting for the session. Not NULLABLE. |
language |
nvarchar(128) |
LANGUAGE setting for the session. NULLABLE. |
date_format |
nvarchar(3) |
DATEFORMAT setting for the session. NULLABLE. |
date_first |
smallint |
DATEFIRST setting for the session. Not NULLABLE. |
quoted_identifier |
bit |
QUOTED_IDENTIFIER setting for the session. Not NULLABLE. |
arithabort |
bit |
ARITHABORT setting for the session. Not NULLABLE. |
ansi_null_dflt_on |
bit |
ANSI_NULL_DFLT_ON setting for the session. Not NULLABLE. |
ansi_defaults |
bit |
ANSI_DEFAULTS setting for the session. Not NULLABLE. |
ansi_warnings |
bit |
ANSI_WARNINGS setting for the session. Not NULLABLE. |
ansi_padding |
bit |
ANSI_PADDING setting for the session. Not NULLABLE. |
ansi_nulls |
bit |
ANSI_NULLS setting for the session. Not NULLABLE. |
concat_null_yields_null |
bit |
CONCAT_NULL_YIELDS_NULL setting for the session. Not NULLABLE. |
transaction_isolation_level |
smallint |
Transaction isolation level of the session. 0 = Unspecified 1 = ReadUncomitted 2 = ReadCommitted 3 = Repeatable 4 = Serializable 5 = Snapshot Not NULLABLE. |
lock_timeout |
int |
LOCK_TIMEOUT setting for the session. The value is in milliseconds. Not NULLABLE. |
deadlock_priority |
int |
DEADLOCK_PRIORITY setting for the session. Not NULLABLE. |
row_count |
bigint |
Number of rows returned on the session up to this point. Not NULLABLE. |
prev_error |
int |
ID of the last error returned on the session. Not NULLABLE. |
original_security_id |
varbinary(85) |
Microsoft Windows security ID that is associated with the original_login_name. Not NULLABLE. |
original_login_name |
nvarchar(128) |
SQL Server login name that the client used to create this session. Can be a SQL Server authenticated login name or a Windows authenticated domain user name. Note that the session could have gone through many implicit or explicit context switches after the initial connection. For example, if EXECUTE AS is used. Not NULLABLE. |
last_successful_logon |
datetime |
Time of the last successful logon for the original_login_name before the current session started. |
last_unsuccessful_logon |
datetime |
Time of the last unsuccessful logon attempt for the original_login_name before the current session started. |
unsuccessful_logons |
bigint |
Number of unsuccessful logon attempts for the original_login_name between the last_successful_logon and login_time. |
Permissions
Requires VIEW SERVER STATE permission on server.
Note
If the user has VIEW SERVER STATE permission on the server, the user will see all executing sessions on the instance of SQL Server; otherwise, the user will see only the current session.
Remarks
Login Statistics for Common Criteria Compliance
When the common criteria compliance enabled option is enabled by using the stored procedure sp_configure, login statistics are stored and displayed in the following columns of sys.dm_exec_sessions:
- last_successful_logon
- last_unsuccessful_logon
- unsuccessful_logons
If this server configuration option is not enabled, these sys.dm_exec_sessions columns will return null values. For more information about setting this server configuration option, see common criteria compliance enabled Option.
Relationship Cardinalities
From | To | On/Apply | Relationship |
---|---|---|---|
sys.dm_exec_sessions |
sys.dm_exec_requests |
session_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_sessions |
sys.dm_tran_session_transactions |
session_id |
One-to-zero or one-to-many |
sys.dm_exec_sessions |
sys.dm_exec_cursors(session_id | 0) |
session_id CROSS APPLY OUTER APPLY |
One-to-zero or one-to-many |
sys.dm_exec_sessions |
sys.dm_db_session_space_usage |
session_id |
One-to-one |
Examples
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. 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. 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
);
See Also
Reference
Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views
Dynamic Management Views and Functions
Execution Related Dynamic Management Views and Functions
Other Resources
Help and Information
Getting SQL Server 2005 Assistance
Change History
Release | History |
---|---|
12 December 2006 |
|