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. - 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:-
