SELECT
r.session_id,
r.request_id as session_request_id,
--r.connection_id,
r.status,
s.host_name,
c.client_net_address,
CASE WHEN s.login_name = s.original_login_name THEN s.login_name ELSE s.login_name + ' (' + s.original_login_name + ')' END as login_name,
s.program_name,
db_name(r.database_id) as database_name,
r.command,
--r.sql_handle,
--r.statement_start_offset,
--r.statement_end_offset,
--r.plan_handle,
substring(st.text,r.statement_start_offset/2 ,(CASE WHEN r.statement_end_offset = -1 THEN len(convert(nvarchar(max), st.text)) * 2 ELSE r.statement_end_offset END - r.statement_start_offset)/2) as statement,
--st.text as query_text,
--qp.query_plan as xml_query_plan,
r.start_time,
r.total_elapsed_time as total_elapsed_time_ms,
r.cpu_time as cpu_time_ms,
r.wait_type as current_wait_type,
r.wait_resource as current_wait_resource,
r.wait_time as current_wait_time_ms,
r.last_wait_type,
r.blocking_session_id,
r.reads,
r.writes,
r.logical_reads,
r.row_count,
r.prev_error,
r.nest_level,
r.granted_query_memory,
r.executing_managed_code,
r.transaction_id,
r.open_transaction_count,
r.open_resultset_count,
r.scheduler_id
--r.user_id,
--r.percent_complete,
--r.estimated_completion_time, -- This is an internal, MS only.
--r.task_address,
--r.text_size,
--r.language,
--r.date_format,
--r.date_first,
--r.quoted_identifier,
--r.arithabort,
--r.ansi_null_dflt_on,
--r.ansi_defaults,
--r.ansi_warnings,
--r.ansi_padding,
--r.ansi_nulls,
--r.concat_null_yields_null,
--CASE r.transaction_isolation_level
-- WHEN 0 THEN 'Unspecified'
-- WHEN 1 THEN 'ReadUncomitted'
-- WHEN 2 THEN 'ReadCommitted'
-- WHEN 3 THEN 'Repeatable'
-- WHEN 4 THEN 'Serializable'
-- WHEN 5 THEN 'Snapshot'
-- ELSE CAST(r.transaction_isolation_level AS VARCHAR(32))
--END as transaction_isolation_level_name,
--r.lock_timeout,
--r.deadlock_priority,
--r.context_info,
FROM
sys.dm_exec_requests r
LEFT OUTER JOIN sys.dm_exec_sessions s on s.session_id = r.session_id
LEFT OUTER JOIN sys.dm_exec_connections c on c.connection_id = r.connection_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE
r.status NOT IN ('background','sleeping')