Friday, 18 December 2009

Quick snapshot of current activity

This query provides a quick snapshot of current active and waiting sessions in SQL Server 2008.

select er.[session_id], txt.[text], pln.[query_plan]
  , sess.login_name, sess.host_name, as db_name
, sess.last_request_start_time, sess.last_request_end_time , er.[blocking_session_id], er.[last_wait_type], er.[wait_time]
  , er.[cpu_time], er.[reads], er.[writes], er.[logical_reads]
from sys.dm_exec_sessions sess
  inner join sys.dm_exec_requests er
  on sess.session_id = er.session_id
    left join sys.databases db
    on er.database_id = db.database_id
    outer apply sys.dm_exec_sql_text(er.[sql_handle]) txt
    outer apply sys.dm_exec_query_plan(er.[plan_handle]) pln
where sess.is_user_process = 1

No comments: