Question:

How do I find the statements that are executing in SQL Server?

Answer:

For such a task Microsoft has been kind enough to provide us with some pretty handy DMVs (Dynamic Management Views).
Below we will use the following DMVs and Table-valued functions:

1. sys.dm_exec_requests – Returns information about each request that is executing within SQL Server.
2. sys.dm_exec_sql_text – A table-valued function that returns the text of the SQL batch that is identified by the specified sql_handle. This table-valued function replaces the system function fn_get_sql. We can use this to get the text of the full batch as well as the specific currently executing statement within the batch.
3. sys.dm_exec_query_plan – A table-valued function that returns the Showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing.
4. sys.dm_exec_sessions – Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks.

Here is a query that I like to use to find the currently executing statements in SQL Server:

-----------------------------------------------------
select
  a.session_id
, a.blocking_session_id
, a.wait_type
, a.wait_resource
, a.wait_time
, a.start_time
, datediff(second, a.start_time, getdate()) as [duration_second]
, d.login_time
, d.last_request_start_time
, d.[status]
, d.login_name
, d.nt_domain
, d.nt_user_name
, d.[host_name]
, d.[program_name]

, DB_NAME(a.database_id) AS [database_name]
, OBJECT_SCHEMA_NAME(b.objectid, a.database_id) AS [schema_name]
, OBJECT_NAME(b.objectid, a.database_id) AS [object_name]

, b.[text] AS [FullSQLBatch]
, CASE a.statement_end_offset
			WHEN -1 THEN SUBSTRING(b.[text], (a.[statement_start_offset] / 2) + 1, 2147483647)
			WHEN 0 THEN b.[text]
			ELSE SUBSTRING(b.[text], (a.[statement_start_offset] / 2) + 1, (a.[statement_end_offset] - a.[statement_start_offset]) / 2)
			END AS [ActiveStatement]
, c.query_plan

, a.open_transaction_count
, a.open_resultset_count
, a.percent_complete
, a.reads
, a.writes
, a.logical_reads
, a.granted_query_memory

FROM sys.dm_exec_requests AS a
   OUTER APPLY sys.dm_exec_sql_text(a.[sql_handle]) AS b
   OUTER APPLY sys.dm_exec_query_plan(a.plan_handle) AS c
   LEFT JOIN sys.dm_exec_sessions AS d ON a.session_id = d.session_id
WHERE a.session_id >= 50
  and a.session_id <> @@spid
-----------------------------------------------------

Here is another iteration of the above statement. This one will also include information about the blocker (if there is one).

<pre>-----------------------------------------------------
select
  a.session_id
, a.blocking_session_id
, a.wait_type
, a.wait_resource
, a.wait_time
, a.start_time
, datediff(second, a.start_time, getdate()) as [duration_second]
, d.login_time
, d.last_request_start_time
, d.[status]
, d.login_name
, d.nt_domain
, d.nt_user_name
, d.[host_name]
, d.[program_name]

, DB_NAME(a.database_id) AS [database_name]
, OBJECT_SCHEMA_NAME(b.objectid, a.database_id) AS [schema_name]
, OBJECT_NAME(b.objectid, a.database_id) AS [object_name]

, b.[text] AS [FullSQLBatch]
, CASE a.statement_end_offset
			WHEN -1 THEN SUBSTRING(b.[text], (a.[statement_start_offset] / 2) + 1, 2147483647)
			WHEN 0 THEN b.[text]
			ELSE SUBSTRING(b.[text], (a.[statement_start_offset] / 2) + 1, (a.[statement_end_offset] - a.[statement_start_offset]) / 2)
			END AS [ActiveStatement]
, c.query_plan

, a.open_transaction_count
, a.open_resultset_count
, a.percent_complete
, a.reads
, a.writes
, a.logical_reads
, a.granted_query_memory

, '***** blocker info *****' as [***** blocker info *****]
, f.session_id as [blocker_session_id]
, f.blocking_session_id as [blocker_blocking_session_id]
, f.wait_type as [blocker_wait_type]
, f.wait_resource
, f.wait_time as [blocker_wait_time]
, f.start_time as [blocker_start_time]
, datediff(second, f.start_time, getdate()) as [blocker_duration_second]
, e.login_time as [blocker_login_time]
, e.last_request_start_time as [blocker_last_request_start_time]
, e.[status] as [blocker_status]
, e.login_name as [blocker_login_name]
, e.nt_domain as [blocker_nt_domain]
, e.nt_user_name as [blocker_nt_user_name]
, e.[host_name] as [blocker_host_name]
, e.[program_name] as [blocker_program_name]

, DB_NAME(f.database_id) AS [blocker_database_name]
, OBJECT_SCHEMA_NAME(h.objectid, f.database_id) AS [blocker_schema_name]
, OBJECT_NAME(h.objectid, f.database_id) AS [blocker_object_name]

, h.[text] AS [blocker_FullSQLBatch]
, CASE f.statement_end_offset
			WHEN -1 THEN SUBSTRING(h.[text], (f.[statement_start_offset] / 2) + 1, 2147483647)
			WHEN 0 THEN h.[text]
			ELSE SUBSTRING(h.[text], (f.[statement_start_offset] / 2) + 1, (f.[statement_end_offset] - f.[statement_start_offset]) / 2)
			END AS [blocker_ActiveStatement]
, g.query_plan as [blocker_query_plan]

, f.open_transaction_count as [blocker_open_transaction_count]
, f.open_resultset_count as [blocker_open_resultset_count]
, f.percent_complete as [blocker_percent_complete]
, f.reads as [blocker_reads]
, f.writes as [blocker_writes]
, f.logical_reads as [blocker_logical_reads]
, f.granted_query_memory as [blocker_granted_query_memory]

FROM sys.dm_exec_requests AS a
    OUTER APPLY sys.dm_exec_sql_text(a.[sql_handle]) AS b
    OUTER APPLY sys.dm_exec_query_plan(a.plan_handle) AS c
    LEFT JOIN sys.dm_exec_sessions AS d ON a.session_id = d.session_id

    left outer join sys.dm_exec_sessions as [e] on e.session_id = a.blocking_session_id
    left outer join sys.dm_exec_requests as [f] on f.session_id = a.blocking_session_id
    outer apply sys.dm_exec_query_plan(f.plan_handle) as [g]
    outer apply sys.dm_exec_sql_text(f.[sql_handle]) as [h]
WHERE a.session_id >= 50
  and a.session_id <> @@spid
-----------------------------------------------------