RMJCS Logo
 

sys.dm_exec_sessions

 
 

BOL: 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.

What's a session? I believe it's an open connection. It may or may not be doing any work at a given time. Client side  connection pooling complicates the picture because when a client app closes the connection it's really returned to the pool, not actually closed. What's the effect of connection pooling on sessions? This view provides a lot of info so it's worth deciding what you're interested in and pulling back only the necessary columns. The definition of some of the columns isn't very clear. For example, from testing it seems like cpu_time is cumulative, but row_count applies to the last batch. This needs further investigation.

 
     
 

Formatted query for sys.dm_exec_sessions

 
 
SELECT
   s.session_id,
   --c.connection_id,
   s.status,
   s.host_name,
   c.client_net_address,
   CASE WHEN s.login_name = s.original_login_name THEN s.login_name ELSE s.login_name + ' (' + s.original_login_name + ')' END as login_name,
   s.program_name,

   --s.security_id,
   --s.nt_domain,
   --s.nt_user_name,
   --s.original_security_id,
   --s.original_login_name,
   --c.auth_scheme,
   --c.encrypt_option,
   --c.connect_time,
   --s.login_time,
   --s.last_successful_logon, -- Requires 'common criteria compliance enabled' option via sp_configure.
   --s.last_unsuccessful_logon, -- Requires 'common criteria compliance enabled' option via sp_configure.
   --s.unsuccessful_logons, -- Requires 'common criteria compliance enabled' option via sp_configure.

   s.total_elapsed_time as total_elapsed_time_ms,
   s.cpu_time as cpu_time_ms,
   (s.total_elapsed_time - s.cpu_time) as total_wait_time_ms, -- need to confirm this???
   s.memory_usage as memory_usage_pages,
   --s.total_scheduled_time as total_scheduled_time_ms,
   s.reads,
   s.writes,
   s.logical_reads,
   c.num_reads as net_reads,
   c.num_writes as net_writes,
   s.row_count,
   s.prev_error,
   --s.last_request_start_time,
   s.last_request_end_time,

   --s.host_process_id,
   c.net_transport,
   c.net_packet_size,
   c.protocol_type,
   c.protocol_version
   --s.client_version as client_TDS_protocol_version,
   --s.client_interface_name,

   --s.text_size,
   --s.language,
   --s.date_format,
   --s.date_first,
   --s.quoted_identifier,
   --s.arithabort,
   --s.ansi_null_dflt_on,
   --s.ansi_defaults,
   --s.ansi_warnings,
   --s.ansi_padding,
   --s.ansi_nulls,
   --s.concat_null_yields_null,
   --CASE s.transaction_isolation_level
   --   WHEN 0 THEN 'Unspecified'
   --   WHEN 1 THEN 'ReadUncomitted'
   --   WHEN 2 THEN 'ReadCommitted'
   --   WHEN 3 THEN 'Repeatable'
   --   WHEN 4 THEN 'Serializable'
   --   WHEN 5 THEN 'Snapshot'
   --   ELSE CAST(s.transaction_isolation_level AS VARCHAR(32))
   --END as transaction_isolation_level_name,
   --s.lock_timeout,
   --s.deadlock_priority,

   --s.context_info,
   --s.endpoint_id,
   --s.is_user_process

FROM
   sys.dm_exec_sessions s
      FULL OUTER JOIN sys.dm_exec_connections c on c.session_id = s.session_id
WHERE
   s.session_id IS NULL
   OR s.session_id > 50
ORDER BY
   s.session_id
 
     
Copyright 2007-2015 RMJCS Ltd