- sys.dm_exec_sessions will give information about each session connected to SQL Server. This DMV is similar to running sp_who2 stored procedure in SQL Server.
- sys.dm_exec_sessions Returns one row per authenticated session on SQL Server. This DMV replaces almost all columns of sys.sysprocesses.
- sys.dm_exec_sessions is a server-scope view that shows information about all active user connections like includes login name,host name,client version, client program name, client login time, login user, current session setting, and more.
In this below Samaple SQL Server query I am including the most used columns from sys.dm_exec_sessions
SELECT session_id, login_time, host_name, program_name, host_process_id, client_interface_name, login_name, original_login_name, status, cpu_time, memory_usage, total_scheduled_time, total_elapsed_time, last_request_start_time, last_request_end_time, reads, writes, logical_reads, is_user_process, transaction_isolation_level FROM sys.dm_exec_sessions WHERE session_id > 50
Identify users that are connected to the server:-
The below query finds the users that are connected to the server and returns the number of sessions for each user.
SELECT host_name,login_name ,program_name,COUNT(session_id) AS session_count FROM sys.dm_exec_sessions GROUP BY login_name,host_name,PROGRAM_NAME;