SQL Info

SQL Info

-- 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
Where c.client_net_address = '192.168.5.19'



-- sessions
SELECT login_name ,COUNT(session_id) AS session_count 
FROM sys.dm_exec_sessions 
GROUP BY login_name



-- requests 
select
er.session_id,
er.blocking_session_id,
er.status,
 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.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