Skip to content

Instantly share code, notes, and snippets.

Created July 14, 2013 13:07
Show Gist options
  • Save anonymous/5994213 to your computer and use it in GitHub Desktop.
Save anonymous/5994213 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment