[DMV] 가장 오래 돌고 있는 세션의 요청 쿼리 정보 확인

select  top 10

        a.session_id, a.host_name, a.client_interface_name, a.login_name, a.status, a.last_request_start_time

,       b.start_time, b.status, b.command, db_name(c.dbid) as 'DBName', object_name(c.objectid, c.dbid) as 'OBJName', b.last_wait_type, b.wait_time

,       b.cpu_time, b.total_elapsed_time, b.reads, b.writes, b.logical_reads

,       substring(c.text, (b.statement_start_offset / 2) + 1, ((case b.statement_end_offset when -1 then datalength(c.text) else b.statement_end_offset end - b.statement_start_offset)/2) + 1) as 'StatementQuery'

,       d.query_plan

from               sys.dm_exec_sessions a

    inner join     sys.dm_exec_requests b on a.session_id = b.session_id

    cross apply   sys.dm_exec_sql_text(b.sql_handle) c

    cross apply   sys.dm_exec_text_query_plan(b.plan_handle, b.statement_start_offset, b.statement_end_offset) d

where a.session_id > 50 and a.last_request_start_time > a.last_request_end_time

order by a.last_request_start_time, a.session_id

--order by b.cpu_time desc

--order by b.total_elapsed_time desc

--order by b.reads + b.writes desc

--order by b.logical_reads desc