Find Current Active Sessions with info

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 AS ReqCPUTime, bs.cpu_time AS SessCPUTime, 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 , bs.login_time, bs.total_scheduled_time, bs.total_elapsed_time, bs.logical_reads, bs.reads, bs.writes 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.nt_user_name, es.nt_domain , es.login_time, es.total_scheduled_time, es.total_elapsed_time, es.logical_reads, es.reads, es.writes --, cp.size_in_bytes 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 ;