Saturday, 22 September 2012

Session Deatils connected to SQL Server

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
order by der.status desc

 

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
order by task_state desc

 

No comments:

Post a Comment