sys.dm_exec_sessions (Transact-SQL)

 

Updated: August 10, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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.

NOTE: To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_exec_sessions.

Column nameData typeDescription and version-specific information
session_idsmallintIdentifies the session associated with each active primary connection. Is not nullable.
login_timedatetimeTime when session was established. Is not nullable.
host_namenvarchar(128)Name of the client workstation that is specific to a session. The value is NULL for internal sessions. Is nullable.

 Security Note: The client application provides the workstation name and can provide inaccurate data. Do not rely upon HOST_NAME as a security feature.
program_namenvarchar(128)Name of client program that initiated the session. The value is NULL for internal sessions. Is nullable.
host_process_idintProcess ID of the client program that initiated the session. The value is NULL for internal sessions. Is nullable.
client_versionintTDS protocol version of the interface that is used by the client to connect to the server. The value is NULL for internal sessions. Is nullable.
client_interface_namenvarchar(32)Protocol name that is used by the client to connect to the server. The value is NULL for internal sessions. Is nullable.
security_idvarbinary(85)Microsoft Windows security ID associated with the login. Is not nullable.
login_namenvarchar(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. Is not nullable.
nt_domainnvarchar(128)Applies to: SQL Server 2008 through SQL Server 2016.

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. Is nullable.
nt_user_namenvarchar(128)Applies to: SQL Server 2008 through SQL Server 2016.

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. Is nullable.
statusnvarchar(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 - Session is in the Resource Governor classifier.

Is not nullable.
context_infovarbinary(128)CONTEXT_INFO value for the session. The context information is set by the user by using the SET CONTEXT_INFO statement. Is nullable.
cpu_timeintCPU time, in milliseconds, that was used by this session. Is not nullable.
memory_usageintNumber of 8-KB pages of memory used by this session. Is not nullable.
total_scheduled_timeintTotal time, in milliseconds, for which the session (requests within) were scheduled for execution. Is not nullable.
total_elapsed_timeintTime, in milliseconds, since the session was established. Is not nullable.
endpoint_idintID of the Endpoint associated with the session. Is not nullable.
last_request_start_timedatetimeTime at which the last request on the session began. This includes the currently executing request. Is not nullable.
last_request_end_timedatetimeTime of the last completion of a request on the session. Is nullable.
readsbigintNumber of reads performed, by requests in this session, during this session. Is not nullable.
writesbigintNumber of writes performed, by requests in this session, during this session. Is not nullable.
logical_readsbigintNumber of logical reads that have been performed on the session. Is not nullable.
is_user_processbit0 if the session is a system session. Otherwise, it is 1. Is not nullable.
text_sizeintTEXTSIZE setting for the session. Is not nullable.
languagenvarchar(128)LANGUAGE setting for the session. Is nullable.
date_formatnvarchar(3)DATEFORMAT setting for the session. Is nullable.
date_firstsmallintDATEFIRST setting for the session. Is not nullable.
quoted_identifierbitQUOTED_IDENTIFIER setting for the session. Is not nullable.
arithabortbitARITHABORT setting for the session. Is not nullable.
ansi_null_dflt_onbitANSI_NULL_DFLT_ON setting for the session. Is not nullable.
ansi_defaultsbitANSI_DEFAULTS setting for the session. Is not nullable.
ansi_warningsbitANSI_WARNINGS setting for the session. Is not nullable.
ansi_paddingbitANSI_PADDING setting for the session. Is not nullable.
ansi_nullsbitANSI_NULLS setting for the session. Is not nullable.
concat_null_yields_nullbitCONCAT_NULL_YIELDS_NULL setting for the session. Is not nullable.
transaction_isolation_levelsmallintTransaction isolation level of the session.

0 = Unspecified

1 = ReadUncomitted

2 = ReadCommitted

3 = Repeatable

4 = Serializable

5 = Snapshot

Is not nullable.
lock_timeoutintLOCK_TIMEOUT setting for the session. The value is in milliseconds. Is not nullable.
deadlock_priorityintDEADLOCK_PRIORITY setting for the session. Is not nullable.
row_countbigintNumber of rows returned on the session up to this point. Is not nullable.
prev_errorintID of the last error returned on the session. Is not nullable.
original_security_idvarbinary(85)Microsoft Windows security ID that is associated with the original_login_name. Is not nullable.
original_login_namenvarchar(128)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. 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. Is not nullable.
last_successful_logondatetimeApplies to: SQL Server 2008 through SQL Server 2016.

Time of the last successful logon for the original_login_name before the current session started.
last_unsuccessful_logondatetimeApplies to: SQL Server 2008 through SQL Server 2016.

Time of the last unsuccessful logon attempt for the original_login_name before the current session started.
unsuccessful_logonsbigintApplies to: SQL Server 2008 through SQL Server 2016.

Number of unsuccessful logon attempts for the original_login_name between the last_successful_logon and login_time.
group_idintID of the workload group to which this session belongs. Is not nullable.
database_idsmallintApplies to: SQL Server 2012 through SQL Server 2016.

ID of the current database for each session.
authenticating_database_idintApplies to: SQL Server 2012 through SQL Server 2016.

ID of the database authenticating the principal. For Logins, the value will be 0. For contained database users, the value will be the database ID of the contained database.
open_transaction_countintApplies to: SQL Server 2012 through SQL Server 2016.

Number of open transactions per session.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

Requires VIEW SERVER STATE permission on server.

System_CAPS_ICON_note.jpg 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.

When the common criteria compliance enabled server configuration option is enabled, logon statistics are displayed in the following columns.

  • last_successful_logon

  • last_unsuccessful_logon

  • unsuccessful_logons

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.

FromToOn/ApplyRelationship
sys.dm_exec_sessionssys.dm_exec_requestssession_idOne-to-zero or one-to-many
sys.dm_exec_sessionssys.dm_exec_connectionssession_idOne-to-zero or one-to-many
sys.dm_exec_sessionssys.dm_tran_session_transactionssession_idOne-to-zero or one-to-many
sys.dm_exec_sessionssys.dm_exec_cursors(session_id | 0)session_id CROSS APPLY

OUTER APPLY
One-to-zero or one-to-many
sys.dm_exec_sessionssys.dm_db_session_space_usagesession_idOne-to-one

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  
    );  

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;  

Dynamic Management Views and Functions (Transact-SQL)
Execution Related Dynamic Management Views and Functions (Transact-SQL)

Community Additions

ADD
Show: