/*
запросы,  СЕЙЧАС выполняющиеся. План - крайняя колонка слева.
*/
SELECT
	 p.query_plan
	, req.command command_type
	, sqltext.TEXT command_text
	, SUBSTRING(sqltext.text,  (req.statement_start_offset/2) + 1,
        ((CASE req.statement_end_offset
          WHEN -1 THEN DATALENGTH(sqltext.text)
         ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) AS statement_text
	, db_name(req.database_id) db_name
	, req.session_id
	, req.blocking_session_id
	, req.status as req_status
	, req.cpu_time req_cpu_time_ms
	, ses.cpu_time ses_cpu_time_ms
	, req.total_elapsed_time / 1000.0 AS req_elapsed_sec
	, ses.original_login_name
	, ses.host_name
	, ses.program_name
	, ses.host_process_id
	, ses.client_interface_name
	, ses.memory_usage
	, ses.reads
	, ses.writes
	, ses.logical_reads
FROM sys.dm_exec_requests AS req with(nolock)
   JOIN sys.dm_exec_sessions AS ses with(nolock) on req.session_id = ses.session_id
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 
   CROSS APPLY	sys.dm_exec_query_plan(req.plan_handle) AS p
WHERE 1 = 1
   and req.database_id > 4
   and req.session_id <> @@SPID
   -- AND req.session_id = 78                   -- задать SPID
   -- AND ses.[host_name] = 'SRV-01'            -- задать хост
ORDER BY req.total_elapsed_time DESC