----------------------------------------------------------------------------------------------------------------------------------------
select br.request_id, br.status AS ReqStatus, bs.status AS SessStatus, bs.session_id, br.wait_type,  br.wait_time, br.wait_resource, br.command,
br.cpu_time, br.total_elapsed_time, br.reads, br.writes, br.logical_reads, br.granted_query_memory, bs.memory_usage, bc.last_read, bc.last_write,
bc.connect_time, bs.Login_Name, bqp.query_plan, 
  br.session_id, br.blocking_session_id, br.percent_complete, estimated_finish_time = DATEADD(MILLISECOND, br.estimated_completion_time, CURRENT_TIMESTAMP),
db_name(br.database_id) as DatabaseName,  bs.program_name AS ProgramName, 
  btx.[text] AS SQLText, 
      (SELECT TOP 1 SUBSTRING(btx.text,statement_start_offset / 2+1 , 
      ( (CASE WHEN statement_end_offset = -1 
         THEN (LEN(CONVERT(nvarchar(max),btx.text)) * 2) 
         ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS SQLStatement, bs.[host_name]
  , cp.size_in_bytes, bs.nt_user_name, bs.nt_domain
 FROM sys.dm_exec_sessions AS bs 
      INNER JOIN [master].[sys].[dm_exec_requests] br on bs.session_id = br.session_id
	  INNER JOIN [master].[sys].[dm_exec_connections] bc on bc.connection_id = br.connection_id 
 CROSS APPLY sys.dm_exec_sql_text(br.[sql_handle]) AS btx
CROSS APPLY master.sys.dm_exec_query_plan(br.plan_handle) AS bqp
inner join master.sys.dm_exec_cached_plans cp on cp.plan_handle = br.plan_handle
 WHERE bs.session_id <>  @@spid;
 GO
------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT er.request_id, er.status AS ReqStatus, es.status AS SessStatus, es.session_id, er.wait_type,  er.wait_time, er.wait_resource, 
er.command, er.cpu_time AS ReqCPUTime, es.cpu_time AS SessCPUTime, er.total_elapsed_time, er.reads, er.writes, er.logical_reads,
 er.granted_query_memory, es.memory_usage, ec.last_read, ec.last_write, ec.connect_time, es.Login_Name, eqp.query_plan, 
  er.session_id, er.blocking_session_id, er.percent_complete, estimated_finish_time = DATEADD(MILLISECOND, er.estimated_completion_time, CURRENT_TIMESTAMP),
db_name(er.database_id) as DatabaseName,  es.program_name AS ProgramName, 
  st.[text] AS SQLText, 
      (SELECT TOP 1 SUBSTRING(st.text,statement_start_offset / 2+1 , 
      ( (CASE WHEN statement_end_offset = -1 
         THEN (LEN(CONVERT(nvarchar(max),st.text)) * 2) 
         ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS SQLStatement,
  es.[host_name], es.program_name, es.nt_user_name, es.login_time
    ,es.total_scheduled_time, es.total_elapsed_time, es.logical_reads, es.reads, es.writes, st.text, eqp.query_plan
	, cp.size_in_bytes, es.nt_user_name, es.nt_domain
FROM sys.dm_exec_sessions es
    LEFT JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
    LEFT JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
    OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) st
    OUTER APPLY master.sys.dm_exec_query_plan(er.plan_handle) AS eqp
	inner join master.sys.dm_exec_cached_plans cp on cp.plan_handle = er.plan_handle
WHERE es.session_id > 50 AND es.session_id <>  @@spid 
 AND es.status <> 'sleeping' AND es.status <> 'dormant'  -- < 50 system sessions
--ORDER BY 1, es.cpu_time DESC
;


----------------------------------------Alteratively with outer apply------------------------------------------------------
SELECT es.session_id, er.status, es.status, es.program_name, es.login_name, es.nt_user_name, es.login_time, es.host_name, es.cpu_time
    ,es.total_scheduled_time, es.total_elapsed_time, es.memory_usage, es.logical_reads, es.reads, es.writes, st.text, eqp.query_plan
FROM sys.dm_exec_sessions es
    LEFT JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
    LEFT JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
    OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) st
    OUTER APPLY master.sys.dm_exec_query_plan(er.plan_handle) AS eqp
WHERE es.session_id > 50 AND es.session_id <>  @@spid 
AND es.status <> 'sleeping' AND es.status <> 'dormant'  -- < 50 system sessions
ORDER BY 1, es.cpu_time DESC
GO
--select @@spid 

-------------------------------------------------------------------------------------------------------------
SELECT 
    t1.session_id, t1.wait_type, t1.wait_time,
    CONVERT(varchar(10), t1.status) AS status,
    CONVERT(varchar(15), t1.command) AS command,
    CONVERT(varchar(10), t2.state) AS worker_state,
    w_suspended = 
      CASE t2.wait_started_ms_ticks
        WHEN 0 THEN 0
        ELSE 
          t3.ms_ticks - t2.wait_started_ms_ticks
      END,
    w_runnable = 
      CASE t2.wait_resumed_ms_ticks
        WHEN 0 THEN 0
        ELSE 
          t3.ms_ticks - t2.wait_resumed_ms_ticks
      END
  FROM sys.dm_exec_requests AS t1
  INNER JOIN sys.dm_os_workers AS t2
    ON t2.task_address = t1.task_address
  CROSS JOIN sys.dm_os_sys_info AS t3
  WHERE t1.scheduler_id IS NOT NULL AND t1.session_id > 50 AND t1.session_id <>  @@spid
  ORDER BY t1.session_id;


EXEC sp_who2;