We can use the below script to find the current sessions details which are connect to SQL Server 2012
Basic Deatils about the sessions connected to SQL Server:
select
des.session_id,der.status,des.login_name,db_name(des.[database_id]) dbname,
des.login_time,der.command,(select text from master.sys.dm_exec_sql_text(der.sql_handle)) sql_text,
der.percent_complete,der.wait_resource,der.wait_type,der.wait_time/1000 wait_time_sec,
des.host_name,des.program_name,des.client_interface_name,
des.cpu_time,des.memory_usage*8 memory_usage_KB,
des.reads,des.writes,des.row_count,
des.original_login_name,des.last_request_start_time,des.last_request_end_time,des.total_elapsed_time,des.total_scheduled_time
from
master.sys.dm_exec_sessions as des left join master.sys.dm_exec_requests der
on des.session_id=der.session_id
where is_user_process=1
and des.session_id<>@@spid
Detailed Info about the sessions connected to SQL Server:
select
des.session_id,task_state,der.status,des.login_name,db_name(des.[database_id]) dbname,
dos.scheduler_id,dos.cpu_id,dos.current_tasks_count,dos.current_workers_count,dos.runnable_tasks_count,
des.login_time,der.command,(select text from master.sys.dm_exec_sql_text(der.sql_handle)) sql_text,
der.percent_complete,der.wait_resource,der.wait_type,der.wait_time/1000 wait_time_sec,
des.host_name,des.program_name,des.client_interface_name,
des.cpu_time,des.memory_usage*8 memory_usage_KB,
des.reads,des.writes,des.row_count,
des.original_login_name,des.last_request_start_time,des.last_request_end_time,des.total_elapsed_time,des.total_scheduled_time
from
master.sys.dm_exec_sessions as des left join master.sys.dm_exec_requests der
on des.session_id=der.session_id
left join master.sys.dm_os_tasks dot
on des.session_id=dot.session_id
left join master.sys.dm_os_workers dow
on dot.task_address=dow.task_address
left join master.sys.dm_os_schedulers dos
on dow.scheduler_address=dos.scheduler_address
where is_user_process=1
and des.session_id<>@@spid
and des.session_id<>@@spid
order by task_state desc
