There are 3 dmv
- sys.dm_exec_connections
- sys.dm_exec_sessions
- sys.dm_exec_requests
1-sys.dm_exec_connections
select c.session_id,
c.auth_scheme,
c.last_read,
c.last_write,
c.client_net_address,
c.local_tcp_port,
st.text as lastQuery
from sys.dm_exec_connections c
cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) st
2-sys.dm_exec_sessions
SELECT login_name ,COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name;
3-sys.dm_exec_requests
select DB_NAME(er.database_id) as DBName,
es.login_name,
es.host_name,
st.text,
SUBSTRING(st.text, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS statement_text,
er.blocking_session_id,
er.status,
er.wait_type,
er.wait_time,
er.percent_complete,
er.estimated_completion_time
from sys.dm_exec_requests er
left join sys.dm_exec_sessions es on es.session_id=er.session_id
cross apply sys.dm_exec_sql_text(er.sql_handle) st
cross apply sys.dm_exec_query_plan(er.plan_handle) qp
where er.session_id>50 and er.session_id!=@@SPID