sys.dm_exec_requests:-

The sys.dm_exec_requests view returns one row for every currently executing request within your SQL Server instance and is useful for many purposes in addition to tracking down plan cache information.

This DMV contains the sql_handle and the plan_handle for the current statement, as well as resource usage information for each request.

For troubleshooting purposes, you can use this view to help identify long-running queries.

Keep in mind that the sql_handle points to the T-SQL for the entire batch. However, the sys.dm_exec_requests view contains the statement_start_offset and statement_end_offset columns,

which indicate the position within the entire batch where the currently executing statement can be found.

Keep in mind that the sql_handle points to the T-SQL for the entire batch. However, the sys.dm_exec_requests view contains the statement_start_offset and statement_end_offset columns, which indicate the position within the entire batch where the currently executing statement can be found. The offsets start at 0, and an offset of –1 indicates the end of the batch. The statement offsets can be used in combination with the sql_handle passed to sys.dm_exec_sql_text to extract the query text from the entire batch text, as demonstrated in the following code.

This query returns the Top 10 longest-running queries currently executing in SQL Server Using sys.dm_exec_requests:-

SELECT TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(text) ELSE statement_end_offset END - statement_start_offset)/2) + 1) AS query_text, session_id,blocking_session_id,start_time,status,command,sql_handle, plan_handle,database_id,wait_type,wait_time,open_transaction_count, logical_reads,reads,writes,text FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle)ORDER BY total_elapsed_time DESC;

Output:-