BOL: Returns one row for each request executing within SQL Server.

In previous versions of SQL Server it was difficult to get information about currently executing queries - this view provides that information.

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')